Oracle wait report

wait.sql

Author : Daniel W. Fink

Oracle Response Time report


SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/



*** Response Time Components by Time ***
Event Wait Seconds % of R
---------------------------------------- ------------------- ----------
control file parallel write 34,523.56 29.613725
latch: shared pool 18,121.77 15.544544
CPU Utilization 14,916.65 12.795248
db file async I/O submit 10,522.31 9.025855
os thread startup 9,933.96 8.521180
db file sequential read 7,489.39 6.424267
log file parallel write 7,079.45 6.072632
RMAN backup & recovery I/O 4,572.45 3.922174
log file sync 2,991.17 2.565779