How can I troubleshoot performance issues on an Amazon RDS for Oracle DB instance?

Resolution

There are a number of reasons that you might see performance issues on an Amazon RDS for Oracle DB instance. Use a combination of the following AWS resources and Oracle native tools to identify the cause of performance issues on your instance.

Amazon CloudWatch metrics

Amazon RDS publishes metrics to Amazon CloudWatch in the AWS/RDS and AWS/Usage namespaces. For a complete list of Amazon RDS metrics sent to CloudWatch, see Metrics reference for Amazon RDS.

Use the following metrics to identify the change in workload and identify instance and storage level limits:

• Amazon CloudWatch instance-level metrics for Amazon RDS
• Amazon CloudWatch usage metrics for Amazon RDS

Use Enhanced Monitoring metrics

With Enhanced Monitoring metrics, you can monitor the operating system of your DB instance in real time. When you want to see how different processes or threads use the CPU, Enhanced Monitoring metrics are useful. For more information, see Setting up and enabling Enhanced Monitoring.

Note: It's a best practice to set the granularity for Enhanced Monitoring to 1 second or 5 seconds for business-critical applications. With this granularity, the metrics provide more accurate information about the load on the application. After you turn on Enhanced Monitoring for your instance, review the following metrics to analyze CPU utilization:

• CPU Total - Check this metric to understand the time period when CPU utilization increased.
• Load Avg 1 min, Load Avg 5 min, and Load Avg 15 min - If the load average is greater than the number of vCPUs, then your instance might have a CPU bottleneck.
• CPU Nice - Review the amount of CPU that your workload uses against the database.
• OS process list - Sort by CPU% or MEM% to identify the process that has the most resource usage. To map the process IDs that you identify to a session on the database, run a query similar to the following:

SET LINESIZE 120;SET PAGES 200;
COL OSUSER FOR a20;
COL USERNAME FOR a20;
COL MACHINE FOR a20;
SELECT a.sid, a.serial#, a.osuser, a.username, a.machine, a.sql_id, c.sql_text FROM v$session a, v$process b, v$sql c 
WHERE a.paddr=b.addr AND b.spid=&spid AND a.sql_id=c.sql_id(+);

Performance Insights metrics

Amazon RDS Performance Insights helps you quickly assess the load on your database. To use Performance Insights, you must activate it on your DB instance. You can deactivate it later if necessary. Activating and deactivating Performance Insights doesn't cause downtime, a reboot, or a failover.

For more information on how to visualize the database load and filter accordingly, see Overview of the Performance Insights dashboard.

Oracle Statspack

Oracle Statspack is a performance reporting tool that provides the performance metrics of your database over a specific time period. For more information, see Oracle Statspack on the Oracle website.

To review the performance of your instance with Statspack, complete the following steps:

1. Generate a Statspack report for the time period when you experienced an issue.
2. Review Instance Efficiency for load change over time.
3. Review SQL ordered by CPU Time and SQL ordered by Elapsed time. Identify and optimize queries that result in high CPU load or queries that took a long time.
4. Review and note the Top wait events for your database processes.

See the following example extract from a Statspack report:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait    %: 99.99  Redo NoWait    %: 100.00
Buffer Hit       %: -45.57 In-memory Sort %: 97.55
Library Hit      %: 99.89  Soft Parse     %: 99.72
Execute to Parse %: -1.75  Latch Hit      %: 99.11
Parse CPU to Parse Elapsd %: 52.66 % Non-Parse CPU: 99.99

Shared Pool Statistics    Begin End
                         ------ ------
    Memory Usage      %:  42.07 43.53
% SQL with executions>1:  73.79 75.08
% Memory for SQL w/exec>1:76.93 77.64

Top 5 Timed Events                                              
~~~~~~~~~~~~~~~~~                                                       % Total
Event                                         Waits        Time (cs) Wt Ela Time
-------------------------------------------- ------------ ------------  -------
db file sequential read                       12,131,221         173,910 58.04
db file scattered read                        93,310              86,884 29.00
log file sync                                 18,629               9,033 3.01
log file parallel write                       18,559               8,449 2.82
buffer busy waits                             304,461              7,958 2.66

SQL ordered by CPU Time
-> Total DB CPU (s):           3,345-> Captured SQL accounts for   91.3% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
    CPU                  CPU per            Elapsed                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
   3043.36      598,100       0.01   91.0    3356.81     994,096,212  219593194

Module: JDBC Thin Client
SELECT tt.ORDER_TOTAL, tt.SALES_REP_ID, tt.ORDER_DATE, customers.CUST_FIRST_NAME, customers.CUST_LAST_NAME FROM   
(SELECT orders.ORDER_TOTAL, orders.SALES_REP_ID, orders.ORDER_DATE, orders.customer_id, rank() Over (ORDER BY orders.O

Automatic Workload Repository

Automatic Workload Repository (AWR) is an Oracle performance reporting tool that provides metrics over a specific time period.

Note: To use AWR, you must use Oracle Enterprise Edition and you require a diagnostic pack license. For more information, see Enterprise database management on the Oracle website. To identify the cause for your performance issues with AWR, use the following steps:

1. To identify the start and end snapshot ID for the relevant time period, run a query similar to the following:

   SELECT SNAP_ID, BEGIN_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 1;

2. Generate an AWR report.

3. Download the AWR report.

4. Review SQL ordered by CPU Time and SQL ordered by Elapsed time in the AWR report. Identify and optimize queries that result in high CPU load or queries that took a long time.

5. Review SQL ordered by CPU Time and SQL ordered by Elapsed time in the AWR report. Identify and optimize queries that result in high CPU load or queries that took a long time.

6. Review and note the Top wait events for your database processes.

Automatic Database Diagnostic Monitor

Automatic Database Diagnostic Monitor (ADDM) helps you to identify and remediate performance bottlenecks in your DB instance.

Note: To use ADDM, you must use Oracle Enterprise Edition and you require a diagnostic pack license. For more information, see Enterprise database management on the Oracle website.

1. To identify the start and end snapshot IDs for the relevant time period, run a query similar to the following one:

   SELECT SNAP_ID, BEGIN_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 1;

2. Generate and then download the ADDM report.

3. Review and implement the recommendations in the report.

Active Session History

Active Session History (ASH) is a native Oracle diagnostic tool that collects active session information. For more information, see Analyzing sampled data on the Oracle website.

Note: To use ASH, you must use Oracle Enterprise Edition and you require a diagnostic pack license. For more information, see Enterprise database management on the Oracle website.

Generate an ASH report for the relevant time period and then review the TOP SQL with TOP Events section. For more information, see Oracle Support Doc ID FAQ: Automatic Workload Repository (AWR) Reports (Doc ID 1599440.1) on the Oracle website.

Note: You must log in to your Oracle account before you access this document.

Oracle SQLTPLAIN

Amazon RDS supports Oracle SQLTXPLAIN (SQLT) with the Oracle SQLT option. Use SQLT to diagnose SQL statements that don't perform well. To produce a report for a specific SQL statement, see Oracle SQLT.

When you use SQLT, you might receive an error similar to the following one:

Error: ORA-20106: SQLT parameter connect_identifier must be set when running SQLT from a remote client.

Before you run the extract, run one of the following commands:

EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@SID');EXEC sqltxadmin.sqlt$a.set_param('connect_identifier', '@example-hostname:example-port/example-sid');

Related information

DB Instance classes

Amazon RDS DB instance storage

Amazon RDS basic operational guidelines

Best practices for working with Oracle