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.
- Log in to Oracle SE as an admin user and configure a sample schema.
install_rds.sql script creates the
dms_user schema along with object and sample data. See the following code:
- Connect as the primary user and grant privileges to manage the outline to
- Log in to the database as
dms_sampleand confirm that the sample tables are created in the schema:
- Run the following SQL query and capture the query plan. The query joins
SPORTING_EVENT_TICKETtables, and generates a result set for
- Confirm that no outline exists for the schema:
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:
You can also create a stored outline with a user-defined name. See the following code:
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
- Log in to the database as
- Run the following queries to confirm that the
SPORTING_EVENT_TICKETtable and index on the
- 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:
- Run the same query including the hint comments. The hint enforces the full table scan and ignores the index on the
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.UNPACK_STGTAB_BASELINE options. You can also migrate your stored outlines using the
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 AWS 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:
- Grant the required privileges to
DMS_SAMPLEusing the admin user:
- Run the following SQL statement:
- Manually create the SQL plan baseline from the cursor cache:
- Run the same statement again. The plan highlights that the it uses the SQL plan baseline:
- Verify that the SQL plan baseline is used and enable it for
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.