AWR Summary Reporting #2

Following on from my previous post on doing summary reports on the data captured in the AWR. Here is some example output from using that query, and a useful statistic you can derive from the output data it produces.

The previous query just gave us 3 time values:

• Duration - real clock time that occurred between the AWR 2 snapshots being reported on
• Database Time - time spent by the database being active
• SQL Execution Time - time spent by the database executing SQL statements

If you use the following WHERE clause within the SQL I gave before you get these data values that have been captured today:

where snaps.end_snap_time > trunc (sysdate)

And with suitable column formatting

col snap_duration format 999,990.9
col db_time       format 999,990.99
col sql_exec_time format 999,990.99

you get output like the following:

SNAP_TIME      SNAP_DURATION     DB_TIME SQL_EXEC_TIME
-------------- ------------- ----------- -------------
15/08/13 00:00       3,602.0    1,637.70      1,394.76
15/08/13 01:00       3,605.1    1,378.69      1,175.09
15/08/13 02:00       3,586.6    2,219.12      2,024.38
15/08/13 03:00       3,619.0    1,436.43      1,265.08
15/08/13 04:00       3,626.7    1,190.99      1,021.95
15/08/13 05:00       3,552.6    1,216.76      1,051.03
15/08/13 06:00       3,602.9    1,287.73      1,104.61
15/08/13 07:00       3,594.1    1,595.69      1,392.66
15/08/13 08:00       3,606.4    2,351.33      2,132.49
15/08/13 09:00       3,609.6    3,449.07      3,132.93
15/08/13 10:00       3,622.1    9,128.43      8,266.52
15/08/13 11:00       3,598.8   12,355.47     10,913.63

And as mentioned you can cut and paste this into a spreadsheet and derive other statistics from these measurements.

One obvious measurement is Average Active Sessions, which is a measure of how many sessions were active at the same time. An "active session" will be doing work and will be measured as an increase in "Database Time". So dividing "Database Time" by the elapsed time between the consecutive snapshots will give you the Average Active Sessions.

We can see that between 8 and 9am the AAS was just under 1 (0.96), while between 9 and 10am it had risen to 2.52, and between 10 and 11am it was 3.43. Depending on the number of CPUs in your system this may or may not indicate an overloaded system. This particular system has 8 CPUs, so it can easily handle under 4 concurrently active sessions and is not overloaded.

Next, I'll add on some more SQL sub-queries to the main query to pull out other data from the AWR, telling us about other things that happened on the system.