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:
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:
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:
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.