Master in DevOps | New Batch Starting from 19 December, 2024 @ 9 PM IST | Register for Free Demo

Know More

ASH Report

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 12 Dec, 2021
  • 0 Comments
  • 2 Mins Read

ASH Report

How to generate an ASH report from Oracle Database?


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)

  • 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 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

  • Retention is how long snapshots should remain in sysaux tablespace before they are purged out.




RMAN Cloning from prod to test server
AWR Report