ASH reports give a quick glance of how the database has performed between two snapshots. It mainly highlights the following areas: - Top User Events (frequent wait events) Top User Events (frequent wait events) - Details to the wait events Details to the wait events - Top Queries Top Queries - Top Sessions Top Sessions - Top Blocking Sessions Top Blocking Sessions - Top DB Objects Top DB Objects - Activity Over Time Activity Over Time Top User Events (frequent wait events) Details to the wait events Top Queries Top Sessions

Top Blocking Sessions Top DB Objects Activity Over Time To generate ASH report, DBA needs to perform the following: 1) Login to DB Server as oracle 2) export ORACLE_SID=PrimeDG

3) cd $ORACLE_HOME/ rdbms/admin 4) sqlplus / as sysdba 5) @ashrpt.sql (or) @$ORACLE_HOME/rdbms/admin/ashrpt.sql 6) Enter value for report_type: html Note: Since, we want to open the file in nicely formatted way, we pick html format.

7) Enter value for begin_time: <enter> Note: Default is last 15 minutes. If you need to change to non-default value, feel fre to change the value. You can change the value as below. - If the requirement is from past 30 minutes then enter: -30 If the requirement is from past 30 minutes then enter: -30 - if the requirement is from past 1 ½ hours then enter: -1:30 if the requirement is from past 1 ½ hours then enter: -1:30 - if the requirement is from past 1 day, then enter: -24:00 if the requirement is from past 1 day, then enter: -24:00 If the requirement is from past 30 minutes then enter: -30 if the requirement is from past 1 ½ hours then enter: -1:30 if the requirement is from past 1 day, then enter: -24:00

8) Enter value for duration: 5 Note: I used 5 minutes as the end time from the begin time specified in Step-6.. Feel free to change the value as per your need. 9) Enter value for report_name: Sample_ASH_testing.html 10.Ash report is Generated To pull the report, download a tool call WinSCP. 10) Start WinSCP and configure it connect to server with “oracle” username.

11) Copy the file form UNIX (right side) to Windows (left side). 12) Double click the file to open the report in browser Sample ACTIVITY:- For your Reference: How to check the snapshot IDs: prompt prompt prompt Enter the number of days to look for snapshot IDs prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ select dhdi.instance_name, dhdi.db_name, dhs.snap_id, to_char(dhs.begin_interval_time,’MM/DD/YYYY:HH24:MI’) begin_snap_time, to_char(dhs.end_interval_time,’MM/DD/YYYY:HH24:MI’)   end_snap_time, decode(dhs.startup_time,dhs.begin_interval_time,’**db restart**’,null) db_bounce from dba_hist_snapshot dhs, dba_hist_database_instance dhdi where dhdi.dbid             = dhs.dbid and dhdi.instance_number  = dhs.instance_number and dhdi.startup_time     = dhs.startup_time and dhs.end_interval_time >= to_date(sysdate – &&num_days_back) order by db_name, instance_name, snap_id;

To check for a specific snapshot ID set linesize 200 select dhdi.instance_name, dhdi.db_name, dhs.snap_id,

to_char(dhs.begin_interval_time,’MM/DD/YYYY:HH24:MI’) begin_snap_time, to_char(dhs.end_interval_time,’MM/DD/YYYY:HH24:MI’)   end_snap_time, decode(dhs.startup_time,dhs.begin_interval_time,’**db restart**’,null) db_bounce from dba_hist_snapshot dhs, dba_hist_database_instance dhdi

where dhdi.dbid=dhs.dbid and dhdi.instance_number=dhs.instance_number and dhdi.startup_time=dhs.startup_time and dhs.snap_ID=&enter_snapid; How to check the retention time (how long snapshots will stay in sysaux tablespace before they get purged)?

col snap_interval format a20 col retention format a20 select snap_interval, retention from

dba_hist_wr_control; Where: - Snap_Interval is the interval between snapshots and Snap_Interval is the interval between snapshots and - Retention is how long snapshots should remain in sysaux tablespace before they are purged out. Retention is how long snapshots should remain in sysaux tablespace before they are purged out. Snap_Interval is the interval between snapshots and Retention is how long snapshots should remain in sysaux tablespace before they are purged out.