How to check database i/o waits and compare them with CPU
column wait_type format a35 heading "Wait Type"
column lock_name format a12
column waits_1000 format 99,999,999 heading "Waits|\1000"
column time_waited_hours format 99,999.99 heading "Time|Hours"
column pct format 99.99 Heading "Pct"
column avg_wait_ms format 9,999.99 heading "Avg Wait|Ms"
set pagesize 10000
set lines 100
set echo on
WITH system_event AS
(SELECT CASE
WHEN wait_class IN ('User I/O', 'System I/O')
THEN event ELSE wait_class
END wait_type, e.*
FROM v$system_event e)
SELECT wait_type, SUM(total_waits) / 1000 waits_1000,
ROUND(SUM(time_waited_micro) / 1000000 / 3600, 2)
time_waited_hours,
ROUND(SUM(time_waited_micro) / SUM(total_waits) / 1000, 2)
avg_wait_ms,
ROUND( SUM(time_waited_micro)
* 100
/ SUM(SUM(time_waited_micro)) OVER (), 2)
pct
FROM (SELECT wait_type, event, total_waits, time_waited_micro
FROM system_event e
UNION
SELECT 'CPU', stat_name, NULL, VALUE
FROM v$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU')) l
WHERE wait_type <> 'Idle'
GROUP BY wait_type
ORDER BY SUM(time_waited_micro) DESC
/
Waits Time Avg Wait
Wait Type \1000 Hours Ms Pct
----------------------------------- ----------- ---------- --------- ------
CPU 318.54 76.46
db file sequential read 379,029 48.56 .46 11.66
db file parallel write 29,812 11.04 1.33 2.65
log file parallel write 61,098 11.03 .65 2.65
Commit 50,008 10.12 .73 2.43
Network 12,120,085 5.46 .00 1.31
control file parallel write 14,511 3.91 .97 .94
db file scattered read 3,777 3.55 3.38 .85
Application 1,498 1.34 3.23 .32
Concurrency 809 .64 2.83 .15
direct path read 875 .58 2.40 .14
db file parallel read 446 .46 3.68 .11
control file sequential read 35,529 .40 .04 .10
read by other session 336 .34 3.62 .08
log file sequential read 216 .26 4.29 .06
db file async I/O submit 25,549 .14 .02 .03
Other 982 .06 .24 .02
Log archive I/O 1 .06 189.48 .01
Disk file operations I/O 1,961 .04 .07 .01
Administrative 0 .02 307.80 .01
Data file init write 11 .01 3.81 .00
direct path write temp 9 .01 4.01 .00
direct path write 21 .01 1.58 .00
Configuration 5 .01 7.25 .00
direct path read temp 18 .00 .95 .00
control file single write 6 .00 2.72 .00
log file single write 2 .00 2.07 .00
db file single write 3 .00 .91 .00
recovery read 0 .00 8.50 .00
local write wait 1 .00 1.87 .00
Parameter File I/O 1 .00 1.17 .00
Scheduler 1 .00 1.79 .00
Disk file Mirror/Media Repair Write 0 .00 2.74 .00
33 rows selected.
SQL>
column wait_type format a35 heading "Wait Type"
column lock_name format a12
column waits_1000 format 99,999,999 heading "Waits|\1000"
column time_waited_hours format 99,999.99 heading "Time|Hours"
column pct format 99.99 Heading "Pct"
column avg_wait_ms format 9,999.99 heading "Avg Wait|Ms"
set pagesize 10000
set lines 100
set echo on
WITH system_event AS
(SELECT CASE
WHEN wait_class IN ('User I/O', 'System I/O')
THEN event ELSE wait_class
END wait_type, e.*
FROM v$system_event e)
SELECT wait_type, SUM(total_waits) / 1000 waits_1000,
ROUND(SUM(time_waited_micro) / 1000000 / 3600, 2)
time_waited_hours,
ROUND(SUM(time_waited_micro) / SUM(total_waits) / 1000, 2)
avg_wait_ms,
ROUND( SUM(time_waited_micro)
* 100
/ SUM(SUM(time_waited_micro)) OVER (), 2)
pct
FROM (SELECT wait_type, event, total_waits, time_waited_micro
FROM system_event e
UNION
SELECT 'CPU', stat_name, NULL, VALUE
FROM v$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU')) l
WHERE wait_type <> 'Idle'
GROUP BY wait_type
ORDER BY SUM(time_waited_micro) DESC
/
Waits Time Avg Wait
Wait Type \1000 Hours Ms Pct
----------------------------------- ----------- ---------- --------- ------
CPU 318.54 76.46
db file sequential read 379,029 48.56 .46 11.66
db file parallel write 29,812 11.04 1.33 2.65
log file parallel write 61,098 11.03 .65 2.65
Commit 50,008 10.12 .73 2.43
Network 12,120,085 5.46 .00 1.31
control file parallel write 14,511 3.91 .97 .94
db file scattered read 3,777 3.55 3.38 .85
Application 1,498 1.34 3.23 .32
Concurrency 809 .64 2.83 .15
direct path read 875 .58 2.40 .14
db file parallel read 446 .46 3.68 .11
control file sequential read 35,529 .40 .04 .10
read by other session 336 .34 3.62 .08
log file sequential read 216 .26 4.29 .06
db file async I/O submit 25,549 .14 .02 .03
Other 982 .06 .24 .02
Log archive I/O 1 .06 189.48 .01
Disk file operations I/O 1,961 .04 .07 .01
Administrative 0 .02 307.80 .01
Data file init write 11 .01 3.81 .00
direct path write temp 9 .01 4.01 .00
direct path write 21 .01 1.58 .00
Configuration 5 .01 7.25 .00
direct path read temp 18 .00 .95 .00
control file single write 6 .00 2.72 .00
log file single write 2 .00 2.07 .00
db file single write 3 .00 .91 .00
recovery read 0 .00 8.50 .00
local write wait 1 .00 1.87 .00
Parameter File I/O 1 .00 1.17 .00
Scheduler 1 .00 1.79 .00
Disk file Mirror/Media Repair Write 0 .00 2.74 .00
33 rows selected.
SQL>
Comments