Joining the 10g time model to the traditional wait interface views and taking advantage of the wait_class data is something most of us have probably done. Here's my standard queries that do that thing....
fCOLUMN wait_class format a20
COLUMN name format a30
COLUMN time_secs format 999,999,999,999.99
COLUMN pct format 99.99
SELECT wait_class, NAME, ROUND (time_secs, 2) time_secs,
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM (SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event AND n.wait_class <> 'Idle'
AND time_waited > 0
UNION
SELECT 'CPU', 'server CPU', SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY time_secs DESC;
Which generates CPU and wait event times broken down to the event name:
WAIT_CLASS NAME TIME_SECS PCT
-------------------- ------------------------------ ------------------- ------
CPU server CPU 13,050.00 56.97
System I/O control file sequential read 3,331.25 14.54
User I/O db file sequential read 2,965.98 12.95
Other reliable message 1,438.62 6.28
Concurrency row cache lock 578.78 2.53
Other lms flush message acks 264.57 1.15
Other enq: WF - contention 143.21 .63
System I/O control file parallel write 134.40 .59
Other name-service call wait 111.51 .49
Other DFS lock handle 106.26 .46
Other CGS wait for IPC msg 72.90 .32
Cluster gc current grant busy 66.16 .29
Concurrency os thread startup 59.71 .26
System I/O db file parallel write 58.73 .26
Other PX Deq Credit: send blkd 56.02 .24
System I/O log file parallel write 44.33 .19
User I/O db file scattered read 42.20 .18
Other ksxr poll remote instances 40.07 .17
Grouping by wait event can be handy if we want to get a higher level summary :
SELECT wait_class, SUM (time_secs) time_secs, SUM (pct) pct
FROM (SELECT wait_class, NAME, ROUND (time_secs, 2) time_secs,
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM (SELECT n.wait_class, e.event NAME,
e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event
AND n.wait_class <> 'Idle'
AND time_waited > 0
UNION
SELECT 'CPU', 'server CPU', SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU')))
GROUP BY wait_class
ORDER BY time_secs DESC;
WAIT_CLASS TIME_SECS PCT
-------------------- ------------------- ------
CPU 13,051.55 56.97
System I/O 3,569.86 15.58
User I/O 3,019.58 13.18
Other 2,413.23 10.46
Concurrency 648.60 2.83
Cluster 145.72 .64
Network 30.66 .14
Application 23.35 .10
Configuration 4.52 .01
Commit 2.99 .01
Administrative .02 .00
Comments