Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Statspack Intervals
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 Rich.Jesse_at_qtiworld.com 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: http://www.orafaq.com
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com
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: ListGuru_at_fatcity.com (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