Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> things that I did not know about statspack in 9.2

things that I did not know about statspack in 9.2

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 13 Feb 2004 18:09:47 -0800
Message-ID: <1ac7c7b3.0402131809.17a9b69f@posting.google.com>


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       Type        
Waits %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       Type        
Reads %Total
---------- ---------- -------------------- ---------- -----
------------ -------

<actual info removed>

in a statspack report.

have fun.

Pd Received on Fri Feb 13 2004 - 20:09:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US