AWS Database Blog

Analyzing performance management in Oracle SE using Amazon RDS for Oracle

Organizations are aggressively adopting cloud as a standard and actively evaluating their database needs. Amazon RDS for Oracle is a managed service that makes it easy to quickly create Oracle Database instances, enabling you to migrate existing on-premises workloads to the cloud. Migration from on-premises Oracle Database to Amazon RDS for Oracle is quick because it doesn’t involve code conversion.

Oracle Database Enterprise Edition (Oracle EE) has become the standard for many organizations. However, if you do a more in-depth database assessment, you may find that not every application needs all the features of the Oracle EE, and you may be overpaying.

You can significantly reduce your Oracle commercial license usage switching to Oracle Database Standard Edition (Oracle SE), which is even easier with the Amazon RDS for Oracle License Included (LI) option. Applications with no or minimum Oracle EE features usage are excellent candidates for migrating to Oracle SE.

This post discusses the Oracle performance analysis offerings available within Oracle SE. We can use Oracle Statspack, an option pack in Oracle SE, in place of Oracle Automatic Workload Repository (AWR) and Oracle Active Session History (ASH) reports provide performance insights for Amazon RDS for Oracle at no cost in Oracle SE. You can use Statspack in place of ASH and AWR reports. You can also use SQLT, which is an add-on action for response analysis.

Solution overview

To implement this solution, we complete the following steps:

  1. Monitor the performance of a full table and index scan
  2. Deploy Statspack for Amazon RDS for Oracle
  3. Use SQLT for Amazon RDS for Oracle

Amazon RDS Performance Insights for Amazon RDS for Oracle

Amazon RDS Performance Insights is an AWS cloud-native database performance monitoring and tuning tool. You can configure Performance Insights for Oracle SE with one click. The tool dashboard enables you to detect performance problems by evaluating top waits, SQLs, hosts, and top users. It provides 22 types of metrics in graph visualization formats. Performance Insights and enhanced monitoring provide almost all the functionality a database administrator needs to do database performance analysis and tuning.

Performance Insights expands on existing Amazon Relational Database Service (Amazon RDS) and Amazon Aurora monitoring features to illustrate your database’s performance and analyze any issues that affect it. With the Performance Insights dashboard, you can visualize the database load and filter the load by waits, SQL statements, hosts, or users.

Performance Insights sends three additional metrics to Amazon CloudWatch:

  • DBLoad – Gives the number of active sessions for the database engine. An active session is a connection that has submitted a query to the database for which the database hasn’t yet returned the results. Ideally, the query spends all of its time running on the CPU processing the query instead of waiting. You can get a quick view of the load on the database by seeing how many connections are concurrently active and what states they are in. Further combining this core metric with wait event data provides a complete picture of the state of the database.
  • DBLoadCPU – Gives the number of active sessions where the wait event type is CPU. This metric helps you monitor how much time connections are running on the CPU. Ideally, if the host has enough CPU resources, all these connections are running on the CPU.
  • DBLoadNonCPU – Gives the number of active sessions where the wait event type is not CPU. This metric helps you monitor connections that are waiting for a database resource.

Configuring Performance Insights for Amazon RDS for Oracle SE

We can configure Performance Insights by selecting the check-box to Enable Performance Insights in the Additional configuration section when we create the database. You can also enable Performance Insights for existing databases without any downtime by using the modify option.

Performance Insights is an independent tab on the left navigation pane. To access the database in Performance Insights, complete the following steps:

  1. On the Amazon RDS console, choose Performance Insights.
  2. From the drop-down menu, choose your Oracle SE database.

Choosing the database opens the Performance Insights dashboard, which allows you to view database performance.

You can use Performance Insights to understand the OS and database statistics. For example, to dive deep into OS statistics of the database host, choose Manage Metrics and select the required OS and database level metrics. The following screenshot shows the OS metrics tab.

The following screenshot shows the Database metrics tab.

Monitoring database performance

The following use case shows how to use Performance Insights to monitor database performance. For this post, we used the sample database from Working with the Sample Database for Migration. We demonstrate tuning a full table scan and index scan.

Full table scan

Log in to the database as the user dms_sample and enter the following code for a full table scan:

[oracle@ip-10-1-0-237 ~]$ sqlplus dms_sample@orase18c

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 26 18:36:22 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Sun Jul 26 2020 16:33:08 +00:00

Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

SQL> set autotrace on
SQL> set timing on
SQL> select /*+ FULL(a) */ * from SPORTING_EVENT_TICKET a where SPORTING_EVENT_ID between 1000 and 2000 order by 1,2,3;

On the Performance Insights dashboard, we can see resource utilization during the runtime. We can see an increase in resource usage as soon as the query starts. The query is complete in 7 minutes, 42 seconds. The following screenshot shows the Counter Metrics section.

The following screenshot shows the Database Load section and Top SQL tab.

We can analyze the top waits during the query by viewing the database load and choosing the Top waits tab.

When the query is complete, we can review details like the query plan and elapsed time of the query. See the following code:

4044559 rows selected.

Elapsed: 00:07:42.41

Execution Plan
----------------------------------------------------------
Plan hash value: 2555944976

--------------------------------------------------------------------------------
--------------------

| Id  | Operation          | Name                  | Rows  | Bytes |TempSpc| Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------

|   0 | SELECT STATEMENT   |                       |  4895K|   140M|       |   1
06K  (1)| 00:00:05 |

|   1 |  SORT ORDER BY     |                       |  4895K|   140M|   243M|   1
06K  (1)| 00:00:05 |

|*  2 |   TABLE ACCESS FULL| SPORTING_EVENT_TICKET |  4895K|   140M|       | 667
90   (1)| 00:00:03 |

--------------------------------------------------------------------------------
--------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SPORTING_EVENT_ID"<=2000 AND "SPORTING_EVENT_ID">=1000)

Index scan

In this section, we perform an index scan. The query runs faster because it’s soft parsed and the data is in the cache. This query took approximately 5 minutes, 53 seconds to complete.

When the query is complete, you can review the plan and elapsed time of the query. See the following code:


Elapsed: 00:05:53.87

Execution Plan
----------------------------------------------------------
Plan hash value: 136468662

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |  4895K|   140M|       | 95797   (1)| 00:00:04 |
|   1 |  SORT ORDER BY                       |                        |  4895K|   140M|   243M| 95797   (1)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET  |  4895K|   140M|       | 55900   (1)| 00:00:03 |
|*  3 |    INDEX RANGE SCAN                  | SET_SPORTING_EVENT_IDX |  4895K|       |       | 16745   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SPORTING_EVENT_ID">=1000 AND "SPORTING_EVENT_ID"<=2000)

Additional analysis

We can summarize these two scans using 1-hour dashboard graphs that show variance in resource usage, and highlight different wait events. The following screenshot shows the Counter Metrics section.

The following screenshot shows the Database Load section and Top SQL tab.

The following screenshot shows the Database Load section and Top waits tab.

We can also analyze performance using various metrics based on disk I/O and CPU usage. Hovering your mouse over the points displays the exact value.

The Database Load section shows CPU usage, top waits, top SQL, top hosts, and top users. The following screenshot shows the top 10 SQLs resource consumption slice by wait events. The color legend explains the system and session wait events.

The following screenshot shows SQL resource consumption slice by SQL.

We can also view the graphs by slice by user, slice by host (client), and slice by SQLs. We can choose the Top hosts or Top users tab for more details.

To get SQLID, choose the Top SQL tab, choose the + icon, and select the SQL query. You can find the SQL statement at the bottom.

Oracle Statspack on Amazon RDS for Oracle

Oracle Statspack provides functionality to store Oracle database performance statistics in tables, which you can use to report and analyze database performance. You can use Statspack in Oracle SE to determine your database performance benchmark. You can reference the Statspack data to troubleshoot your database performance issues by comparing values between two time periods. In Amazon RDS for Oracle, you can install Statspack using an option group. Statspack installation creates a perfstat schema to store database performance statistics. You need to use the rdsadmin.rds_run_spreport package to generate the Statspack report between two snap IDs.

This section explains how to configure Statspack on Amazon RDS for Oracle.

Creating option group

To create your option group, complete the following steps:

  1. On the Amazon RDS console, choose Option groups.
  2. Choose Create group.
  3. Enter the group details.
  4. Choose Create.
  5. Select the option group you just created and choose Add option.
  6. For Option name, choose
  7. Choose Add option.
  8. Select the database and choose Modify.
  9. For Option group, choose the Statspack option group.
  10. Choose Continue.
  11. Select Apply immediately.
  12. Choose Modify DB Instance.
  13. On the Configuration tab, confirm that the option group is added in the database.

Validating the PERFSTAT user

To validate the PERFSTAT user, complete the following steps:

  1. Log in to the database and confirm that the Statspack user and objects are created. See the following code: 
    $ sqlplus oraadmin@orase18c

SQL> set lines 1000 pages 1000
col username format a30
col owner format a30
col object_type format a30
select username,default_tablespace from dba_users where username='PERFSTAT';
select owner,object_type,count(*) from dba_objects where owner='PERFSTAT' group by owner,object_type;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
PERFSTAT                       SYSAUX

SQL>
OWNER                          OBJECT_TYPE                      COUNT(*)
------------------------------ ------------------------------ ----------
PERFSTAT                       PACKAGE BODY                            1
PERFSTAT                       SEQUENCE                                1
PERFSTAT                       TABLE                                  73
PERFSTAT                       VIEW                                    1
PERFSTAT                       INDEX                                  73
PERFSTAT                       PACKAGE                                 1

6 rows selected.
  2. Log in to the database as an admin user to reset the perfstat schema password and unlock the account: 
    SQL> alter user perfstat identified by "perfstat";
User altered.

SQL> alter user perfstat account unlock;
User altered.

 

  1. Log in as PERFSTAT and configure Statspack: 
    SQL> conn perfstat/perfstat@orase18c

REM Create Statspack schedule job
variable jn number;
execute dbms_job.submit(:jn, 'statspack.snap;',sysdate,'trunc(SYSDATE+1/24,''HH24'')');
commit;

PL/SQL procedure successfully completed.

Commit complete.

REM Verify job Creation
alter session set nls_Date_format='dd-mon-yyyy hh24:mi:ss';
col what format a20
col schema_user format a30
select job,SCHEMA_USER,BROKEN,WHAT from dba_jobs;

       JOB SCHEMA_USER                    B WHAT
---------- ------------------------------ - --------------------
         1 PERFSTAT                       N statspack.snap;


select snap_id, snap_time from stats$snapshot order by 1;

   SNAP_ID SNAP_TIME
---------- --------------------
         1 15-jul-2020 13:48:52

       JOB SCHEMA_USE B WHAT                 LAST_DATE            NEXT_DATE
---------- ---------- - -------------------- -------------------- --------------------
         1 PERFSTAT   N statspack.snap;      15-jul-2020 13:48:52 15-jul-2020 14:00:00

Note: Wait until we have two snaps available

select snap_id, snap_time from stats$snapshot order by 1;

   SNAP_ID SNAP_TIME
---------- --------------------
         1 15-jul-2020 13:48:52
         2 15-jul-2020 14:00:27

REM Execute the run report to generate reports
exec RDSADMIN.RDS_RUN_SPREPORT(1,2);

PL/SQL procedure successfully completed.

     

Accessing the Statspack report

To access the Statspack report, complete the following steps:

  1. On the Amazon RDS dashboard, select your database.
  2. On the Logs tab, select the report that contains spreport in the name.
  3. Choose Download.
  4. Choose the link to download and save the log.
  5. Open the report in your preferred text editor.

Oracle SQLT on Amazon RDS for Oracle

You can use SQLT reports to tune a SQL statement’s explain plan. The report helps identify the step were a statement is taking a long time to run. We can tune the SQL statement by modifying the query or by introducing objects like an index. You can add SQLT using an option group. It creates the SQLTXPLAIN and SQLTADMIN schema. You need to install the SQLT client to your Oracle client machine to run the SQLT utility. You can download SQLT.zip from Oracle Document ID 21518701. For instructions, see Using SQLT.

This section explains how to configure SQLT for Amazon RDS for Oracle.

Creating an option group

You follow similar instructions as before to create your option group:

  1. On the Amazon RDS console, choose Option groups.
  2. Choose Create group.
  3. Enter your group details.
  4. Choose Create.
  5. Select the SQLT option group and choose Add option.
  6. For Option name, choose SQLT.
  7. For the value of LICENSE_PACK, enter N.

Options D and T require Diagnostics Pack and Tuning Pack licensing.

  1. For Apply immediately, select Yes.
  2. Choose Add option.
  3. Select the database and choose Modify.
  4. In the Database options section, for Option group, choose sqlt.
  5. Choose Continue.
  6. In the Scheduling of modifications section, select Apply immediately.
  7. Choose Modify DB Instance.
  8. On the Configuration tab, confirm that the option group is added in the database.

Installing the SQLT client

You can install the SQLT client to any Oracle client machine to run the SQLT utility. Download SQLT.zip from Oracle Document ID 21518701 and unzip the file in any folder.

Analyzing the SQL statement

You can run the SQLT utility to trace the SQL ID. For instructions, see Using SQLT.

  1. Log in to the database and get the SQL ID of the SQL statement: 
    sqlplus dms_sample@orase18c

select sql_id,sql_text from v$sql where sql_text like '%BEGIN%ticketManagement.generateTicketActivity(0.01,100000);%' and sql_text not like '%select%';

SQL_ID               SQL_TEXT
-------------------- ------------------------------------------------------------
b7bruw4ku0xtn        BEGIN ticketManagement.generateTicketActivity(0.01,100000);
                     END;
  2. Grant SQLT_USER_ROLE to the schema owner. For this post, I use the DMS_SAMPLE schema: 
    SQL> conn oraadmin@orase18c
Enter password:
Connected.
SQL> grant SQLT_USER_ROLE to dms_sample;
Cl scr
Grant succeeded.

You can now analyze the SQL statement using SQLT.

  1. Run the sqltxtract.sql script and provide the input asked during runs. In the following code, I choose all the default options: 
    sqlplus dms_sample@orase18c

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 20 11:07:20 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Mon Jul 20 2020 11:02:42 -04:00

Connected to:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

SQL> EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@orase18c');

PL/SQL procedure successfully completed.

SQL> @sqltxtract.sql b7bruw4ku0xtn sqltxplain123

PL/SQL procedure successfully completed.


Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)


Describe the characteristic of this run

"F[AST]"             if you have a FAST run
"S[LOW]"             if you have a SLOW run (default)
"H[ASH]"             if this is a run with a HASH JOIN
"N[L]"               if this is a run with a NESTED LOOP
"C[OLUMN HISTOGRAM]" if this is a run with a Column Historgram in place

SQL Description [S]: 
Note: Press Enter to accept ‘S’ as default choice

When sqltxtract.sql has run successfully, it creates a .zip file that has all the data required to analyze the SQL statement. For example, for this use case it created the file sqlt_s31633_sqldx_b7bruw4ku0xtn_log.zip. You can also upload this file to Oracle support when opening a performance tuning ticket.

Conclusion

This post discussed Amazon RDS Performance Insights, Statspack, and the SQLT tool, which you can use to troubleshoot performance issues on Oracle SE on Amazon RDS for Oracle. Each tool comes with a unique feature, and depending on the use case, you either choose one of them or use them in combination.

About the Authors

Srinivas Potlachervoo is a Consultant with the Professional Services team at Amazon Web Services. He works as database migration specialist to help AWS customers to move their on-premises database environment to AWS cloud database solutions.

 

 

 

 

Bhavesh Rathod is an Oracle Database Cloud Architect with the Professional Services team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.

 

 

 

Samujjwal Roy is a Database Specialty Architect with the Professional Services team at Amazon Web Services. He has been with Amazon for 15+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.