As a DBA, how many times have you been asked... “what happened at 3:00am today, everything slowed down?” Try answering this question with V$SESSION_WAIT – you can’t. Try answering by looking at AWR, Automatic Workload Repository, or Statpack reports – you may be able to make some guesses, but they’d only be guesses. Try answering with data in the alert log or data in O/S logs – you’ll only be able to answer this question if you get very lucky. Try answering by looking at Oracle Enterprise Manager Grid Control 10g. Well actually, this gets you on the right track, because Grid Control will show you ASH, Activity Session History data!
ASH is built into the kernel in Oracle 10g and MMON and MMNL do the work! ASH collects data every second from V$SESSION, V$SESSTAT, V$SESSION_WAIT, etc, and populates V$ACTIVE_SESSION_HISTORY. An hour of data is kept in memory for easy access, just query V$ACTIVE_SESSION_HISTORY (think of it as V$SESSION_WAIT plus some more stuff, but longer lasting). Data is flushed to disk every hour and externalized via views WRH$_<ASH name>. The collection interval and volume self-adjusts based on load. ASH will collect more aggressively when the system is busy, less so when not.
In short, ASH gives you a way to use all your “right now” tuning methods for time periods in the past. When someone asks you what happened at X:00AM when you were fast asleep, you have an option now. Give it a try!
Look for deeper dives into ASH in future posts. Stay tuned!
