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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s