>>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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 20 2006 - 09:06:43 CST
