Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Database Monitoring
Kia, Oracle provides a set of scripts/packages called statspack that
you can run as a starting point.
Look in $ORACLE_HOME/rdbms/admin/ at the spcreate.sql script. You can find information on the use of the package in the documentation set.
You can also scan v$sqlarea for SQL statements that have high relative to the average value for all SQL on your system buffer_gets or disk_reads. This would be logical and physical IO respectively.
There are a dozen other things you can do, but I would just scan the SQL looking for the biggest resource users so that I could then check the associated SQL (explain) plain. And I would run the statspack report to get overall system numbers.
Bad overall numbers can be the result of only a few very poorly performing queries so do not get too bogged down on the statspack report. And when you look at IO numbers for queries remember that a high logical IO value may be the result of the need to do a lot of work.
HTH -- Mark D Powell -- Received on Thu Feb 10 2005 - 07:52:32 CST