RE: Database tracking

[Orr, Steve]

Title: RE: Database tracking

Yes. Stay tuned... about 2-3 months.  -----Original Message----- From: Leonard, George [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: Database tracking Are you willing to share the solution with us ?   George ________________________________________________ George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Cell: (+27) 82 655 2466 Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web:   http://www.didata.co.za   You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -----Original Message----- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 18: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   -----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