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
order by begin_interval_time desc
DBID INO BEGIN_INTERVAL_TIME END_INTERVAL_TIME
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.
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.