Pages

Tuesday 10 January 2012

SOME TIPS ABOUT 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. 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 to disk (data stored in dba_hist_active_session_history).
ASH resides in the SGAand it’s size is calculated by the lesser of:

  • total # of cpu x 2MB memory
  • 5% of shared pool
So on a 16 cpu server with a shared pool of 500MB

  • Ash desired size 16 x 2MB = 32MB
  • 5% of 500MB = 25MB (this is the lower so ASH will be 25MB)
ASH collects the following:

  • SQL_ID
  • SID
  • Client ID, Service ID
  • Program, Module, Action
  • Object, File, Block
  • Wait event number, actual wait time (if session is waiting) 
Some hidden parameters to control the behaviour of ASH-

To enable or disable ASH data collection-
_ash_enable=TRUE


_ash_sampling_interval = 1000 (milliseconds)
This is where the one second sampling is specified. I'm tempted to try to reduce this to a silly level and watch the server fall on it's back-side.

_ash_sample_all = FALSE

Oooh, this one would be fun! Why not sample all sessions include those that aren't Active (Hint, you would have an enormous growth in the volume of data generated so, again, I'm kidding)

_ash_disk_write_enable = TRUE

Whether samples are flushed to the workload repository or not. Might initially seem a good idea to save space or improve performance a little but (just a guess) I think it would confuse the hell out of ADDM when it couldn't find any ASH samples to correlate with the AWR information.

_ash_disk_filter_ratio = 10

No comments: