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 %

Leave a Reply