Running an Oracle Statspack Performance Report
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 theBSTAT
/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.
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
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