Systems Engineering and RDBMS

Creating an adhoc ASH (Active session history) report using timeframe

Posted by decipherinfosys on July 31, 2009

In one of our previous blog post, we demonstrated that how can we generate adhoc awr report based on the given range of snap_id.  As we are aware that default timeframe for collecting snapshot data is every hour and data is retained up to 7 days. Now let us assume that snapshot is taken every hour but we want some statistics within one hour period let’s say between 1:15 PM and 1:30 PM or between 2:17 PM to 2:48 PM etc. How can we generate report for such time frame? Again we can resort back to DBMS_WORKLOAD_REPOSITORY package. This package has quite a few procedures, which can help us out to troubleshoot the problem. Keep in mind that Oracle always recommends using awrrpt.sql and ashrpt.sql to generate the AWR or ASH reports. In this blog post, we will see once again how we can get the data for specific time range using ASH_REPORT_TEXT procedure.

ASH data is sampled every second and whenever AWR snapshot is flushed to the disk, content from v$active_session_history is also flushed to the disk. It system during that time is heavily utilized, then only sample data will be flushed to the disk.  Historical data is stored in another view called DBA_HIST_ACTIVE_SESS_HISTORY. Now let us get the data:

select dbid,instance_number ino,begin_interval_time,end_interval_time
from dba_hist_snapshot
order by begin_interval_time desc


2234839021    1    7/29/2009 5:00:46.793 PM    7/29/2009 5:30:37.953 PM
2234839021    1    7/29/2009 4:30:55.638 PM    7/29/2009 5:00:46.793 PM
2234839021    1    7/29/2009 4:00:01.491 PM    7/29/2009 4:30:55.638 PM
2234839021    1    7/29/2009 3:30:10.348 PM    7/29/2009 4:00:01.491 PM

Now, we know the value of dbid, instance_number and interval start time and interval end time. We can take this time as is and run report for the time range or we can run report for in between time range as well. In this case, we will run report for in between time range. But before that here is the signature of the procedure.

l_dbid          IN NUMBER,
l_inst_num      IN NUMBER,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL)
RETURN awrrpt_text_type_table PIPELINED;

We already know the value for l_Dbid, l_instnum, l_btime and l_etime from the first sql. For rest of the parameters we will go with the default values. Here is the sql to run for a specific time range.

SELECT output
to_Date('07/29/2009 16:10','MM/DD/YYYY HH24:MI'),
to_date('07/29/2009 16:25','MM/DD/YYYY HH24:MI'))

We can spool the output to the file from above sql and open it up as a text file to investigate the results. Here is the sample that confirms that report is generated for a specified time frame.


Analysis Begin Time:   29-Jul-09 16:10:00
Analysis End Time:   29-Jul-09 16:25:00
Elapsed Time:        15.0 (mins)
Sample Count:         527
Average Active Sessions:        0.59
Avg. Active Session per CPU:        0.05
Report Target:   None specified

As mentioned earlier, policy to collect AWR data varies from company to company as each company has its own set of standards to follow.


  • Oracle-base article – here.
  • Oracle 10g Performance Tuning guide – here.

One Response to “Creating an adhoc ASH (Active session history) report using timeframe”

  1. brunocfnba said

    It’s a very good post! I’d like to add your blog in my blog roll on

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: