Monday, April 18, 2011

What is the retention of ASH

Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second from v$session and stores them in a circular FIFO buffer in the SGA.
ASH records are very recent session history within the last 5-10 mins. (This may not true, it depends on how busy the database it is. I show this later).
The MMNL (Manageability Monitor light - shows up as "Manageability Monitor light 2" process) process, if the buffer fills up before the AWR flushes (by default every hour) the MMNL process will flush the data (in v$active_session_history) to disk (data stored in dba_hist_active_session_history). The is column called IS_AWR_SAMPLE to indicates whether this sample has been flushed or will be flushed to the AWR DBA_HIST_ACTIVE_SESS_HISTORY) (Y) or not (N)

ASH resides in the SGA and it’s size is calculated by the lesser of:

    * total # of cpu x 2MB memory
    * 5% of shared pool

SYS@TT> select min(sample_time), max(sample_time) from v$ACTIVE_SESSION_HISTORY ;

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
17-APR-11 11.43.44.860 PM
18-APR-11 02.49.08.124 PM

LIQY: Near 15 hours' data in memory.


SYS@TT> select min(sample_time), max(sample_time) from sys.WRH$_ACTIVE_SESSION_HISTORY ;

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
16-AUG-10 10.37.41.345 AM
18-APR-11 02.00.07.434 PM
SYS@TT> select  min(end_interval_time ), max(end_interval_time )  from dba_hist_snapshot ;

MIN(END_INTERVAL_TIME)
---------------------------------------------------------------------------
MAX(END_INTERVAL_TIME)
---------------------------------------------------------------------------
16-AUG-10 12.00.49.868 PM
18-APR-11 03.00.46.004 PM

LIQY:
1.) DBA_HIST_ACTIVE_SESSIONS_HISTORY is a view built on sys.WRH$_ACTIVE_SESSION_HISTORY.
2.) Observer that Historical ASH (on disk) is almost the same as ASH snapshots.


Testes that suceeds to generate ASH report within AWR retention, while fails to do so when the slot beyonds AWR retention.  Hence, I think ASH retention is same as AWR's.



References:
Active Session History (ASH)