RE: Database tracking

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_at_p        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    s.net>               cc:                                                                        
                    Sent by:             Subject:     RE: Database tracking                                         
                    root_at_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:sorr_at_rightnow.com]
     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: Rajesh.Rao_at_jpmchase.com

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