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
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 -----
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.
