Active Session History (ASH) Reports
ASH is Helpful to identify performance issues in old sessions.
ASH Collects samples of active sessions every second (waiting for non-idle events, or on the CPU working) from v$sessions (inactive sessions are not captured).
Sampled data collected to circular buffer in SGA and the same can be accessed through v$views. V$ACTIVE_SESSION_HISTORY view provides the sampled session activity for the instance.
Using the Active Session History you can examine and perform the detailed analysis on the current data in the v$ACTIVE_SESSION_HISTORY and the past data in the DBA_HIST_ACTIVE_SESS_HISTORY view.
ASH report is a small report compared to the AWR report which will provide the db/instance details for a short period of time.
ASH Report Covers a much shorter period of time (e.g 5 min) compared to an AWR Report (e.g 30 min or 1 hour) .
ASH Report captures the following things :
• SQL Identifier of SQL Statement.
• Object Number, File number and block number
• Wait event identifier and session serial number
• Module and Action name.
• Client identifier of the session
• Service hash identifier
Major ASH Report sections are :
Top User events
Top Background Events.
Top Cluster Events.
Top Sql Command Types
Top Phases Of Execution
Top Remote Instances
Top SQl With Top Events.
2. Ways Of Gathering ASH :
2.1 . Using ORADEBUG :
ASHDUMP needs tobe collected During the time of the issue.
In case collecting from RAC, Gather ASH Dumps from All Database instances at approximately the same time
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump ashdump 5 # this will gather 5 minutes of ASH data , you may increase this if you feel necessary but try to keep it under 1 hour.
SQL> oradebug tracefile_name # Displays the trace file
2.2 Using ashrpt.sql or enterprise manager
Enter Value for Report type : HTML Or TEXT [HTML is default.]
Enter Value for Begin time : default to -15 min
To specify absolute begin time : [dd-mm-[yy]] HH24:MI[:ss]
To specify relative begin time : (start with ‘-’ sign ) – [HH24:]MI
Enter Value for duration : default is till current time.
Enter value for report_name :
2.3 Using v$active_session_history
3. desc v$active_session_history
4. ASH parameters :