Re: statspack snap level 7 as execution plan rep

  • From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
  • To: ax.mount@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 20 Nov 2006 07:06:43 -0800 (PST)

>>I wonder if any of you use statspack to store a execution plan repository? 
>>When run in level 7. Is it worthy? Oracle 9.2.0.8 in HP-UX.

Alex, I've used statspack (3 way RAC on 9.2.0.5) to record level 7 including 
plans - in the course of a (lengthy, automated and very repeatable) performance 
testing and tuning exercise, rather than in production. The main benefit for me 
was detection of plan changes (expected or unexpected, as a result of index, 
parameter or SQL tweaking). Secondary benefits were around impact / usage 
analysis: which tables / indexes did we believe we had accessed, and when? 
Which seemed to be useful? When did we start or stop using an index?

By building a few shell scripts and a handful of simple reports / queries on 
top of the PERFSTAT tables we could:
- kick off snaps on demand or at preset intervals on all RAC instances
- aggregate snaps over the RAC instances (within a few seconds)
- collect iostat (memory, disk, cpu etc) statistics from the underlying Linux 
servers (RAC and/or OC4J) and combine that into the PERFSTAT schema
- combine the lot into Excel for slicing, dicing and graphing

HTH

Regards Nigel

Other related posts: