Active Session History (ASH) Reports

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 03 Oct, 2022
  • 0 Comments
  • 1 Min Read

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