RE: Statspack Intervals

[MacGregor, Ian A.]

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?" ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Much of this monitoring  could be replaced by proper code review.    Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED]      -----Original Message----- From: Anjo Kolk [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 10, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Subject: Re: Statspack Intervals Lee,   The rule of thumb is:   Monitoring: 15 to 30 minute interval   Problem solving: 5 to 15 minute interval.   Really wrong are intervals of an hour or so(only usefull for comparision)   Anjo. ----- Original Message ----- From: Robertson Lee - lerobe To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 10, 2002 11:43 AM Subject: Statspack Intervals All,   I've just been rolling out statspack against all of our production DBs. What I am not sure about is how often I should take a snapshot ? I am planning on holding one months worth of data and then backing it up and then purging.   How often do you guys take snapshots and does my history/purge plan sound reasonable.   Regards and TIA   Lee   ********************************************************************* The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.