SQL performance regression is a leading cause of poor system performance. Managing and tuning individual SQL execution performance can be a daunting task that requires an in-depth analysis of schema design; tedious comparison reports on historic and present performance; and thorough knowledge of an application’s workflow. Don’t worry, SQL performance of cloud and on-premise databases can now be managed and the likelihood of unexpected regression reduced with the SQL Plan Management (SPM) feature available in OCI Database Management service (DBM). SPM allows database users to maintain stable, optimal performance for a set of SQL statements, and SPM functionality can be managed and configured to improve the overall performance of database environments over time. The SPM feature in DBM allows visualization and deeper insights into the usage of SQL plans utilizing the following capabilities within the database resource home page:
- Overview of SQL plan baselines in your database
- Filter SQL plans by usage or statistics and search based on SQL text, plan name, or origin (auto- or manual-capture)
- Configure retention and storage budgets for SQL plans
- Enable automatic plan capture with filters based on SQL actions, modules, parsing schema, or SQL text
- Enable and configure automatic SPM Evolve Advisor task
What is SPM, and why is it important?
The Oracle Database SQL Optimizer (the Optimizer) determines execution plans for thousands or millions of SQL statements in any given system, and sometimes with multiple plans per SQL. Without SPM, the Optimizer may change or add plans for a SQL at any time. The vast majority of the time these new plans improve performance as expected. However, when the new plan is a performance regression, it can have a large impact if it involves a mission-critical SQL. SPM allows customers to defer immediate plan changes while new plans are tested prior to being used by production clients. Customers will get the advantage of new plans that perform better, at the modest price of waiting for them to be validated against current plans as indeed better. SPM incorporates the positive attributes of plan adaptability and plan stability. It has two main objectives:
- Prevent performance regressions in the face of database system changes
- Offer performance improvements by adapting to database system changes
SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. An enhanced version of the Oracle Optimizer called SPM Aware Optimizer, accesses, uses and manages this information which is stored in a repository called the SQL Management Base (SMB). SPM involves three main components:
- Plan Capture: Creation of SQL plan baselines that store execution plans, the first plan is accepted, and subsequent plans are collected as unaccepted
- Plan Selection: Ensures only accepted execution plans are used for statements with SQL plan baselines and records any new plans as unaccepted
- Plan Evolution: Evaluation of unaccepted execution plans and updates those with better performance as accepted in the SQL plan baseline
Enable SPM Plan Capture automatically
SPM plan capture can be configured to run automatically for all SQL statements or filtered for application workflows based on SQL actions, modules, parsing schema, or SQL text. Repeatable SQL statements are targeted and generate SQL plan baselines to ensure only critical SQLs are being evaluated to reduce overhead. It is best to perform the capture during normal database performance windows to baseline SQL execution plans with acceptable plans. Manual plan capture is also available from the cursor cache or AWR. For more information on SPM, check out this blog.
How to configure, enable, and manage SPM in DBM
From Observability and Management, navigate to Database Management. Within the database resource home page, accessible from drilling down from the fleet overview or administration page, the “SQL plan management” feature is found under the “Resources” menu.
You can create a SQL plan baseline in several ways: using a SQL Tuning Set (STS); from the cursor cache; exporting from one database and importing into another; and automatically for every statement. Currently, Database Management allows you to configure SQL plan baselines to be captured automatically and supports the creation of plan baselines from AWR and cursor cache. Three separate tabs are available for various tasks.
The “SQL plan baselines” tab provides an overview of SQL plan baselines in use by the database. You can filter plans based on attributes and search for specific plans based on SQL text, plan name, or origin. You can configure individual plans by deleting them from the SQL plan, altering attributes, or viewing details which include Optimizer statistics such as plan outline, predicate information, and hint usage.
The “Load SQL plans” tab provides capabilities to manually create SQL plans from AWR or cursor cache. Task names for previous collections are shown with run details. Filtering options are available when loading SQL plans which include SQL ID, parsing schema, module, or action of the SQL statement. In addition, there is an option to set attributes of the SQL plan to fixed or enabled when loading.
Finally, the “Configuration” tab allows adjusting storage usage, retention, automatic plan capture, and automatic SPM Evolve Advisor task functionality (High-frequency Automatic SPM Evolve Advisor tasks are limited to Exadata environments).
SPM is supported in the following configurations
- This feature is available for external (on-premise) and cloud databases, Base DB and ExaDB-D deployments, version 11.2.0.4 and later
- The following are available only for Oracle Databases version 12.2 and later
- Support for loading SQL plans from AWR
- Automatic SPM Evolve Advisor Task
- Select plan capturing (filters)
- High-frequency Automatic Advisor task is only available for Oracle Databases 19c and later running on the Oracle Exadata platform
In Summary
Take advantage of the new SPM feature in OCI Database Management to streamline SQL performance administration for databases in your own data center or in the cloud. New visualizations and easily accessible plan management enable maximized database performance with confidence.
To learn more about DBM capabilities, visit:
Get started with a hands-on workshop
Database Management technical content
OCI Database Management documentation