Oracle-L: RE: Statspack Intervals

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 10 Jul 2002 14:43:20 -0800
Message-ID: <F001.0049461F.20020710144320@fatcity.com>

Thanks!! I too have a system which is evolving. I'm trying to balance the capture needs against impacting the database. One doesn't want one's monitoring program to be a resource hog. Besides capturing the SQL I glom it together and send it off to explain plan. In 9i I'll switch to using V$PLAN or whatever its called.

When I arrive at work I check on what happened the previous day. I want to know what the most expensive queries were by such things as disk and logical reads. I want to know which queries suffered the greatest waits. I want to exclude queries which were run as part of an export dump. Statspack gives me only part of what I want.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

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

Ian,

I've done something similar in troubleshooting a rather poorly behaved app. I wanted to record all stats for particular sessions, and take frequent snapshots, every minute in my case.

See http://www.cybcon.com/~jkstill/util/perfmon/perfmon.html for the PL/SQL packages I used to do it, if interested.

Jared

"MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU> Sent by: root_at_fatcity.com
07/10/2002 09:13 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Statspack Intervals

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.
----- Original Message -----
To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 10, 2002 11:43 AM

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:
INET: Jared.Still_at_radisys.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).
--

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

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------