RE: Database tracking

[Post, Ethan]

Title: RE: Database tracking

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