SQL ORDERED BY CPU TIME AND USER I/O WAIT TIME IN AWR REPORT
In AWR , SQL ORDERED BY USER I/O WAIT TIME section has %IO, %CPU and %TOTAL
We will see how to calculate the percentage of these metrics in AWR
Generally, % disk usage is calculated by ( Time spent by disk in IO / elapsed time ) * 100
Example: If disk spend 0.30 seconds in I/O operation in one second interval, then,
% Disk usage = (0.30 / 1) * 100
Similarly,
%Total – % Time spent by specific SQL in I/O operation out of Total user I/O wait time
%CPU – % Time spent by specific SQL burning CPU out of the wall clock time(elapsed time) which means total duration of AWR report.
%IO – % Time spent by specific SQL in I/O operation out of wall clock time
User I/O wait time (s) means the I/O time spent by user (application class) like SQL accessing blocks from datafile
Calculate the total user IO wait time by adding up all the service name in Service Wait Class Stats
Calculating the value for top SQL.
%IO of SQL is calculated by,
%IO = ( User I/O wait time / Elapsed time ) * 100
= ( 1.01 / 6.07 ) * 100
~ 16.6 %
%CPU is calculated by,
%CPU = ( CPU Time / Elapsed time ) * 100
= ( 4.96 / 6.07 ) * 100
~ 81.71 %
%TOTAL of SQL is calculated by,
%TOTAL = ( User I/O wait time / Total User I/O wait time ) * 100
= ( 1.01 / ( Service Wait Class Stats -> User I/O Wt Time ) * 100
= ( 1.01 / 3 ) * 100
~ 33.7 %