Optimize application workload with SQL Plan Management in OCI Database Management

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.