Statspack is all good to troubleshoot problems. If my boss had his way, I
would be looking at the statspack reports every 15 minutes. But for me to
be proactive as a DBA, I need bells and whistles. A user just executed a
query which does 14 full table scans. Other than the monitoring tools
available with all the glitzy ratio crap, what do you folks do to alert you
to such problems? Do you wait for a screaming user to call? Just curious.
Are there any tools out there, which look at statspack reports, or
concentrates on wait events?
Regards
Raj
"Post, Ethan"
<Ethan.Post@p To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
s.net> cc:
Sent by: Subject: RE: Database tracking
root@fatcity.
com
January 16,
2003 03:40 PM
Please
respond to
ORACLE-L
I concur, I have used MRTG for this in the past, now I have an Access
database that I connect to via ODBC and get my graphs. They are very handy
for trending and analysis, If someone says I had a problem yesterday around
3 pm I go look at the charts and I usually see something out of whack. I
can also look at my data over longer periods just like MRTG and RRD, I
basically copied the storage logic I saw in those tools so I get a lot of
history without the overhead of a ton of storage space. I opted to keep my
data in the database because I can literally install the whole system in
about 2 minutes on the average database. With MRTG and RRD it takes a bit
more to get things set up.
I have another system which stores 25 stats from the from the tables
mentioned below in a single row and takes a snapshot every hour. 365 days
* 24 rows per day is not really that much storage. This allows me to
quickly determine what is increasing (V$SYSSTAT) and what is the impact
(V$SYSTEM_EVENT). This can also be deployed in a couple of minutes on the
average database.
Finally I usually determine some other type of metric to gather data on,
for example, J.D. Edwards OneWorld performance will be most impacted by
batch jobs which are listed in the F986110 table. I have an report which
gets the # of jobs, total run time and average run time for grouped by job.
I can quickly see if particular jobs need to be tuned, are running more
often or just taking longer for some reason.
Most systems have some key components which impact performance the most. I
use of mix of the options above depending on the requirements at hand. As
far as alerting goes I pretty much send everything to the Oracle alert log
and I have a very nice script which allows me to respond to various
patterns in the file including running commands, sending email/pages or
just logging the event somewhere else.
-----Original Message-----
From: Orr, Steve [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 12:55 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database tracking
I concur with the recommendation to use STATSPACK but you might want
to augment it. I take STATSPACK snapshots every 15 minutes and if
there's a performance problem caused by a few bad queries I can
usually isolate the offenders. But constant fined-grained STATSPACK
snapshots can be a lot of overhead so you may want something more
lightweight.
I've developed a DBA web app which queries V$SYSSTAT and
V$SYSTEM_EVENT every minute. I assume regular queries on these tables
do not impact system performance enough to worry about. I record the
result sets from these queries outside of Oracle in a very light
weight RRDTool "round robin database." (RRDTool is free,
http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/.) From this I can
produce 55 graphs on demand for 5 different time spans: daily; weekly;
monthly; quarterly; and yearly. Of course damagement loves
graphs/pictures. The storage needed for one plus year's worth of
minute to minute V$SYSSTAT/V$SYSTEM_EVENT query data only comes to
3.2MB for each database instance being monitored. A cool thing to do
is produce a graph with a visually obvious spike in some
V$SYSTEM_EVENT wait statistic at say 3:15PM yesterday then correlate
that graphic spike to a specific problem query as recorded in
STATSPACK. It provides nice "smoking gun" incriminating evidence to be
used for putting duhvelopers on trial.
Steve Orr
Bozeman, MT
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
