Shawn_Moore

CA Tuesday Tip: Introduction to the Database Performance Reports

Discussion created by Shawn_Moore Employee on Jan 11, 2011
Latest reply on Jan 14, 2011 by sanon01
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 1/11/2011

This in the 1st of several tips (over the next few weeks) where we will cover the usefulness of the various database monitoring/statistical reports.

In today's installment we will look at an overview of the various types of reports in both MS SQL Server and Oracle.

SQL Server

SQL Server has a number of useful reports available in the SQL Server Management Studio. From the database explorer, right click the database name, then choose reports, standard reports and finally choose one of the following reports:

[list]
[*]Performance - Top Queries by Total CPU Time
[*]Performance - Top Queries by Average CPU Time
[*]Performance - Top Queries by Total IO
[*]Performance - Top Queries by Average IO
[list]

The "Total" based reports are good for looking at queries that may add up to high amounts of IO or CPU time, but in themselves don't appear to be a problem.

The "Average" based reports are good for looking for expensive single run(instance) queries.

Oracle:

AWR - This report was introducing in Oracle 10, and displays a number of top metrics, advisories and statistics about the Oracle environment. It can be used to find single expensive queries, frequent queries that aggregate to high usage as well as queries that consume high IO, but still return back quickly. It also offers tuning advisories to show how much benefit can be achieved by adding more memory to various areas. (i.e. PGA, SGA)

It can be easily run with the supplied sql scripts:

$ORACLE_HOME/rdbms/admin/awrrpt.sql

STATSPACK - The statspack is similar to the AWR report but has less advisory information and doesn't typically show the full queries in its output, but still can be useful in finding the same types of issues that you would find from the AWR output. It also offers some tuning advisories similar to the AWR. The AWR evolved out of the statspack.

For more information on Oracle statspack, you can navigate to: Using Statspack


Over the next few weeks we will take a look at each of these reports and discuss what makes them useful for monitoring and identifying problems in your Clarity environment.

-shawn

Outcomes