Skip to main content

RDBMS Forensics: Troubleshooting using ASH

[Author Note: Blogger app for IOS doesn't have all the nifty formating features of the web. I'll try to clean up later.]

Tim Gorman, Evergreen Database Technologies (EvDBT.com)

Three Case studies
- WHen someone complains of a performance problem or an error (but not happening right now)

Active Session History is a sampling mechanism
MMNL process scraps data about active sessions from session state in SGA to the ASH buffers in SGA (MMON light process)

parameter _ASH_SAMPLING_INTERVAL defaults to 1000 (usecs)

At hourly intervals, or as ASH buffers get more than 66% full, one of the 10 samples of ASH are writtent to partitioned tables in AWR in SYSAUX.
- configure AWR retension to mirror business cycles (quarterly, monthly, etc)

ASH is not a trace or audit trail. But it's not perfect. Only queries that are run in more than 1 second are captured in buffer. 

DBA_HIST_ACTIVE_SESS_HISTORY is samples of ASH taken every 10 seconds. 

[Updated: 2:38pm PST]


V$EVENT_NAME to look at events 

Long Running SQL Statement 
- we know what execution plan looks like
- we need to know the actuals

We can get this with SQL PLan Monitor
- Elapsed time, CPU Time
- number of i/o volume of data read and written
- volumne of PGA memory used

v$SQL_PLAN_MONITOR
V$SQL_MONITOR


effdt and effseq processing using 
row_number() over (partition by seqno order by effdt) -- maybe?

ASH_XPLAN (available from here: EvDBT.com/scripts) can be used to add timings to each step of an exection plan, using timings based on ASH. 



Comments