Database Monitoring

Mark D Powell · *nix forums Guru Joined: 23 Apr 2005 Posts: 701

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 --

Ah Ming · *nix forums beginner Joined: 03 Feb 2005 Posts: 7

Hi!

I have a question about database monitoring. My situation is I have
a single instance with single user for storing point of sale data.
There are some reporting tools running in client sides. However, the
overall peformance of database is slow at a instance(CPU occupied more
than 80% and memory are nearly full). May I have some SQL statements
can "KNOW" which report has a problem? Thanks!

Kai Ming

Google