AAS on AWR

February 9th, 2011

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.

Here is the query for a “live” system:


V$ACTIVE_SESSION_HISTORY + DBA_HIST_ACTIVE_SESS_HISTORY no DBID given


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

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
   from
      dba_hist_active_sess_history
   where sample_time > sysdate - &v_days
     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')
   union all
   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',1,decode(session_type,'BACKGROUND',0,1)))  total
     , sum(decode(session_state,'ON CPU' ,1,0))    cpu
     , sum(decode(session_state,'WAITING',1,0)) -
       sum(decode(session_type,'BACKGROUND',decode(session_state,'WAITING',1,0)))    -
       sum(decode(event,'db file sequential read',1,
                        'db file scattered read',1,
                        'db file parallel read',1,
                        'direct path read',1,
                        'direct path read temp',1,
                        'direct path write',1,
                        'direct path write temp',1, 0)) waits
     , sum(decode(session_type,'FOREGROUND',
         decode(event,'db file sequential read',1,
                                  'db file scattered read',1,
                                  'db file parallel read',1,
                                  'direct path read',1,
                                  'direct path read temp',1,
                                  'direct path write',1,
                                  'direct path write temp',1, 0))) IO
    from
      v$active_session_history
   where sample_time > sysdate - &v_days
   group by  trunc(to_char(sample_time,'SSSSS')/&v_secs),
             to_char(sample_time,'YYMMDD')
) ash,
  ( select  value from dba_hist_parameter where  parameter_name='cpu_count' and rownum < 2 ) p
order by to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS')
/

and output looks like

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

Here is the query for AWR repository where I’m looking for a particular DBID:


DBA_HIST_ACTIVE_SESS_HISTORY only with particular DBID


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 &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')
/

Update: here is a sql statement to give the wait/io/cpu graph along with the top two waits: (note, the cpu core count is hard coded at 4 since I didn’t join to dba_hist for the cpu_count parameters. Could be changed in the future version)


DBA_HIST_ACTIVE_SESS_HISTORY with particular DBID, give top wait event names


Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters

col aveact format 999.99
col graph format a80
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas1 format 9.99
col aas2 format 9.99
col pct1 format 999
col pct2 format 999
col first format  a15
col second format  a15

Def p_value=4

--select to_char(start_time,'DD HH:MI:SS'),
select to_char(start_time,'DD HH:MI'),
       --samples,
       --total,
       --waits,
       --cpu,
       (total/&v_secs) aas,
       --round(fpct * (total/&v_secs),2) aas1,
       fpct*100  pct1,
       decode(fpct,null,null,first) first,
       --round(spct * (total/&v_secs),2) aas2,
       spct*100 pct2,
       decode(spct,null,null,second) second,
	-- 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
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(io) io
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,
                        'db file sequential read',0,
                        'db file scattered read',0,
                        'db file parallel read',0,
                        'direct path read',0,
                        'direct path read temp',0,
                        'direct path write',0,
                        'direct path write temp',0, total)) waits
     , sum(decode(event,'db file sequential read',total,
                                  'db file scattered read',total,
                                  'db file parallel read',total,
                                  'direct path read',total,
                                  'direct path read temp',total,
                                  'direct path write',total,
                                  'direct path write temp',total, 0)) io
  from (
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        dba_hist_active_sess_history ash
     where
        --       sample_time > sysdate - &v_days
        -- and sample_time < ( select min(sample_time) from v$active_session_history)
           dbid=&DBID
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact
order by start_time
/

The output looks like

TO_CHAR(    AAS PCT1 FIRST           PCT2 SECOND          GRAPH
-------- ------ ---- --------------- ---- --------------- -------------------------------------
16 05:00     .0   74 CPU               10 log file sync   +                   4
16 06:00    1.0   67 CPU               11 db file scatter +++++o-             4
16 07:00    1.0   77 CPU               13 db file scatter +++++o              4
16 08:00    1.0   82 CPU               13 db file scatter +++++o              4
16 09:00    1.0   84 CPU               13 db file scatter +++++o              4
16 10:00    1.0   73 CPU               12 db file scatter +++++o-             4
16 11:00    1.0   85 CPU               12 db file scatter ++++++o             4
17 12:00    1.0   87 CPU               11 db file scatter +++++o              4
17 01:00    1.0   81 CPU               16 db file scatter +++++o              4
17 02:00    4.0   94 CPU                5 db file scatter +++++++++++++++++++o4o
17 03:00    3.0   91 CPU                6 db file scatter +++++++++++++++o    4
17 04:00    1.0   79 CPU               16 db file scatter +++++o              4
17 05:00    1.0   83 CPU               15 db file scatter +++++o              4
17 06:00    2.0   83 CPU               13 db file scatter +++++++o            4
17 07:00    2.0   85 CPU               12 db file scatter ++++++o             4
17 08:00     .0   57 CPU               14 db file scatter                     4


Uncategorized

  1. Trackbacks

  1. Comments

  2. João
    September 28th, 2012 at 18:07 | #1

    Hi Kyle, your blog is great.
    I have a doubt, in the first SQL
    “sum(decode(session_state,’ON CPU’ ,10,0))”
    Should not be “sum(decode(session_state,’ON CPU’ ,1,0))” ?

    If I am wrong, why did you use 10?

    Thanks

  3. September 28th, 2012 at 19:13 | #2

    @João DBA_HIST_ACTIVE_SESS_HISTORY has 1 in 10 samples, so every sample found is equivalent to 10 samples in v$active_session_history

  4. João
    October 2nd, 2012 at 12:01 | #3

    Kyle, I (think) know why I found strangers results…
    I put these values: v_days=1 and v_secs=1

    Seems that the query is returning some samples from both sources.
    If I change v_secs with any value greater than 1 then results are correct.
    Thanks again.

  5. João
    August 6th, 2013 at 13:49 | #4

    Kyle, I needed to put zero as DECODE default value:

    Before:
    …sum(decode(session_type,’BACKGROUND’,decode(session_state,’WAITING’,10,0)))…
    …’direct path write temp’,10, 0))) IO…

    After:
    …sum(decode(session_type,’BACKGROUND’,decode(session_state,’WAITING’,10,0),0))…
    …’direct path write temp’,10, 0),0)) IO…

    My result changed from:

    TM AAS CPU IO WAIT
    29/07/13 13:42:00 2 0,2 0,8 null

    To:
    29/07/13 13:42:00 2 0,2 0,8 1

    Thanks


1 × = five