Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Statspack Intervals

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Wed, 10 Jul 2002 09:28:38 -0800
Message-ID: <F001.00493CF9.20020710092838@fatcity.com>


Hi Ian,
If you need that level of granularity and if you have a budget, you probably want to look at one of the tools that sits on the server and monitors the SGA directly. How does the idea of collecting stats every couple seconds without impacting database performance sound? In a previous life (at a company with a budget), I used Precise to good effect. In an later previous life I compared Precise against the Quest "me too" tool (the name escapes me at the moment) and found them to both be a huge improvement over estat/bstat or statspack. Of course, the price of that huge improvement was significant -- glad it didn't come out of my paycheck 8-) Kevin Kennedy
First Point Energy Corporation

-----Original Message-----
Sent: Wednesday, July 10, 2002 9: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?"



Much of this monitoring could be replaced by proper code review.  

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu    

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

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.

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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: kkennedy
  INET: kkennedy_at_firstpoint.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:28:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US