How do I check the performance statistics on an Amazon RDS DB instance that is running Oracle?

Last updated: 2019-08-30

How can I check the performance statistics on my Amazon Relational Database Service (Amazon RDS) instance that is running Oracle so that I can analyze and tune the performance of DB instance?

Short Description

You can use Oracle Statspack on an RDS DB instance to collect, store, and display performance data. Statspack generates reports based on DB snapshots, which you can use to analyze your DB instance's performance over a period of time. For more information, see the Oracle documentation for Statspack.

Resolution

Using Oracle Statspack on Amazon RDS

1.    If the perfstat user exists, delete perfstat by running the following command:

SQL> DROP USER perfstat CASCADE;

2.    Add the Statspack option to the DB option group. If you don't have an option group, create a new option group, and then assign the option group to the DB instance.

3.    When you add the Statspack option, Amazon RDS instances running Oracle automatically install Statspack. This creates a perfstat user to collect database statistics and to generate reports, but the perfstat user won't have access to your DB instance by default. To grant the perfstat user access, log in to the DB instance as the Amazon RDS master user and reset the password for the perfstat user by running a query similar to the following:

ALTER USER perfstat IDENTIFIED BY <new_password> ACCOUNT UNLOCK;

Note: Be sure to replace new_password with your own password.

4.    While logged in as the perfstat user, create a DB snapshot in the Amazon RDS console, or run the following command to manually take a DB snapshot:

SQL> exec statspack.snap (i_snap_level => 7, i_modify_parameter => 'TRUE');

Note: You can also change the DB snapshot capture level to obtain more detailed information. This example command takes a DB snapshot at level 7, which includes usage, row locks, and segment-level I/O statistics for SQL and execution plans.

5.    Optionally, you configure Oracle to take DB snapshots automatically. The following procedure creates a job that automatically takes one DB snapshot per hour:

SQL> set serveroutput on 
SQL> variable jn number; 
SQL> execute dbms_job.submit (:jn, 'statspack.snap;', sysdate, 'trunc(SYSDATE+1/24,''HH24'')');
SQL> execute dbms_output.put_line ('statspack job number:' || :jn);
SQL> commit;

6.    To see the available DB snapshots, run the following command:

SQL> set linesize 32767; 
SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
SQL> select snap_id, snap_time from stats$snapshot order by 1;
    SNAP_ID SNAP_TIME
---------- -------------------
        :: ::
        11 2019/06/03 03:18:30
        12 2019/06/03 04:18:30
        :: ::

7.    After more than two DB snapshots are created, create a Statspack report by running a command similar to the following:

SQL> exec RDSADMIN.RDS_RUN_SPREPORT (11,12);

Note: This example command creates a report using SNAP_IDs of 11 and 12, and the report name is similar to trace/ORCL_spreport<INITIAL_SNAP_ID>_<FINAL_SNAP_ID>.lst.

8.    To view and download the report, open the Amazon RDS console, and choose Databases from the navigation pane. Choose the DB instance, and choose the Log tab.

9.    Stored DB snapshots are not deleted automatically, which can consume storage space on your DB instance. To manually purge the DB snapshots, specify a start SNAP_ID and an end SNAP_ID similar to the following:

SQL> execute statspack.purge(<INITIAL_SNAP_ID>, <END_SNAP_ID>);

Stop using Oracle Statspack on Amazon RDS

1.    To stop the automatic retrieval of Statspack, delete the job that you created. To get the job number, run the following command:

SQL> select job, what FROM user_jobs;

2.    To delete the job, run the following command:

SQL> execute dbms_job.remove (<job_id>);

Note: Be sure to replace job_id with your own job ID.

After you remove the Statspack option from the DB option group, the perfstat user and the DB snapshots are deleted, and all stored DB snapshots are purged.


Did this article help you?

Anything we could improve?


Need more help?