Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Statspack Intervals

RE: Statspack Intervals

From: Jesse, Rich <>
Date: Wed, 10 Jul 2002 09:18:36 -0800
Message-ID: <>

Hey Ian,

I'm a little confused (hopefully not just because of this stupid cold I'm fighting). Offending SQL in your DB is purged out of the shared pool in less than five minutes? I would think that joining V$SQLTEXT to V$SQLAREA, even after a particularly nasty query/DML was completed, would point to the offender(s).

Or am I missing something here (there's a good chance of that)?

Rich Jesse                           System/Database Administrator              Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Wednesday, July 10, 2002 11:13 AM
To: Multiple recipients of list ORACLE-L

Statspack is a very nice tool, however if one really wants to find problems lurking in a database even five minute intervals can be too long. By finding problems I mean locating inefficiencies proactively. As good as the first few chapters of "Database Tuning 101" are, the book preaches a reactive and not a proactive tuning method. Both are needed.  

I have reached the conclusion that some data such as that in V$SESSION_WAIT and V$SQLTEXT should be collected each minute. I don't mean to collect everything just the active sessions and those that have been idle for a minute or less. It would be nice to collect session stats every minute as well, but that becomes too expensive. I choose to collect the session stats which best mirror what tkprof puts out.  

Running statspack is not ideal for this. It will record information which I do not need that fine-grained. I do however need it for comparison purposes.  

I can better answer questions such as who is accessing the data, what fields are being accessed, what is the query plan used, how expensive is the access, etc. I can also better answer questions, such as, "One of my overnight jobs ran very slowly, can you tell me why?"

Please see the official ORACLE-L FAQ:

Author: Jesse, Rich

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jul 10 2002 - 12:18:36 CDT

Original text of this message