DB Time, Average Active Sessions, and Some Scripts

Over the years, Oracle DBAs and developers have used different techniques to identify performance problems. Many books have been written, white papers published, blogs blogged upon, and so forth.  Nearly every Oracle DBA has the term “tuning” somewhere on their resume.  Common tuning approaches have included:
Ratio based tuning (ugh)
– Based on counters and math
– “buffer cache hit ratio”
– “library cache hit ratio”
– BSTAT/ESTAT
– Statspack and AWR, looking at “Efficiency” sections
Wait event tuning (better)
– Based on timers, counts, times-per-count
– V$SESSION_WAIT
– V$SESSION_EVENT, V$SYSTEM_EVENT
DB Time Model optimization (best)
– Focused on time
– Uses an “Average Active Sessions” metric
– The modern approach
– The “right” approach
When interviewing DBAs, some of the questions I ask in every interview are:
“You arrive at work in the morning and receive several complaints that the system is slow.  What do you do?”
“You arrive at work in the morning and learn that the overnight batch jobs ran twice as long as normal.  How to you analyze this?”
The replies I get are varied and usually include some of the following responses:
“I look and see what’s running long”
(This is where I’d typically ask them exactly how they do this)
“I ask the users what’s changed”
(Interesting response, usually means that the DBA has no idea where to start)
“I see what has changed”
(Another interesting response, and their follow-up to additional probing questions tells me what they know about Oracle performance)
“I run ‘top’ and kill the stuff at the top of the list”
(Scary, but you’d be surprised …)
“I run an AWR report and …”
(Getting better …)
“I query V$SESSION_WAIT to see what’s actively waiting on ….”
(Closer to being on the right track – typically points them in the right direction, but is often not enough)
In this post I’m going to talk briefly about the DB Time Method and Average Active Sessions, the methods interviewees never seem to talk about.  I’ll also show a bunch of scripts, corrections or modifications welcome …
The DB Time Model
What importance does time have in addressing Oracle performance problems?  In short, time is the only thing that’s important.   Performance is defined by how long it takes to perform a chunk of work, or, in the eyes of the program/user:
Performance  = (DB Time) / (Clock Time)
In other words:
– Database performance is evaluated based on the amount of time spent in the database
– This time is comprised of CPU time, Database wait time, and scheduler wait time
– The end-to-end performance profile of a session or program comprises all the time elements, not just wait times or CPU time
– Performance is about time – performance improvement means doing things faster.  Or, using the graphic above, shortening or removing the horizontal lines.
Using the Time Model for Oracle performance tuning is great because it impacts the one dimension that impacts a user’s experience the most – time.
So What is DB Time?
Database Time, or DB Time, is defined by Oracle as the total time by foreground sessions executing database calls. This includes CPU time, IO time, and non-idle wait time.  In other words, it’s the total time spent either actively working or actively waiting in a database call.
An Active Session is defined as an Oracle session currently spending time in a database call, and the average activity of a session is the ratio of active to total wall clock time.
Average Active Sessions
At a macroscopic level, DB Time is the sum of DB time over all sessions.  Average Active Sessions, or AAS, represents the sum of active sessions over all sessions, at any given point in time.    Let’s use the graphic below:
As we can see, AAS represents the number of active sessions at any given time interval.
The Average Active Sessions metric is important because it’s best representation of your database system load.
AAS over all sessions, for the example above, looks like this:
Another way to understand the AAS metric is:
AAS = (DB Time / Elapsed Time)
In other words, AAS is a time-normalized DB Time.
DB Time and AAS in Enterprise Manager
On the main performance page in Enterprise Manager, you can see a perfect picture of both Average Active Sessions and DB Time:
– The “curve” (i.e., line at the top) = Average Active Sessions
– The area under the curve = DB Time
Or as Leibniz would say:
Active Sessions = ∂DBTime / ∂Time
Or:
DBTime = ∫ Active Sessions
ASH, DB Time, and AAS
ASH samples all active foreground and background sessions every second, and active foreground sessions contribute to DB Time.  Contents of ASH are stored in a circular in-memory (SGA) buffer and flushed to disk, exposed via DBA_HIST_ACTIVE_SESS_HISTORY.  The sampling interval of DBA_HIST_ACTIVE_SESS_HISTORY is 10 second.
The math with ASH is simple:

 

– V$ACTIVE_SESSION_HISTORY => COUNT(*) = DB Time in seconds

– DBA_HIST_ACTIVE_SESS_HISTORY => 10 * (COUNT(*)) = DB Time in seconds

Understanding how ASH information can be used to compute DB Time, you can build a relatively extensive library of scripts to calculate DB Time.
One thing that’s important to point out in this post is the following – we’re talking about DB Time and Average Active Sessions, or the time model stuff.  In ASH, these are counters.  You’ll also see WAIT_TIME and TIMED_WAITED in ASH, which are actual measurements of wait time and CPU time (if SESSION_STATE=’ON CPU’).  These numbers, in ASH, are accurate but tabulated within an ASH interval; in other words, if you sum TIMED_WAITED and WAIT_TIME per sample, you won’t come up with 1 second (even though 1 second = 1 interval).   The per-interval calculations could be less or more depending on when calls complete; for example, a long IO operation could show up in 10 subsequent ASH intervals as “db file scattered read”, wait class = “User IO”, session state = “WAITING”.  But only the last sample will summarize the total wait time spent on the single IO operation. It’s important to think this way when looking at ASH with respect to DB Time:
SUM(1), or COUNT(SAMPLE_ID), or COUNT(*) per SAMPLE_ID = DB Time in seconds.  It’s approximated, but pretty close over large ranges of sample times.
Throughout the scripts below, I’m not going to be looking at data from “wait” views, such as V$WAITCLASSMETRIC or V$WAITCLASSMETRIC_HISTORY.  These views have important information to gather wait detail, and in fact this is what EM uses in some of its drill-downs.  But here we’re talking about DB Time and AAS here, so the focus is on this model.
Script: dbt_ashcurr_bytype.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashcurr_bytype.sql
rem Purpose: Show DB Time and AAS at 1-second intervals from V$ACTIVE_SESSION_HISTORY
rem Granluarity: By wait class or “CPU/CPU + CPU Wait”
rem Notes: At 1-sec intervals, AAS = DBT
rem #################################################################################col samplestart format a30 head ‘Begin Time’
col tc format a30 head ‘Time|Comp’
col dbtw format 999.99 head ‘DB Time|Metric’
col smpcnt format 999.99 head ‘DB Time’
col epct format 999.99 head ‘% per Sample’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime
break on samplestart
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(1/86400) FROM xtimes WHERE xdate+(1/86400) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
nvl(wait_class,’*** IDLE *** ‘) tc,
decode(wait_class,null,0,dbtw) dbtw,
nvl(smpcnt,0) smpcnt,
round(100*(dbtw/smpcnt),2) epct
from (
select s1.xdate,ash.sample_id,ash.sample_time,wait_class,count(*) dbtw,ash.smpcnt
from (
select sample_id,sample_time,session_state,
decode(session_state,’ON CPU’,’CPU + CPU Wait’,wait_class) wait_class,
count(sample_id) over (partition by sample_id) smpcnt
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime
)  ash,
(select xdate
from xtimes ) s1
where to_date(TO_CHAR(ash.sample_time(+),’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)=s1.xdate
group by s1.xdate,sample_id,wait_class,smpcnt,sample_time)
order by 1,dbtw desc
/
undefine etime
undefine btime
undefine bt
undefine et
Script: dbt_ashcurr_byevt.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashcurr_byevt.sql
rem Purpose: Show DB Time and AAS at 1-second intervals from V$ACTIVE_SESSION_HISTORY
rem Granluarity: By wait event or “CPU/CPU + CPU Wait”
rem #################################################################################
col samplestart format a30 head ‘Begin Time’
col tc format a30 head ‘Component’
col dbtw format 999.99 head ‘DB Time/AAS|Per Event’
col smpcnt format 999.99 head ‘DB Time / AAS’
col epct format 999.99 head ‘% per Sample’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime
break on samplestart
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(1/86400) FROM xtimes WHERE xdate+(1/86400) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
nvl(evt,’*** IDLE *** ‘) tc,
decode(evt,null,0,dbtw) dbtw,
nvl(smpcnt,0) smpcnt,
round(100*(dbtw/smpcnt),2) epct
from (
select s1.xdate,ash.sample_id,ash.sample_time,evt,count(*) dbtw,ash.smpcnt
from (
select sample_id,sample_time,session_state,
decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) evt,
count(sample_id) over (partition by sample_id) smpcnt
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime
)  ash,
(select xdate
from xtimes ) s1
where to_date(TO_CHAR(ash.sample_time(+),’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)=s1.xdate
group by s1.xdate,sample_id,evt,smpcnt,sample_time)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
Script: dbt_ashall_bytype.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashall_bytype.sql
rem Purpose: Show DB Time and AAS at 1-min intervals from DBA_HIST_ACTIVE_SESS_HISTORY
rem Granluarity: By wait event or “CPU/CPU + CPU Wait”
rem #################################################################################
col samplestart format a30 head ‘Begin Time’
col tc format a30 head ‘Component’
col dbt_w format 99999.99 head ‘DB Time|Per Metric’
col aas_w format 99999.99 head ‘AAS|Per Metric’
col dbt_a format 99999.99 head ‘DB Time’
col aas_a format 99999.99 head ‘AAS’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)
 between :btime and :etime
break on samplestart
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(1/1440) FROM xtimes WHERE xdate+(1/1440) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI’) samplestart,
nvl(wait_class,’*** IDLE *** ‘) tc,
sum(decode(wait_class,null,0,dbtw)) dbt_w,
sum(decode(wait_class,null,0,dbtw))/60 aas_w,
nvl(smpcnt,0) dbt_a,
nvl(smpcnt,0)/60 aas_a
from (
select s1.xdate,ash.sample_id,
wait_class,10*count(*) dbtw,ash.smpcnt
from (
select sample_id,sample_time,session_state,
decode(session_state,’ON CPU’,’CPU + CPU Wait’,wait_class) wait_class,
10*(count(sample_id) over (partition by to_date(to_char(sample_time,’DD-MON-RR HH24:MI’), ‘DD-MON-RR HH24:MI:SS’))) smpcnt
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)
 between :btime and :etime
)  ash,
(select to_date(TO_CHAR(xdate,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’) xdate
from xtimes ) s1
where to_date(TO_CHAR(ash.sample_time(+),’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)=s1.xdate
group by s1.xdate,sample_id,wait_class,smpcnt
)
group by xdate,wait_class,smpcnt
order by 1,dbt_w desc
/
undefine btime
undefine etime
undefine bt
undefine et
Script: dbt_ashall_byevt.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashall_byevt.sql
rem Purpose: Show DB Time and AAS at 1-second intervals from DBA_HIST_ACTIVE_SESS_HISTORY
rem Granluarity: By wait class or “CPU/CPU + CPU Wait”
rem #################################################################################
col samplestart format a30 head ‘Begin Time’
col tc format a30 head ‘Component’
col dbt_w format 99999.99 head ‘DB Time|Per Event’
col aas_w format 99999.99 head ‘AAS|Per Event’
col dbt_a format 99999.99 head ‘DB Time’
col aas_a format 99999.99 head ‘AAS’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)
 between :btime and :etime
break on samplestart
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(1/1440) FROM xtimes WHERE xdate+(1/1440) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI’) samplestart,
nvl(evt,’*** IDLE *** ‘) tc,
sum(decode(evt,null,0,dbtw)) dbt_w,
sum(decode(evt,null,0,dbtw))/60 aas_w,
nvl(smpcnt,0) dbt_a,
nvl(smpcnt,0)/60 aas_a
from (
select s1.xdate,ash.sample_id,
evt,10*count(*) dbtw,ash.smpcnt
from (
select sample_id,sample_time,session_state,
decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) evt,
10*(count(sample_id) over (partition by to_date(to_char(sample_time,’DD-MON-RR HH24:MI’), ‘DD-MON-RR HH24:MI:SS’))) smpcnt
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)
 between :btime and :etime
)  ash,
(select to_date(TO_CHAR(xdate,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’) xdate
from xtimes ) s1
where to_date(TO_CHAR(ash.sample_time(+),’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)=s1.xdate
group by s1.xdate,sample_id,evt,smpcnt
)
group by xdate,evt,smpcnt
order by 1,dbt_w desc
/
undefine btime
undefine etime
undefine bt
undefine et
Script: dbt_ashrec_bytype.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashrec_bytype.sql
rem Purpose: Show DB Time and AAS at X-second intervals from V$ACTIVE_SESSION_HISTORY
rem Granluarity: By wait class or “CPU/CPU + CPU Wait”
rem X = entered seconds
rem #################################################################################
col samplestart format a19 head ‘Begin Time’
col samplesend format a19 head ‘End Time’
col tc format a30 head ‘Component’
col dbtw format 999.99 head ‘DB Time|Per Metric’
col smpcnt format 999.99 head ‘DB Time|Total’
col aas format 999.99 head ‘AAS|Total’
col epct format 999.99 head ‘% per Sample’
col aas_comp format 999.99 head ‘AAS per|Metric’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime;
break on samplestart on sampleend on smpcnt on aas
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(&&interval_secs/86400) FROM xtimes WHERE xdate+(&&interval_secs/86400) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
        to_char(xdate+&&interval_secs/86400,’DD-MON-RR HH24:MI:SS’) sampleend,
       (sum(decode(wait_class,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’))) smpcnt,
       (sum(decode(wait_class,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’)))/&&interval_secs aas,
       nvl(wait_class,’*** IDLE *** ‘) tc,
       decode(wait_class,null,0,dbtw) dbtw,
       decode(wait_class,null,0,dbtw)/&&interval_secs aas_comp
from (
        select s1.xdate,wait_class,count(*) dbtw,count(ash.smpcnt) smpcnt
          from (
                select  sample_id,
                        sample_time,
                        session_state,
                        decode(session_state,’ON CPU’,’CPU + CPU Wait’,wait_class) wait_class,
                        count(sample_id) over (partition by sample_id) smpcnt
                from v$active_session_history
                where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
                        between :btime and :etime
                )  ash,
                (select xdate
                from xtimes ) s1
        where 1=1
        and ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_secs/86400)
        group by s1.xdate,wait_class)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
undefine interval_secs
Script: dbt_ashrec_byevt.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashrec_byevt.sql
rem Purpose: Show DB Time and AAS at X-second intervals from V$ACTIVE_SESSION_HISTORY
rem Granluarity: By wait event or “CPU/CPU + CPU Wait”
rem Notes: At X-sec intervals, where X = Entered time in seconds
rem #################################################################################
col samplestart format a19 head ‘Begin Time’
col samplesend format a19 head ‘End Time’
col tc format a30 head ‘Component’
col dbtw format 999.99 head ‘DB Time|Per Metric’
col smpcnt format 999.99 head ‘DB Time|Total’
col aas format 999.99 head ‘AAS|Total’
col epct format 999.99 head ‘% per Sample’
col aas_comp format 999.99 head ‘AAS per|Metric’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime;
break on samplestart on sampleend on smpcnt on aas
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(&&interval_secs/86400) FROM xtimes WHERE xdate+(&&interval_secs/86400) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
        to_char(xdate+&&interval_secs/86400,’DD-MON-RR HH24:MI:SS’) sampleend,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’))) smpcnt,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’)))/&&interval_secs aas,
       nvl(event,’*** IDLE *** ‘) tc,
       decode(event,null,0,dbtw) dbtw,
       decode(event,null,0,dbtw)/&&interval_secs aas_comp
from (
        select s1.xdate,event,count(*) dbtw,count(ash.smpcnt) smpcnt
          from (
                select  sample_id,
                        sample_time,
                        session_state,
                        decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) event,
                        count(sample_id) over (partition by sample_id) smpcnt
                from v$active_session_history
                where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
                        between :btime and :etime
                )  ash,
                (select xdate
                from xtimes ) s1
        where 1=1
        and ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_secs/86400)
        group by s1.xdate,event)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
undefine interval_secs
Script: dbt_ashpast_bytype.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashpast_bytype.sql
rem Purpose: Show DB Time and AAS at X-min intervals from DBA_HIST_ACTIVE_SESS_HISTORY
rem Granluarity: By wait class or “CPU/CPU + CPU Wait”
rem Notes: At X-min intervals, where X = Entered time in mins
rem #################################################################################
col samplestart format a19 head ‘Begin Time’
col samplesend format a19 head ‘End Time’
col tc format a30 head ‘Component’
col dbtw format 999999.99 head ‘DB Time|Per Metric’
col smpcnt format 999999.99 head ‘DB Time|Total’
col aas format 999.99 head ‘AAS|Total’
col epct format 999.99 head ‘% per Sample’
col aas_comp format 999.99 head ‘AAS per|Metric’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime;
break on samplestart on sampleend on smpcnt on aas
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(&&interval_mins/1440) FROM xtimes WHERE xdate+(&&interval_mins/1440) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
        to_char(xdate+&&interval_mins/1440,’DD-MON-RR HH24:MI:SS’) sampleend,
       (sum(decode(wait_class,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’))) smpcnt,
       (sum(decode(wait_class,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’)))/60/&&interval_mins aas,
       nvl(wait_class,’*** IDLE *** ‘) tc,
       decode(wait_class,null,0,dbtw) dbtw,
       decode(wait_class,null,0,dbtw)/60/&&interval_mins aas_comp
from (
        select s1.xdate,wait_class,10*count(*) dbtw,count(ash.smpcnt) smpcnt
          from (
                select  sample_id,
                        sample_time,
                        session_state,
                        decode(session_state,’ON CPU’,’CPU + CPU Wait’,wait_class) wait_class,
                        10*(count(sample_id) over (partition by sample_id)) smpcnt
                from dba_hist_active_sess_history
                where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
                        between :btime and :etime
                )  ash,
                (select xdate
                from xtimes ) s1
        where 1=1
        and ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_mins/1440)
        group by s1.xdate,wait_class)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
undefine interval_mins
Script: dbt_ashpast_byevt.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashpast_byevt.sql
rem Purpose: Show DB Time and AAS at X-min intervals from dba_hist_active_sess_history
rem Granluarity: By wait event or “CPU/CPU + CPU Wait”
rem Notes: At X-min intervals, where X = Entered time in minutes
rem #################################################################################
col samplestart format a19 head ‘Begin Time’
col samplesend format a19 head ‘End Time’
col tc format a30 head ‘Component’
col dbtw format 9999999.99 head ‘DB Time|Per Metric’
col smpcnt format 999999.99 head ‘DB Time|Total’
col aas format 999.99 head ‘AAS|Total’
col epct format 999.99 head ‘% per Sample’
col aas_comp format 999.99 head ‘AAS per|Metric’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime;
break on samplestart on sampleend on smpcnt on aas
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(&&interval_mins/1440) FROM xtimes WHERE xdate+(&&interval_mins/1440) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
        to_char(xdate+&&interval_mins/1440,’DD-MON-RR HH24:MI:SS’) sampleend,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’))) smpcnt,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’)))/60/&&interval_mins aas,
       nvl(event,’*** IDLE *** ‘) tc,
       decode(event,null,0,dbtw) dbtw,
       decode(event,null,0,dbtw)/60/&&interval_mins aas_comp
from (
        select s1.xdate,event,10*count(*) dbtw,count(ash.smpcnt) smpcnt
          from (
                select  sample_id,
                        sample_time,
                        session_state,
                        decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) event,
                        10*(count(sample_id) over (partition by sample_id)) smpcnt
                from dba_hist_active_sess_history
                where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
                        between :btime and :etime
                )  ash,
                (select xdate
                from xtimes ) s1
        where 1=1
        and ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_mins/1440)
        group by s1.xdate,event)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
undefine interval_mins
Script: dbt_ashcurr_bysqlid.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashcurr_sqlid.sql
rem Purpose: Show DB Time and AAS at 1-second intervals from V$ACTIVE_SESSION_HISTORY
rem Granluarity: By SQL_ID
rem #################################################################################
col samplestart format a30 head ‘Begin Time’
col tc format a35 head ‘Component’
col sql_id format a15 head ‘SQL_ID’
col dbtw format 999.99 head ‘DB Time/AAS|Per Event’
col smpcnt format 999.99 head ‘DB Time / AAS’
col epct format 999.99 head ‘% per Sample’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime
break on samplestart on sql_id
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(1/86400) FROM xtimes WHERE xdate+(1/86400) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
sql_id,
nvl(evt,’*** IDLE *** ‘) tc,
decode(evt,null,0,dbtw) dbtw,
nvl(smpcnt,0) smpcnt,
round(100*(dbtw/smpcnt),2) epct
from (
select s1.xdate,ash.sample_id,ash.sample_time,sql_id,evt,count(*) dbtw,ash.smpcnt
from (
select sample_id,sample_time,session_state,sql_id,
decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) evt,
count(sample_id) over (partition by sample_id) smpcnt
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime
)  ash,
(select xdate
from xtimes ) s1
where to_date(TO_CHAR(ash.sample_time(+),’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)=s1.xdate
group by s1.xdate,sample_id,evt,sql_id,smpcnt,sample_time)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
Script: dbt_ashall_bysqlid.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashall_bysqlid.sql
rem Purpose: Show DB Time and AAS at 1-second intervals from DBA_HIST_ACTIVE_SESS_HISTORY
rem Granluarity: By SQL_ID, wait class or “CPU/CPU + CPU Wait”
rem #################################################################################
col samplestart format a30 head ‘Begin Time’
col tc format a30 head ‘Component’
col sql_id format a15 head ‘SQL_ID’
col dbt_w format 99999.99 head ‘DB Time|Per Event’
col aas_w format 99999.99 head ‘AAS|Per Event’
col dbt_a format 99999.99 head ‘DB Time’
col aas_a format 99999.99 head ‘AAS’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)
 between :btime and :etime
break on samplestart
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(1/1440) FROM xtimes WHERE xdate+(1/1440) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI’) samplestart,sql_id,
nvl(evt,’*** IDLE *** ‘) tc,
sum(decode(evt,null,0,dbtw)) dbt_w,
sum(decode(evt,null,0,dbtw))/60 aas_w,
nvl(smpcnt,0) dbt_a,
nvl(smpcnt,0)/60 aas_a
from (
select s1.xdate,ash.sample_id,sql_id,
evt,10*count(*) dbtw,ash.smpcnt
from (
select sample_id,sample_time,session_state,sql_id,
decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) evt,
10*(count(sample_id) over (partition by to_date(to_char(sample_time,’DD-MON-RR HH24:MI’), ‘DD-MON-RR HH24:MI:SS’) )) smpcnt
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)
 between :btime and :etime
)  ash,
(select to_date(to_char(xdate,’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’) xdate
from xtimes ) s1
where to_date(TO_CHAR(ash.sample_time(+),’DD-MON-RR HH24:MI’),’DD-MON-RR HH24:MI’)=s1.xdate
group by s1.xdate,sample_id,evt,smpcnt,sql_id
)
group by xdate,evt,smpcnt,sql_id
order by 1,dbt_w desc
/
undefine btime
undefine etime
undefine bt
undefine et
Script: dbt_ashrec_bysqlid.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashrec_bysqlid.sql
rem Purpose: Show DB Time and AAS at X-second intervals from V$ACTIVE_SESSION_HISTORY
rem Granluarity: By SQL_ID + wait event or “CPU/CPU + CPU Wait”
rem Notes: At X-sec intervals, where X = Entered time in seconds
rem #################################################################################
col samplestart format a19 head ‘Begin Time’
col samplesend format a19 head ‘End Time’
col sql_id format a15 head ‘SQL ID’
col tc format a30 head ‘Component’
col dbtw format 999.99 head ‘DB Time|Per Metric’
col smpcnt format 999.99 head ‘DB Time|Total’
col aas format 999.99 head ‘AAS|Total’
col epct format 999.99 head ‘% per Sample’
col aas_comp format 999.99 head ‘AAS per|Metric’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
 between :btime and :etime;
break on samplestart on sampleend on smpcnt on aas
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(&&interval_secs/86400) FROM xtimes WHERE xdate+(&&interval_secs/86400) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
        to_char(xdate+&&interval_secs/86400,’DD-MON-RR HH24:MI:SS’) sampleend,
        sql_id,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’))) smpcnt,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’)))/&&interval_secs aas,
       nvl(event,’*** IDLE *** ‘) tc,
       decode(event,null,0,dbtw) dbtw,
       decode(event,null,0,dbtw)/&&interval_secs aas_comp
from (
        select s1.xdate,event,count(*) dbtw,sql_id,count(ash.smpcnt) smpcnt
          from (
                select  sample_id,
                        sample_time,
                        session_state,sql_id,
                        decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) event,
                        count(sample_id) over (partition by sample_id) smpcnt
                from v$active_session_history
                where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
                        between :btime and :etime
                )  ash,
                (select xdate
                from xtimes ) s1
        where 1=1
        and ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_secs/86400)
        group by s1.xdate,event,sql_id)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
undefine interval_secs
Script: dbt_ashpast_bysqlid.sql
@br
@reset
rem #################################################################################
rem Name: dbt_ashpast_bysqlid.sql
rem Purpose: Show DB Time and AAS at X-min intervals from dba_hist_active_sess_history
rem Granluarity: By SQL_ID + wait event or “CPU/CPU + CPU Wait”
rem Notes: At X-min intervals, where X = Entered time in minutes
rem #################################################################################
col samplestart format a19 head ‘Begin Time’
col samplesend format a19 head ‘End Time’
col sql_id format a15 head ‘SQL ID’
col tc format a30 head ‘Component’
col dbtw format 9999999.99 head ‘DB Time|Per Metric’
col smpcnt format 999999.99 head ‘DB Time|Total’
col aas format 999.99 head ‘AAS|Total’
col epct format 999.99 head ‘% per Sample’
col aas_comp format 999.99 head ‘AAS per|Metric’
col bt NEW_VALUE _bt NOPRINT
col et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from dba_hist_active_sess_history
where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
between :btime and :etime;
break on samplestart on sampleend on smpcnt on aas
WITH xtimes (xdate) AS
  (SELECT to_date(:btime) xdate FROM dual
  UNION ALL
  SELECT xdate+(&&interval_mins/1440) FROM xtimes WHERE xdate+(&&interval_mins/1440) <= :etime
  )
select to_char(xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
        to_char(xdate+&&interval_mins/1440,’DD-MON-RR HH24:MI:SS’) sampleend,
        sql_id,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’))) smpcnt,
       (sum(decode(event,null,0,dbtw)) over (partition by to_char(xdate,’DD-MON-RR HH24:MI:SS’)))/60/&&interval_mins aas,
       nvl(event,’*** IDLE *** ‘) tc,
       decode(event,null,0,dbtw) dbtw,
       decode(event,null,0,dbtw)/60/&&interval_mins aas_comp
from (
        select s1.xdate,event,10*count(*) dbtw,sql_id,count(ash.smpcnt) smpcnt
          from (
                select  sample_id,
                        sample_time,
                        session_state,sql_id,
                        decode(session_state,’ON CPU’,’CPU + CPU Wait’,event) event,
                        10*(count(sample_id) over (partition by sample_id)) smpcnt
                from dba_hist_active_sess_history
                where to_date(to_char(sample_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)
                        between :btime and :etime
                )  ash,
                (select xdate
                from xtimes ) s1
        where 1=1
        and ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_mins/1440)
        group by s1.xdate,event,sql_id)
order by 1,dbtw desc
/
undefine btime
undefine etime
undefine bt
undefine et
undefine interval_mins
Script: dbt_awr_bytype.sql
@brsnap
@reset
rem #################################################################################
rem Name: dbt_awr_bytype.sql
rem Purpose: Show DB Time and AAS per AWR Snapshow
rem Granluarity: By wait class or “CPU/CPU + CPU Wait”
rem #################################################################################
col snap_id format 99999 head ‘Snap ID’
col bet format a15 head ‘Snap Time’
col stat_name format a30 head ‘Component’
col v format 999,999,999.99 head ‘DB Time|Component’
col dbt format 999,999,999.99 head ‘DB Time’
col pctdbt format 999.99 head ‘% DB Time’
col aas format 999.99 head ‘AAS’
set lines 120
break on snap_id  on bet on dbt
select snap_id,bet,max(v) over (partition by snap_id) dbt,
stat_name,v,round(100*(v/(max(v) over (partition by snap_id))),2) pctdbt,
v/secs aas
from  (
select snap_id,to_char(begin_interval_time,’DD-MON-RR HH24:MI’) bet,
stat_name,sum(v) v , secs from (
select t1.snap_id,s1.begin_interval_time,
86400*(to_date(to_char(s2.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)-
to_date(to_char(s1.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)) secs,
t1.stat_name,
(t2.value-t1.value)/1000000 v
from dba_hist_sys_time_model  t1,
     dba_hist_sys_time_model t2,
     dba_hist_snapshot s1,
     dba_hist_snapshot s2
where t1.stat_name=’DB time’
and s1.dbid =(Select dbid from v$database)
and s1.dbid=s2.dbid
and s1.instance_number=s2.instance_number
and t1.stat_name=t2.stat_name
and s2.snap_id=s1.snap_id+1
and s2.snap_id=t2.snap_id
and s1.snap_id=t1.snap_id
and s2.instance_number=t2.instance_number
and s2.dbid=t2.dbid
and s1.instance_number=t1.instance_number
and s1.dbid=t1.dbid
and s1.snap_id between :bid and :eid
union
select t1.snap_id,s1.begin_interval_time,
86400*(to_date(to_char(s2.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)-
to_date(to_char(s1.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)) secs,
t1.stat_name,
(t2.value-t1.value)/1000000 v
from dba_hist_sys_time_model  t1,
     dba_hist_sys_time_model t2,
     dba_hist_snapshot s1,
     dba_hist_snapshot s2
where t1.stat_name=’DB CPU’
and s1.dbid =(Select dbid from v$database)
and s1.dbid=s2.dbid
and s1.instance_number=s2.instance_number
and t1.stat_name=t2.stat_name
and s2.snap_id=s1.snap_id+1
and s2.snap_id=t2.snap_id
and s1.snap_id=t1.snap_id
and s2.instance_number=t2.instance_number
and s2.dbid=t2.dbid
and s1.instance_number=t1.instance_number
and s1.dbid=t1.dbid
and s1.snap_id between :bid and :eid
union
select e1.snap_id,s1.begin_interval_time,
86400*(to_date(to_char(s2.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)-
to_date(to_char(s1.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)) secs,
e1.wait_class,
(e2.time_waited_micro_fg – e1.time_waited_micro_fg)/1000000 v
from dba_hist_system_event e1,
     dba_hist_system_event e2,
     dba_hist_snapshot s1,
     dba_hist_snapshot s2
where s1.dbid=(Select dbid from v$database)
and s1.dbid=s2.dbid
and s1.instance_number=s2.instance_number
and e1.wait_class <> ‘Idle’
and e1.wait_class=e2.wait_class
and e1.event_id=e2.event_id
and e2.total_waits > nvl(e1.total_waits,0)
and s2.snap_id=s1.snap_id+1
and s2.snap_id=e2.snap_id
and s1.snap_id=e1.snap_id
and s2.instance_number=e2.instance_number
and s2.dbid=e2.dbid
and s1.instance_number=e1.instance_number
and s1.dbid=e1.dbid
and s1.snap_id between :bid and :eid
)
group by snap_id,to_char(begin_interval_time,’DD-MON-RR HH24:MI’),stat_name,secs)
order by snap_id, 5 desc
/
Script: dbt_awr_byevt.sql
@brsnap
@reset
rem #################################################################################
rem Name: dbt_awr_byevt.sql
rem Purpose: Show DB Time and AAS per AWR Snapshow
rem Granluarity: By wait class or “CPU/CPU + CPU Wait”
rem #################################################################################
col snap_id format 99999 head ‘Snap ID’
col bet format a15 head ‘Snap Time’
col stat_name format a40 head ‘Event’
col v format 999,999,999.99 head ‘DB Time|Component’
col dbt format 999,999,999.99 head ‘DB Time’
col pctdbt format 999.99 head ‘% DB Time’
col aas format 999.99 head ‘AAS’
set lines 120
break on snap_id  on bet on dbt
select snap_id,bet,max(v) over (partition by snap_id) dbt,
stat_name,v,round(100*(v/(max(v) over (partition by snap_id))),2) pctdbt,
v/secs aas
from  (
select snap_id,to_char(begin_interval_time,’DD-MON-RR HH24:MI’) bet,
stat_name,sum(v) v , secs from (
select t1.snap_id,s1.begin_interval_time,
86400*(to_date(to_char(s2.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)-
to_date(to_char(s1.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)) secs,
t1.stat_name,
(t2.value-t1.value)/1000000 v
from dba_hist_sys_time_model  t1,
     dba_hist_sys_time_model t2,
     dba_hist_snapshot s1,
     dba_hist_snapshot s2
where t1.stat_name=’DB time’
and s1.dbid =(Select dbid from v$database)
and s1.dbid=s2.dbid
and s1.instance_number=s2.instance_number
and t1.stat_name=t2.stat_name
and s2.snap_id=s1.snap_id+1
and s2.snap_id=t2.snap_id
and s1.snap_id=t1.snap_id
and s2.instance_number=t2.instance_number
and s2.dbid=t2.dbid
and s1.instance_number=t1.instance_number
and s1.dbid=t1.dbid
and s1.snap_id between :bid and :eid
union
select t1.snap_id,s1.begin_interval_time,
86400*(to_date(to_char(s2.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)-
to_date(to_char(s1.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)) secs,
t1.stat_name,
(t2.value-t1.value)/1000000 v
from dba_hist_sys_time_model  t1,
     dba_hist_sys_time_model t2,
     dba_hist_snapshot s1,
     dba_hist_snapshot s2
where t1.stat_name=’DB CPU’
and s1.dbid =(Select dbid from v$database)
and s1.dbid=s2.dbid
and s1.instance_number=s2.instance_number
and t1.stat_name=t2.stat_name
and s2.snap_id=s1.snap_id+1
and s2.snap_id=t2.snap_id
and s1.snap_id=t1.snap_id
and s2.instance_number=t2.instance_number
and s2.dbid=t2.dbid
and s1.instance_number=t1.instance_number
and s1.dbid=t1.dbid
and s1.snap_id between :bid and :eid
union
select e1.snap_id,s1.begin_interval_time,
86400*(to_date(to_char(s2.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)-
to_date(to_char(s1.begin_interval_time,’DD-MON-RR HH24:MI:SS’),’DD-MON-RR HH24:MI:SS’)) secs,
e1.event_name,
(e2.time_waited_micro_fg – e1.time_waited_micro_fg)/1000000 v
from dba_hist_system_event e1,
     dba_hist_system_event e2,
     dba_hist_snapshot s1,
     dba_hist_snapshot s2
where s1.dbid=(Select dbid from v$database)
and s1.dbid=s2.dbid
and s1.instance_number=s2.instance_number
and e1.wait_class <> ‘Idle’
and e1.event_name=e2.event_name
and e1.event_id=e2.event_id
and e2.total_waits > nvl(e1.total_waits,0)
and s2.snap_id=s1.snap_id+1
and s2.snap_id=e2.snap_id
and s1.snap_id=e1.snap_id
and s2.instance_number=e2.instance_number
and s2.dbid=e2.dbid
and s1.instance_number=e1.instance_number
and s1.dbid=e1.dbid
and s1.snap_id between :bid and :eid
)
where  v > 1
group by snap_id,to_char(begin_interval_time,’DD-MON-RR HH24:MI’),stat_name,secs)
order by snap_id, 5 desc
/
Notes about the scripts
br.sql, brsnap.sql, and reset.sql are poached from ashrpti.sql and awrrpti.sql – drop me a note if you want these as well
Wrapping Up
Play around in Enterprise Manager.  Don’t be afraid to get comfortable with ADDM, he’s your friend.  Try these scripts out and match up the results with what you see in EM.  It’ll be fun.