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
-----Original Message-----
From: Terrian, Tom (Contractor) (DAASC) [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 12:52 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database tracking
Ok, thanks
-----Original Message-----
Sent: Wednesday, January 15, 2003 2:10 PM
To: Multiple recipients of list ORACLE-L
Tom - I'll provide an example of what we do and maybe it will give you some
ideas. On one database, the users have identified a certain process that has
marginal performance at best, and when anything gets out of whack it gets bad
real fast. So the developers have added a logging feature in the application.
When the user hits "submit", that is logged, along with the username and other
relevant data. When the results are returned to the user, that is also logged.
Now we have a measurement from the user's perspective. This has allowed us to
detect problems a number of times before they were serious. When the users have
complained about intolerable performance it has given us some actual numbers to
review (rather than opinions or impressions).
I think you have a good idea, but if possible you should go end-to-end,
rather than just the database. I would go with STATSPACK snapshots if you just
want to look at the server. That gives you an overall server status, while a
single query may not detect a lot of severe problems.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, January 15, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L
All, I would like to track the performance of my production databases by running
the same SQL statement against each database every 5 minutes or so and recording
the results. For example:
sql> set timing on;
sql> select count(*) from dba_tables;
That was I would know if they are getting faster or slower over time. As anyone
already done this? Would there be a good SQL statement to use?
Thanks,
Tom Terrian
