Oracle Statspack
The Oracle Statspack option installs and enables the Oracle Statspack performance
statistics feature. Oracle Statspack is a collection of SQL, PL/SQL, and SQL*Plus
scripts that collect, store, and display performance data. For information about using
Oracle Statspack, see Oracle Statspack
Note
Oracle Statspack is no longer supported by Oracle and has been replaced by the more advanced Automatic Workload Repository (AWR). AWR is available only for Oracle Enterprise Edition customers who have purchased the Diagnostics Pack. You can use Oracle Statspack with any Oracle DB engine on Amazon RDS. You can't run Oracle Statspack on Amazon RDS read replicas.
Setting up Oracle Statspack
To run Statspack scripts, you must add the Statspack option.
To set up Oracle Statspack
-
In a SQL client, log in to the Oracle DB with an administrative account.
-
Do either of the following actions, depending on whether Statspack is installed:
-
If Statspack is installed, and the
PERFSTAT
account is associated with Statspack, skip to Step 4. -
If Statspack is not installed, and the
PERFSTAT
account exists, drop the account as follows:DROP USER PERFSTAT CASCADE;
Otherwise, attempting to add the Statspack option generates an error and
RDS-Event-0058
.
-
-
Add the Statspack option to an option group. See Adding an option to an option group.
Amazon RDS automatically installs the Statspack scripts on the DB instance and then sets up the
PERFSTAT
account. -
Reset the password using the following SQL statement, replacing pwd with your new password:
ALTER USER PERFSTAT IDENTIFIED BY pwd ACCOUNT UNLOCK;
You can log in using the
PERFSTAT
user account and run the Statspack scripts. -
Grant the
CREATE JOB
privilege to thePERFSTAT
account using the following statement:GRANT CREATE JOB TO PERFSTAT;
-
Ensure that idle wait events in the
PERFSTAT.STATS$IDLE_EVENT
table are populated.Because of Oracle Bug 28523746, the idle wait events in
PERFSTAT.STATS$IDLE_EVENT
may not be populated. To ensure all idle events are available, run the following statement:INSERT INTO PERFSTAT.STATS$IDLE_EVENT (EVENT) SELECT NAME FROM V$EVENT_NAME WHERE WAIT_CLASS='Idle' MINUS SELECT EVENT FROM PERFSTAT.STATS$IDLE_EVENT; COMMIT;
Generating Statspack reports
A Statspack report compares two snapshots.
To generate Statspack reports
-
In a SQL client, log in to the Oracle DB with the
PERFSTAT
account. -
Create a snapshot using either of the following techniques:
-
Create a Statspack snapshot manually.
-
Create a job that takes a Statspack snapshot after a given time interval. For example, the following job creates a Statspack snapshot every hour:
VARIABLE jn NUMBER; exec dbms_job.submit(:jn, 'statspack.snap;',SYSDATE,'TRUNC(SYSDATE+1/24,''HH24'')'); COMMIT;
-
-
View the snapshots using the following query:
SELECT SNAP_ID, SNAP_TIME FROM STATS$SNAPSHOT ORDER BY 1;
-
Run the Amazon RDS procedure
rdsadmin.rds_run_spreport
, replacing begin_snap and end_snap with the snapshot IDs:exec rdsadmin.rds_run_spreport(begin_snap,end_snap);
For example, the following command creates a report based on the interval between Statspack snapshots 1 and 2:
exec rdsadmin.rds_run_spreport(1,2);
The file name of the Statspack report includes the number of the two snapshots. For example, a report file created using Statspack snapshots 1 and 2 would be named
ORCL_spreport_1_2.lst
. -
Monitor the output for errors.
Oracle Statspack performs checks before running the report. Therefore, you could also see error messages in the command output. For example, you might try to generate a report based on an invalid range, where the beginning Statspack snapshot value is larger than the ending value. In this case, the output shows the error message, but the DB engine does not generate an error file.
exec rdsadmin.rds_run_spreport(2,1); * ERROR at line 1: ORA-20000: Invalid snapshot IDs. Find valid ones in perfstat.stats$snapshot.
If you use an invalid number a Statspack snapshot, the output shows an error. For example, if you try to generate a report for snapshots 1 and 50, but snapshot 50 doesn't exist, the output shows an error.
exec rdsadmin.rds_run_spreport(1,50); * ERROR at line 1: ORA-20000: Could not find both snapshot IDs
-
(Optional)
To retrieve the report, call the trace file procedures, as explained in Working with Oracle trace files.
Alternatively, download the Statspack report from the RDS console. Go to the Log section of the DB instance details and choose Download. The following example shows
trace/ORCL_spreport_1_2.lst
If an error occurs while generating a report, the DB engine uses the same naming conventions as for a report but with an extension of
.err
. For example, if an error occurred while creating a report using Statspack snapshots 1 and 7, the report file would be namedORCL_spreport_1_7.err
. You can download the error report using the same techniques as for a standard Snapshot report.
Removing Statspack snapshots
To remove a range of Oracle Statspack snapshots, use the following command:
exec statspack.purge(begin snap, end snap);