AAS on AWR

I’m starting this post as an ongoing discussion of research.
Today I received a AWR export and wanted to get a feel for what the load is on the system. I imported the AWR report and then wanted a quick overview, so I put together a script to give the AAS over time divided up as CPU, WAIT and IO. Current drawbacks is that the script uses SYSDATE which might not be appropriate if the AWR data is from many days ago.
The query seems a bit slow on a month of data, so will be looking at optimizations in the future.
+ = load demanding CPU
o = load waiting for IO to complete
- = load waiting for something like a lock, latch or other resource
the “8″ in the middle of the line is the # of CPU cores. A system that was a 100% maxed out and not waiting for IO or other resources would have pluses (“+”) right up to the number of cores, which is 8 in this case. If the pluses went over the number of cores then there would be contention on CPU resources.

TM                  AAS    CPU     IO   WAIT GRAPH
---------------- ------ ------ ------ ------ --------------------------------------------------------------------------------
03-FEB  04:00:00    3.6    1.8    1.8     .1 +++++++++ooooooooo                      8
03-FEB  05:00:00    4.1    1.9    2.1     .1 ++++++++++oooooooooo-                   8
03-FEB  06:00:00    5.1    2.8    1.9     .3 ++++++++++++++oooooooooo--              8
03-FEB  07:00:00    5.2    2.6    2.2     .3 +++++++++++++ooooooooooo--              8
03-FEB  08:00:00    5.9    3.0    2.6     .2 +++++++++++++++ooooooooooooo-           8
03-FEB  09:00:00    6.3    3.2    2.8     .3 ++++++++++++++++oooooooooooooo--        8
03-FEB  10:00:00    6.3    3.0    2.9     .3 +++++++++++++++ooooooooooooooo--        8
03-FEB  11:00:00    5.5    2.9    2.5     .2 ++++++++++++++oooooooooooo-             8
03-FEB  12:00:00    5.3    2.6    2.5     .2 +++++++++++++ooooooooooooo-             8
03-FEB  13:00:00    5.4    2.7    2.4     .3 +++++++++++++oooooooooooo-              8
03-FEB  14:00:00    5.5    2.8    2.4     .3 ++++++++++++++oooooooooooo-             8
03-FEB  15:00:00    6.1    2.8    3.1     .2 ++++++++++++++ooooooooooooooo-          8
03-FEB  16:00:00    6.3    3.1    3.0     .2 +++++++++++++++ooooooooooooooo-         8
03-FEB  17:00:00    5.6    2.7    2.6     .2 ++++++++++++++ooooooooooooo-            8
03-FEB  18:00:00    3.4    2.1    1.1     .1 +++++++++++oooooo-                      8
03-FEB  19:00:00    4.7    2.5    1.6     .6 +++++++++++++oooooooo---                8
03-FEB  20:00:00    4.7    2.8    1.8     .1 ++++++++++++++ooooooooo                 8
03-FEB  21:00:00    7.3    3.6    3.3     .4 ++++++++++++++++++ooooooooooooooooo--   8
03-FEB  22:00:00   19.2    6.4   11.5    1.2 ++++++++++++++++++++++++++++++++oooooooo8ooooooooooooooooooooooooooooooooooooooo
03-FEB  23:00:00   12.7    5.2    7.1     .5 ++++++++++++++++++++++++++oooooooooooooo8oooooooooooooooooooooo--
04-FEB  00:00:00   11.1    4.5    6.3     .3 ++++++++++++++++++++++oooooooooooooooooo8oooooooooooooo--
04-FEB  01:00:00   12.8    4.3    5.9    2.6 ++++++++++++++++++++++oooooooooooooooooo8oooooooooooo-------------
04-FEB  02:00:00    4.2    2.5    1.6     .1 ++++++++++++oooooooo                    8
04-FEB  03:00:00    2.1    1.3     .7     .1 +++++++ooo                              8
04-FEB  04:00:00    2.1    1.3     .9     .0 ++++++oooo                              8
04-FEB  05:00:00    2.3    1.2    1.0     .1 ++++++ooooo                             8
04-FEB  06:00:00    3.7    2.5    1.0     .2 +++++++++++++ooooo-                     8

Def v_days=10   -- amount of days to cover in report
Def v_secs=3600 -- size of bucket in seconds, ie one row represents avg over this interval
Def v_bars=5    -- size of one AAS in characters wide
Def v_graph=80  -- width of graph in characters

undef DBID
col graph format a&v_graph
col aas format 999.9
col total format 99999
col npts format 99999
col wait for 999.9
col cpu for 999.9
col io for 999.9

/* dba_hist_active_sess_history */

select
        to_char(to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS'),'DD-MON  HH24:MI:SS') tm,
        --samples npts,
        round(total/&v_secs,1) aas,
        round(cpu/&v_secs,1) cpu,
        round(io/&v_secs,1) io,
        round(waits/&v_secs,1) wait,
	-- substr, ie trunc, the whole graph to make sure it doesn't overflow
        substr(
	   -- substr, ie trunc, the graph below the # of CPU cores line
           -- draw the whole graph and trunc at # of cores line
	   substr(
	     rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
        p.value  ||
	   -- draw the whole graph, then cut off the amount we drew before the # of cores
           substr(
	     rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),( &v_graph-&v_bars*p.value) )
        ,0,&v_graph)
        graph
from (
   select
       to_char(sample_time,'YYMMDD')                   tday
     , trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
     , (max(sample_id) - min(sample_id) + 1 )      samples
     , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10)))  total
     , sum(decode(session_state,'ON CPU' ,10,0))    cpu
     , sum(decode(session_state,'WAITING',10,0)) -
       sum(decode(session_type,'BACKGROUND',decode(session_state,'WAITING',10,0)))    -
       sum(decode(event,'db file sequential read',10,
                        'db file scattered read',10,
                        'db file parallel read',10,
                        'direct path read',10,
                        'direct path read temp',10,
                        'direct path write',10,
                        'direct path write temp',10, 0)) waits
     , sum(decode(session_type,'FOREGROUND',
		 decode(event,'db file sequential read',10,
                                  'db file scattered read',10,
                                  'db file parallel read',10,
                                  'direct path read',10,
                                  'direct path read temp',10,
                                  'direct path write',10,
                                  'direct path write temp',10, 0))) IO
       /* for waits I want to subtract out the BACKGROUND
          but for CPU I want to count everyone */
   from
      dba_hist_active_sess_history
   where sample_time > sysdate - &v_days
   and dbid=&&DBID
   and sample_time < (select min(sample_time) from v$active_session_history)
   group by  trunc(to_char(sample_time,'SSSSS')/&v_secs),
             to_char(sample_time,'YYMMDD')
) ash,
  ( select  value from dba_hist_parameter where DBID=&&DBID and parameter_name='cpu_count' and rownum < 2 ) p
order by to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS')
/