Active Session History (ASH)

Published by Ahmad on

The Oracle wait event model was originally introduced in Oracle7.

Wait event information is gathered by the Oracle MMON background process, stored in intermediate x$ structures in a circular SGA buffer, and later transferred to Oracle10g dba_hist tables.

Oracle 10g introduced the Active Session History (ASH) as part of the Diagnostics and Tuning Pack. 

The new Automated Session History (ASH) tables revolutionized Oracle wait event tuning. 

ASH gathers sampled data at the session level rather than at the instance level. 

The ASH, AWR, and ADDM are separate components of the Oracle Enterprise Manager Diagnostic Pack which must be licensed as a separate option.

$ORACLE_HOME/rdbms/admin/ashrpt.sql.

  • Part of Oracle 10g.
  • On by default.
  • Licensed as part of the Diagnostic Pack.
  • Samples each active database session every second.
  • Data is held in a buffer in memory.
  • Built into the Oracle kernel and accessed through the v$active_session_history view.
  • Written to disk by AWR snapshots.
  • 10-second samples in AWR.
  • In an AWR snapshot, 1 row in 10 from the ASH buffer is placed into the AWR repository.
  • Managed by the MMNL, (Memory Monitor Lite).
  • Should not be used to track occurrence.
  • ASH is always by time, not a snapshot.
  • Set start date and time.
  • End date and time in minutes.
  • Generate report.
  • Limiting from a 50 minute/1 hour view to a more definitive view of the database on a given timeline.
  • Top SQL.
  • Top Sessions.
  • Top Waits.
  • Blocking Sessions.
  • Top Objects.
  • Waits by time during sample intervals.
  • ASH provides most of the data that we use from SQL Trace/tkprof.
  • Using ASH greatly reduces the number of times that you need to resort to SQL Trace.
  • V$ACTIVE_SESSION_HISTORY.
  • DBA_HIST_ACTIVE_SESS_HISTORY.

Thanks

With Best Wishes!!!!

Ahmad

ACE Associate
Cloud Architect
Oracle Database Consultant

Certified in-: | 1XAzure | 6XOCI | 1XOCS (GG) | 1XOCE (RAC) | 3XOCP (11g,10g,9i) | 1XOCA (9i) |

My YouTube Channel (OracleRide)
http://www.youtube.com/c/OracleRide


1 Comment

Shivam Kamthan · May 8, 2021 at 2:24 pm

Very good article. Covered all the aspects related to ASH.

Leave a Reply to Shivam Kamthan Cancel reply

Your email address will not be published. Required fields are marked *