Skip to Main Content

Feature Details

Buttons

Feature Details Container

Adaptive SQL Plan Management

Database Overall → SQL Plan Management

With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.

Business Benefit: Unaccepted plans in a SQL plan baseline are automatically evolved during the nightly maintenance window and a persistent verification report is generated which means a DBA no longer has to manually evolve plans and they can go back days or weeks later and review what plans were evolved during each of the nightly maintenance windows.

Release Availability
11.2
12.1
12.2
18c
19c
21c
23ai
Parent Feature
SQL Plan Management
Available On
  • Oracle Database FREE
  • Standard Edition 2
  • Enterprise Edition
  • Oracle Database Appliance
  • Exadata
  • Exadata Database Service on Dedicated Infrastructure / Cloud@Customer
  • Database Cloud Service Standard Edition
  • Database Cloud Service Enterprise Edition
  • Database Cloud Service Enterprise Edition - High Performance
  • Database Cloud Service Enterprise Edition - Extreme Performance

Notes:

SQL Plan Management does not require a license for Oracle Diagnostics Pack or Oracle Tuning Pack

SE2 and BaseDB SE: This feature is available in the SE2 and BaseDB SE offerings starting with Oracle Database 18c. Only one SQL plan baseline per SQL statement is allowed and SQL plan evolution is disabled.

Details:

  1. SQL plan baselines can be created or captured using the following methods:
    • Auto capture (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE=TRUE)
    • Manual loading from the cursor cache (DBMS_SPM.LOAD_PLANS_FROM CURSOR_CACHE)
    • Migration from stored outlines (DBMS_SPM.MIGRATE_STORED_OUTLINE)
    • Import using DBMS_SPM.UNPACK_STGTAB_BASELINE
  2. All capture and creation methods store only one SQL plan baseline per SQL statement.
  3. SQL plan baselines can be exported and imported using DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, and DBMS_SPM.UNPACK_STGTAB_BASELINE.
  4. Unused SQL plan baselines are not auto-purged.
  5. Alternative SQL execution plans for SQL statements are not added to the SQL plan history.
  6. SQL plan baselines can be altered and dropped (DBMS_SPM.ALTER_SQL_PLAN_BASELINE and DBMS_SPM.DROP_SQL_PLAN_BASELINE).
  7. The following DBMS_SPM functions and procedures are not allowed: CONFIGURE, LOAD_PLANS_FROM_AWR, LOAD_PLANS_FROM_SQLSET, and all functions and procedures associated with SQL plan evolution.

Initial Release
12.1.0.1
DISCLAIMER
The information in this application is subject to change at any time. Oracle Corporation and its affiliates disclaim any liability for incomplete or inaccurate information contained herein. If you have a question about your licensing needs, contact your Oracle sales representative, or refer to Licensing Information User Manual and Licensing Guidelines.
Release 1.0