Running an Oracle Statspack Performance Report

manager Mon, 01/10/2011 - 13:44

After snapshots are taken, you can generate performance reports. The SQL scripts that generate the reports prompts you for a beginning snapshot ID, an ending snapshot ID, and a report name. The Statspack package includes two reports.

  • First, run a Statspack report, SPREPORT.SQL, which is a general instance health report that covers all aspects of instance performance. This reports calculates and prints ratios and differences for all statistics between the two snapshots, similar to the BSTAT/ESTAT report.
  • After examining the instance report, run a SQL report, SPREPSQL.SQL, on a single SQL statement (identified by its hash value). The SQL report only reports on data relating to the single SQL statement.

    Note:

    It is not correct to specify begin and end snapshots where the begin snapshot and end snapshot were taken from different instance startups. In other words, the instance must not have been shutdown between the times that the begin and end snapshots were taken.

    This is necessary because the database's dynamic performance tables, which Statspack queries to gather the data, reside in memory. Hence, shutting down the database resets the values in the performance tables to 0. Because Statspack subtracts the begin-snapshot statistics from the end-snapshot statistics, the resulting output is invalid. If begin and end snapshots taken between shutdowns are specified in the report, then the report shows an appropriate error to indicate this.

Because data gathering is separate from report production, you have flexibility to base a report on any data points you select. For example, as DBA you might want to use the supplied automation script to automate data collection every hour, on the hour. If, at some later point, a performance issue arose that might be better investigated by looking at a three-hour data window, all you have to do is specify the required start point and end point when running the report.

Running the Statspack Report

To examine the change in instance-wide statistics between two time periods, the SPREPORT.SQL script is run while connected to the PERFSTAT user. The SPREPORT.SQL script is located in the rdbms/admin directory of the Oracle home.

Note:

In an Oracle Real Application Clusters environment, you must connect to the instance on which you want to report.

When the report is run, you are prompted for the following:

  • The beginning snapshot ID
  • The ending snapshot ID
  • The name of the report text file to be created

    Note:

    Both the serial number and the session_id (SID) must be the same for the begin and end snapshots. Blank lines between lines of snapshot IDs means that the instance has been restarted (shutdown/startup) between those times, changing the serial number. The blank lines thus identify begin and end snapshots that cannot be used together when running a Statspack report.

Example 21-2 shows the SQL commands to run the report and an example of the partial report output.

Example 21-2 Creating a Statspack Report with Prompts

SQL>  connect perfstat/my_perfstat_password
SQL>  @?/rdbms/admin/spreport

On Windows platforms, the command to run the report is:

SQL>  @%ORACLE_HOME%\rdbms\admin\spreport

Sample output:

SQL>  connect perfstat/my_perfstat_password
Connected.
SQL>  @?/rdbms/admin/spreport

DB Id       DB Name      Inst Num Instance
----------- ------------ -------- ------------
2618106428  PRD1                1 prd1
Completed Snapshots
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------
prd1         PRD1             1 11 May 2000 12:07     5
                              2 11 May 2000 12:08     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2  To use this name, press <return> to 
continue, otherwise enter an alternative. Enter value for report_name: <press 
return or enter a new name>

Using the report name sp_1_2

The report now scrolls past and is also written to the file specified. For example:

ORACLE_HOME/bin/sp_1_2.lis