Blog
Active Session History (ASH) Reports
Active Session History (ASH) Reports
1. Overview
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 Service/Module
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
ashrpt.sql
SQL> @?/rdbms/admin/ashrpt
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 :
_ash_size
_ash_enable
_ash_sampling_interval
_ash_disk_write_enable
_ash_disk_filter_ratio