Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> things that I did not know about statspack in 9.2
I was lucky enough to attend RMOUG and catch Tim Gorman's statspack
presentation, which was excellent. If you get to choose one Oracle
conference for the year, I would highly recommend RMOUG.
you can find papers here:
http://www.evdbt.com/papers.htm
although the presentation from rmoug is not yet there.
utility scripts here:
http://www.evdbt.com/tools.htm
I had no idea that segment statistics were available from a level 7 statspack snapshot in 9.2. The snapshot itself is much more expensive in terms of logical IOs, so you will likely want to set your thresholds higher than the defaults.
Note Note:209197.1 includes:
"Capturing a snapshot at i_snap_level>=6 will capture the execution
plans of
any SQL statements present in the library cache at the time the
snapshot is
taken, that have exceeded one of the SQL thresholds. "
If you suffer from having to extract, re-execute or explain plan the query to get an execution plan for a query that ran in the past, this is excellent news.
I was most impressed by seeing:
Top 5 Row Lock Waits per Segment for DB: EAS Instance: eas Snaps:
4931 -4932
-> End Segment Row Lock Waits Threshold: 100
Row
Subobject Obj. Lock Owner Tablespace Object Name Name TypeWaits %Total
---------- ---------- -------------------- ---------- ----------------- -------
<actual info removed>
and
Top 5 Physical Reads per Segment for DB: EAS Instance: eas Snaps:
4931 -4932
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical Owner Tablespace Object Name Name TypeReads %Total
---------- ---------- -------------------- ---------- ----------------- -------
<actual info removed>
in a statspack report.
have fun.
Pd Received on Fri Feb 13 2004 - 20:09:47 CST