Database Reference
In-Depth Information
WAIT_CLASS TIME_WAITED %
------------- ----------- -----
Idle 154.77 60.2
User I/O 96.99 37.7
CPU 4.46 1.7
Commit 0.85 0.3
Network 0.04 0.0
Configuration 0.03 0.0
Concurrency 0.02 0.0
Application 0.01 0.0
The resource usage profile based on wait classes is a beginning, but most of the time you need more precise
information. You need the wait events. For this purpose, the database engine also externalizes information about wait
events at the system level and for all connected sessions through the
v$system_event
and
v$session_event
views,
respectively. In addition, in a 12.1 multitenant environment, the
v$con_system_event
view shows statistics at the
container level. The following query, which was executed for the same session as the previous one, illustrates how
produce a detailed resource usage profile for the processing carried out by a session (you can use a similar query for the
system level as well as the container level, just use the dynamic performance views of the level you're interested in):
SQL> SELECT event,
2 round(time_waited, 3) AS time_waited,
3 round(1E2 * ratio_to_report(time_waited) OVER (), 1) AS "%"
4 FROM (
5 SELECT sid, event, time_waited_micro / 1E6 AS time_waited
6 FROM v$session_event
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;
EVENT TIME_WAITED %
----------------------------- ----------- -----
SQL*Net message from client 154.790 60.2
db file sequential read 96.125 37.4
CPU 4.461 1.7
log file sync 0.850 0.3
read by other session 0.734 0.3
db file parallel read 0.135 0.1
SQL*Net message to client 0.044 0.0
cursor: pin S 0.022 0.0
enq: TX - row lock contention 0.011 0.0
Disk file operations I/O 0.001 0.0
latch: In memory undo latch 0.001 0.0
In the preceding output, it's interesting to notice that the DB time accounts for only 39.8 percent (100 - 60.2)
of the total elapsed time. In fact, 60.2 percent is related to an idle wait event (
SQL*Net message from client
). This
