| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database tracking
Are you willing to share the solution with us ?
George
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-----
Sent: 16 January 2003 18:55 PM
To: Multiple recipients of list ORACLE-L
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/
<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-----
<mailto:tterrian_at_daas.dla.mil> ]
Sent: Wednesday, January 15, 2003 12:52 PM
To: Multiple recipients of list ORACLE-L
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.
dwilliams_at_lifetouch.com
-----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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Leonard, George INET: george.leonard_at_farnell.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Fri Jan 17 2003 - 03:23:53 CST
---------------------------------------------------------------------
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).
 |
 |