AWS Database Blog

Managing your SQL plan in Oracle SE with Amazon RDS for Oracle

Organizations are aggressively adopting the cloud as the 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 Oracle EE, and you may be overpaying.

You can significantly reduce your Oracle commercial license usage by 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 SQL plan management (SPM) offerings in Oracle SE. We demonstrate how to stabilize your SQL plan with the following options:

  • Stored outlines – Carries over from Oracle EE to Oracle SE
  • Hints – Mostly reusable in Oracle SE
  • Baseline plan – Available from version 18c in Oracle SE

Oracle stored outlines

The stability of the run plans used by the Oracle optimizer is a critical component of database performance. We can fix the plan for a particular query using Oracle stored outlines in Oracle SE. The stored outline makes sure that the query uses the same path each time.

To demonstrate stored outline capabilities, we use sample data from Working with the Sample Database for Migration and AWS Database Migration Service Documentation. In the following use case, we create and validate that the query uses an outline while creating a plan.

  1. Log in to Oracle SE as an admin user and configure a sample schema.

The install_rds.sql script creates the dms_sample and dms_user schema along with object and sample data. See the following code:

$ cd ~/aws-database-migration-samples/oracle/sampledb/v1
$ sqlplus oraadmin@orclse

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 20 00:05:28 2020

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

Enter password:

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

@install_rds.sql
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL
  1. Connect as the primary user and grant privileges to manage the outline to dms_sample:
sqlplus oraadmin@orclse
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 14 23:28:06 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

grant create any outline, drop any outline to dms_sample;
Grant succeeded.
SQL
  1. Log in to the database as dms_sample and confirm that the sample tables are created in the schema:
$ sqlplus dms_sample@orclse

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 14 23:30:28 2020

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

Last Successful login time: Sun Jun 14 2020 23:29:51 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

set lines 300 pages 1000
col TNAME format a30
select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
MLB_DATA                       TABLE
NAME_DATA                      TABLE
NFL_DATA                       TABLE
NFL_STADIUM_DATA               TABLE
PERSON                         TABLE
PLAYER                         TABLE
SEAT                           TABLE
SEAT_TYPE                      TABLE
SPORTING_EVENT                 TABLE
SPORTING_EVENT_INFO            VIEW
SPORTING_EVENT_TICKET          TABLE
SPORTING_EVENT_TICKET_INFO     VIEW
SPORT_DIVISION                 TABLE
SPORT_LEAGUE                   TABLE
SPORT_LOCATION                 TABLE
SPORT_TEAM                     TABLE
SPORT_TYPE                     TABLE
TICKET_PURCHASE_HIST           TABLE
SQL
  1. Run the following SQL query and capture the query plan. The query joins SPORTING_EVENT and SPORTING_EVENT_TICKET tables, and generates a result set for id 11301:
sqlplus dms_sample@orclse

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 14 23:47:58 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Sun Jun 14 2020 23:45:47 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
set lines 300 pages 1000
set autotrace trace explain
select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301;

Execution Plan
----------------------------------------------------------
Plan hash value: 1276075110

-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                     |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | SPORTING_EVENT         |     1 |    14 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SPORTING_EVENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET  | 49501 |   773K|   617   (1)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | SET_SPORTING_EVENT_IDX | 49501 |       |   186   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."ID"=11301)
   5 - access("A"."SPORTING_EVENT_ID"=11301)
SQL
  1. Confirm that no outline exists for the schema:
select name,CATEGORY,USED,SQL_TEXT from user_outlines;

no rows selected
SQL

Creating an outline

You can create an outline two different ways. One option is to create an outline at the session level with a system-generated name. See the following code:

Enable session to create system-generated outline
alter session set create_stored_outlines=true;

Session altered.

Execute the query
set lines 300 pages 1000
set autotrace trace explain
select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301;

Execution Plan
----------------------------------------------------------
Plan hash value: 1276075110

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | SPORTING_EVENT         |     1 |    14 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SPORTING_EVENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET  | 49501 |   773K|   617   (1)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | SET_SPORTING_EVENT_IDX | 49501 |       |   186   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."ID"=11301)
   5 - access("A"."SPORTING_EVENT_ID"=11301)

Disable outline generation for the session
alter session set create_stored_outlines=false;

Session altered.

Verify that the outline is created
set long 10000
set lines 300 pages 1000
col name format a30
col CATEGORY format a15
col USED format a10
col SQL_TEXT format a100
select name,CATEGORY,USED,SQL_TEXT from user_outlines;

NAME                           CATEGORY        USED       SQL_TEXT
------------------------------ --------------- ---------- ----------------------------------------------------------------------------------------------------
SYS_OUTLINE_20072002171764009  DEFAULT         UNUSED     select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPOR
                                                          TING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301

Re-execute the SQL statement to confirm that it is using the system generated outline

set lines 300 pages 1000
set autotrace trace explain

ALTER SESSION SET query_rewrite_enabled=TRUE; 

Session altered.

ALTER SESSION SET use_stored_outlines=SYS_OUTLINE_20072002171764009;

Session altered.

SQL> select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301;

Execution Plan
----------------------------------------------------------
Plan hash value: 1276075110

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | SPORTING_EVENT         |     1 |    14 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SPORTING_EVENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET  | 49501 |   773K|   617   (1)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | SET_SPORTING_EVENT_IDX | 49501 |       |   186   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."ID"=11301)
   5 - access("A"."SPORTING_EVENT_ID"=11301)

Note
-----
   - outline "SYS_OUTLINE_20072002171764009" used for this statement

Drop outline

drop outline SYS_OUTLINE_20072002171764009;

Outline dropped.
SQL

You can also create a stored outline with a user-defined name. See the following code:

Create outline with name my_outline
create or replace outline my_outline on select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301;

Outline created.

Confirm that the outline is created successfully
select name,CATEGORY,USED,SQL_TEXT from user_outlines;

NAME                           CATEGORY        USED       SQL_TEXT
------------------------------ --------------- ---------- ----------------------------------------------------------------------------------------------------
MY_OUTLINE                     DEFAULT         UNUSED     select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPOR
                                                          TING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301

Verify that the outline is used by the SQL statement’s execution plan

SQL> select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301;

Execution Plan
----------------------------------------------------------
Plan hash value: 1276075110

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                        | 49501 |  1450K|   619   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | SPORTING_EVENT         |     1 |    14 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SPORTING_EVENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET  | 49501 |   773K|   617   (1)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | SET_SPORTING_EVENT_IDX | 49501 |       |   186   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   3 - access("B"."ID"=11301)
   5 - access("A"."SPORTING_EVENT_ID"=11301)

Note
-----
   - outline "MY_OUTLINE" used for this statement

Confirm that the outline view shows it as USED 

select name,CATEGORY,USED,SQL_TEXT from user_outlines;

NAME                           CATEGORY        USED       SQL_TEXT
------------------------------ --------------- ---------- ----------------------------------------------------------------------------------------------------
MY_OUTLINE                     DEFAULT         USED       select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPOR
                                                          TING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301

Drop outline

drop outline MY_OUTLINE;

Outline dropped.
SQL

Oracle hints

Hints are comments embedded in the SQL statement that provide necessary instructions to the optimizer, which controls and defines a SQL query’s plan. It automatically generates multiple SQL statement access paths using the object-level statistics in the data dictionary, which includes storage-level characteristics of object and data distribution. The optimizer compares multiple plans and chooses the plan it believes is the most efficient path. However, the plan may not be the best suitable plan for the query, and it may impact the performance of the query. Also, depending on the statistics collected on the object, the same query may use different plans and deliver inconsistent results.

Oracle hints allow you to control the query plan and decide which plan to use. Manually included hints in the SQL statement force the optimizer to generate and use the same paths. This is the oldest technique Oracle shared with developers to choose more efficient query plan.

Oracle doesn’t recommend using hints in the query because it interferes in the optimizer’s plan-selection process. They expect the end-user to use tools like SQL Tuning Advisor, SPM, and SQL Performance Analyzer to tune the SQL queries, which is an extra option in Oracle EE. However, for Oracle SE, testing and identifying correct hints can help you achieve a similar result to those tuning tools.

Oracle provides many hints, and except for parallel based hints, all other hints works in Oracle SE. Parallel hints from code are ignored, but it doesn’t spin up the parallel process. For more information about available hints, see the Comments section on the SQL Language Reference website.

The following use case illustrates how to efficiently use hints in Oracle. We run a SQL query to find the max SPORTING_EVENT_ID from the table SPORTING_EVENT_TICKET, first without any hints and then including hints. The SPORTING_EVENT_TICKET table has an index SET_EV_ID_TKHOLDER_ID_IDX on column SPORTING_EVENT_ID, so by default, the optimizer uses the low-cost index scanned query plan. However, when we add hints, it ignores the low-cost index scanned plan and enforces a full table scan on the SPORTING_EVENT_TICKET table.

  1. Log in to the database as dms_sample.
  2. Run the following queries to confirm that the SPORTING_EVENT_TICKET table and index on the SPORTING_EVENT_ID column exist:
$sqlplus dms_sample@orclse

col TABLE_NAME format a30
col index_name format a30
col column_name format a30
select TABLE_NAME,INDEX_NAME,COLUMN_NAME from user_ind_columns where table_name='SPORTING_EVENT_TICKET' and column_name='SPORTING_EVENT_ID';

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SPORTING_EVENT_TICKET          SET_SPORTING_EVENT_IDX         SPORTING_EVENT_ID
SPORTING_EVENT_TICKET          SET_EV_ID_TKHOLDER_ID_IDX      SPORTING_EVENT_ID
SQL
  1. Run the following SQL statement without any hint comments. The query uses the index, which maintains a lower cost compared to the full table scan:
set autotrace trace explain
select max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET;

Execution Plan
----------------------------------------------------------
Plan hash value: 2322050914

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     1 |     5 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                           |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| SET_EV_ID_TKHOLDER_ID_IDX |     1 |     5 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
SQL
  1. Run the same query including the hint comments. The hint enforces the full table scan and ignores the index on the SPORTING_EVENT_ID column:
select /*+ full(SPORTING_EVENT_TICKET) */ max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET;

Execution Plan
----------------------------------------------------------
Plan hash value: 1532261385

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |     1 |     5 | 73349   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |                       |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| SPORTING_EVENT_TICKET |    56M|   269M| 73349   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------
SQL

Oracle 18c/19c SQL baseline plan

From Oracle 18c onward, the SPM baselines are available in Oracle SE, with limitations. The plan preserved in the SQL plan baseline gets priority over the plan generated during the hard parse of the SQL statement. The SPM baseline makes sure that the query uses the same plan and delivers consistent results for every SQL statement. You can store one SQL plan baseline per statement.

Like Oracle EE, you can use the DBMS_SPM package to create and manage the SQL plan baseline. You can import and export your SQL plan baseline using the DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, and DBMS_SPM.UNPACK_STGTAB_BASELINE options. You can also migrate your stored outlines using the DBMS_SPM.MIGRATE_STORED_OUTLINE option.

As of this writing, the SPM baseline comes with a few limitations in Oracle SE. Oracle SE doesn’t allow SQL plan evolution, so you can’t store new low-cost plans in the SQL plan baseline. You also can’t load plans from AWR or SQLSETS because you don’t have auto-purge functionality for unused SQL plan baselines. For more information about limitations, see Licensing Information.

To use SPM in Oracle SE, complete the following steps:

  1. Grant the required privileges to DMS_SAMPLE using the admin user:
$ sqlplus oraadmin@orase18c
grant select on v$sqlarea to DMS_SAMPLE;
grant select on v$session to DMS_SAMPLE;
grant select on v$sql_plan_statistics_all to DMS_SAMPLE;
grant select on v$sql_plan to DMS_SAMPLE;
grant select on v$sql to DMS_SAMPLE;
grant ADMINISTER SQL MANAGEMENT OBJECT to DMS_SAMPLE;
SQL
  1. Run the following SQL statement:
$ sqlplus dms_sample@orase18c
select max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET;
MAX(SPORTING_EVENT_ID)
----------------------
                 10631
SQL
  1. Manually create the SQL plan baseline from the cursor cache:
Identify the SQL_ID of sql statement
select sql_id,sql_text,plan_hash_value from v$sql where sql_text like '%max(SPORTING_EVENT_ID)%' and  sql_text not like  '%sql_text%' and sql_text not like 'EXPLAIN%';
SQL_ID        SQL_TEXT                                              PLAN_HASH_VALUE
------------- -------------------------------------------------- ------------------
9p9twf6mnnnzf select max(SPORTING_EVENT_ID) from SPORTING_EVENT_         2322050914
              TICKET


Manually create baseline using cursor cache

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '9p9twf6mnnnzf');
END;
/
PL/SQL procedure successfully completed.
SQL
  1. Run the same statement again. The plan highlights that the it uses the SQL plan baseline:
select max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET;

Execution Plan
----------------------------------------------------------
Plan hash value: 2322050914

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

| Id  | Operation                  | Name                      | Rows  | Bytes |
 Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT           |                           |     1 |     5 |
     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |                           |     1 |     5 |
            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| SET_EV_ID_TKHOLDER_ID_IDX |     1 |     5 |
     4   (0)| 00:00:01 |

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


Note
-----
   - SQL plan baseline "SQL_PLAN_bw9a0x0wpvdd5fc42741e" used for this statement
SQL
  1. Verify that the SQL plan baseline is used and enable it for sql_id:
col plan_name format a40
SELECT sql_handle, plan_name, enabled, accepted FROM   dba_sql_plan_baselines where PLAN_NAME='SQL_PLAN_bw9a0x0wpvdd5fc42741e';

SQL_HANDLE                     PLAN_NAME                                ENA ACC
------------------------------ ---------------------------------------- --- ---
SQL_be2540e8395db5a5           SQL_PLAN_bw9a0x0wpvdd5fc42741e           YES YES
SQL

Conclusion

SQL plan stability is vital to maintain query consistency. This post described how stored outlines, hints, and SPM can help fix your SQL query plans in Oracle SE. We used Oracle sample schema and tables to demonstrate these capabilities. You can use any database query and test the feature in your environment. You can also set the use_stored_outline=true parameter at the system level to use stored outlines persistently.

 

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.