[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
Post a Comment