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
Oracle L
Trackbacks
Comments
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
@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
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.
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