Database Reference
In-Depth Information
Scheduler 10
System I/O 34
User I/O 51
1569
SQL> SELECT name
2 FROM v$event_name
3 WHERE wait_class = 'Commit';
NAME
----------------------------------
remote log force - commit
log file sync
nologging standby txn commit
enq: BB - 2PC across RAC instances
The classes of wait events that have been experienced at the system level and for all connected sessions are
externalized through the
v$system_wait_class
and
v$session_wait_class
views, respectively. In addition, in a 12.1
multitenant environment, the
v$con_system_wait_class
view shows statistics at the container level. These dynamic
performance views have three key columns:
•
wait_class
identifies the wait class,
•
total_waits
is the number of wait events cumulated since the component they belong to
(database instance, session or container) was initiated, and
•
time_waited
is the amount of waiting time (in hundredths of a second) cumulated since the
component they belong to (database instance, session or container) was initiated.
It goes without saying that for the session-level statistics (
v$session_wait_class
), there's also a column (
sid
)
that identifies the session they belong to, and in a 12.1 multitenant environment, there's a column (
con_id
) that
identifies the container. The following example is based on the same session shown in the previous section, the one
that spends only 4.4 percent of the DB time on CPU. The example illustrates how to produce a concise resource usage
profile for the processing carried out by a session. You can use a similar query for the system level, as well as for the
container level. Just change the query to reference the dynamic performance views for the level you are interested in.
SQL> SELECT wait_class,
2 round(time_waited, 3) AS time_waited,
3 round(1E2 * ratio_to_report(time_waited) OVER (), 1) AS "%"
4 FROM (
5 SELECT sid, wait_class, time_waited / 1E2 AS time_waited
6 FROM v$session_wait_class
7 WHERE total_waits > 0
8 UNION ALL
9 SELECT sid, 'CPU', value / 1E6
10 FROM v$sess_time_model
11 WHERE stat_name = 'DB CPU'
12 )
13 WHERE sid = 42
14 ORDER BY 2 DESC;
