Oracle Cloud Infrastructure Documentation

Use SPM to Manage SQL Execution Plans

You can use SQL Plan Management (SPM) to ensure that the runtime performance of a SQL statement does not degrade due to SQL execution plan (SQL plan) changes.

SPM is a preventative mechanism that enables the Oracle optimizer to automatically manage SQL plans, ensuring that the database uses only known or verified plans. The performance of any database application heavily relies on consistent SQL statement execution. A SQL statement’s execution plan can change unexpectedly for a variety of reasons such as regathering optimizer statistics, changes to the optimizer parameters or schema or metadata definitions. SPM provides "plan stability" through a framework that preserves the current SQL plans amidst environment changes, yet allows changes only for better plans. When a new SQL plan is found for a SQL statement, it will not be used until it has been verified to have comparable or better performance than the current plan.

SPM uses a proactive mechanism called SQL plan baseline, which is a set of accepted SQL plans that the Oracle optimizer is allowed to use for a SQL statement. By using baselines, SPM prevents plan regressions from environmental changes, while permitting the optimizer to discover and use better plans.

The main components of SPM are:

  • Plan capture: The techniques for capturing and storing relevant information about plans in the SQL management base for a set of SQL statements. Capturing a plan involves making SPM aware of the plan and it can be done through:
    • Automatic plan capture: When enabled, the database checks whether executed SQL statements are eligible for automatic capture. To be eligible for automatic plan capture, an executed statement must be repeatable, and it must not be excluded by any capture filters.
    • Manual plan capture: User-initiated bulk load of existing execution plans for SQL statements into a SQL plan baseline.
  • Plan selection: The Oracle optimizer ability to detect plan changes based on stored plan history, and the use of SQL plan baselines to select plans to avoid potential performance regressions.
  • Plan evolution: The process of adding new plans to existing SQL plan baselines, either manually or automatically. The Oracle optimizer verifies new plans and adds them to an existing SQL plan baseline.

For more information on SPM and its components, see Overview of SQL Plan Management in Oracle Database SQL Tuning Guide.

To use SPM, go to the Managed database details page and click SQL plan management on the left pane under Resources. You can perform the following SPM tasks in Database Management:

  • Manage SQL plan baselines.
  • Submit tasks to load SQL plans into SQL plan baselines.
  • Perform configuration tasks such as enabling, disabling, or editing SQL plan baseline, automatic plan capture, and Automatic SPM Evolve Advisor task parameters.

Privileges Required to Perform SPM Tasks

The following table lists SPM tasks and the privileges required to perform them.

Note

Any user granted the ADMINISTER SQL MANAGEMENT OBJECT privilege can execute the DBMS_SPM package.
Task Required Privileges
Change one or more attributes of a single SQL plan or all the plans associated with a SQL statement. EXECUTE privilege on the SYS.DBMS_SPM package.
Change the disk space limit for the SQL management base. EXECUTE privilege on the SYS.DBMS_SPM package.
Change the retention period of unused SQL plans. EXECUTE privilege on the SYS.DBMS_SPM package.
Configure automatic capture filters. EXECUTE privilege on the SYS.DBMS_SPM package.
Configure the Automatic SPM Evolve Advisor task. EXECUTE privilege on the SYS.DBMS_SPM package.

Note: Only the SYS user can configure the Automatic SPM Evolve Advisor task, SYS_AUTO_SPM_EVOLVE_TASK.

Disable automatic plan capture. ALTER SYSTEM privilege
Disable the Automatic SPM Evolve Advisor task. EXECUTE privilege on the SYS.DBMS_AUTO_TASK_ADMIN package.
Disable the high-frequency Automatic SPM Evolve Advisor task. EXECUTE privilege on the SYS.DBMS_SPM package.
Disable the use of SQL plan baselines stored in SQL management base. ALTER SYSTEM privilege
Drop a single SQL plan or all the plans associated with a SQL statement. EXECUTE privilege on the SYS.DBMS_SPM package.
Enable automatic plan capture. ALTER SYSTEM privilege
Enable the Automatic SPM Evolve Advisor task. EXECUTE privilege on the SYS.DBMS_AUTO_TASK_ADMIN package.
Enable the high-frequency Automatic SPM Evolve Advisor task. EXECUTE privilege on the SYS.DBMS_SPM package.
Enable the use of SQL plan baselines stored in SQL management base. ALTER SYSTEM privilege
Load SQL plans from AWR snapshots. EXECUTE privilege on the SYS.DBMS_SPM and SYS.DBMS_SCHEDULER packages.
Load SQL plans from the cursor cache. EXECUTE privilege on the SYS.DBMS_SPM and SYS.DBMS_SCHEDULER packages.
View SQL plan baseline configuration details. SELECT or READ privilege on the following views:
  • SYS.DBA_SQL_MANAGEMENT_CONFIG
  • SYS.V_$SYSAUX_OCCUPANTS
  • SYS.V_$SYSTEM_PARAMETER2
  • SYS.DBA_ADVISOR_PARAMETERS
  • SYS.DBA_AUTOTASK_CLIENT
View SQL plan baseline details.
  • SELECT or READ privilege on the SYS.DBA_SQL_PLAN_BASELINES view.
  • Privileges required to execute the SQL statement for which you want to obtain the plan.
  • EXECUTE privilege on SYS.DBMS_XPLAN package.
View SQL plan baselines. SELECT or READ privilege on the SYS.DBA_SQL_PLAN_BASELINES view.
View the jobs submitted to load SQL plan baselines. SELECT or READ privilege on the SYS.DBA_SCHEDULER_JOBS view.
View the number of SQL plan baselines aggregated by their attributes. SELECT or READ privilege on the SYS.DBA_SQL_PLAN_BASELINES view.
View the number of SQL plan baselines aggregated by their last execution. SELECT or READ privilege on the SYS.DBA_SQL_PLAN_BASELINES view.
View the SQL statements from the cursor cache. SELECT or READ privilege on the SYS.V_$SQL view.

Manage SQL Plan Baselines

You can manage SQL plan baselines on the SQL plan baselines tab.

The following tiles are displayed on the top of the SQL plan baselines tab:

  • Summary: Displays the total number of SQL plan baselines and whether SQL plan baseline, automatic plan capture, and Automatic SPM Evolve Advisor tasks are enabled. On the Summary tile, you can enable or disable SQL plan baseline, automatic plan capture, and Automatic SPM Evolve Advisor tasks by clicking the Enable or Disable buttons and providing database credentials.
  • Baseline last executions: Displays the number of SQL plan baselines based on when they were last executed. On the Baseline last executions tile, hover the mouse on the pie chart to view additional details; and filter the data displayed in the chart by clicking the time period options listed in the legend.
  • SQL plan statistics: Displays SQL plans broken down by the following statistics:
    • Enabled: SQL plans that are eligible for use by the Oracle optimizer.
    • Accepted: SQL plans that are in SQL plan baselines and thus available for use by the Oracle optimizer.
    • Reproduced: SQL plans that are reproduced by the Oracle optimizer.
    • Fixed: Accepted SQL plans that are marked as preferred, so that the Oracle optimizer considers only these plans in the SQL plan baseline.
    • Auto purge: SQL plans that are configured to be automatically purged after the default retention period.

    On the SQL plan statistics tile, hover the mouse on the horizontal bar chart to view additional details; and filter the data displayed in the chart by clicking the options listed in the legend.

The SQL plans section lists the executed SQL plans with additional details such as when a SQL plan was last executed, whether it's enabled, accepted, reproduced, and so on, and its origin. To filter the list:

  • Click a section of the pie chart on the Baseline last executions tile to filter by last execution time.
  • Click a bar on the SQL plan statistics tile to filter the plans based on whether they are enabled, accepted, reproduced, fixed, or configured to auto purge.

You can also use the search field to search by SQL text, plan name, or origin.

In the SQL plans section, you can:

  • Click the SQL plan link in the SQL text column to view the SQL plan.
  • Click the Actions icon (Actions) for a SQL statement and use the following options in the menu:
    • Edit SQL statement attributes: Click to edit SQL statement attributes. Note that any changes made to the SQL statement attributes will impact all the associated SQL plans.
    • Drop SQL statement: Click to drop the SQL statement. Note that dropping the SQL statement will drop the associated SQL plans.
  • Click the Actions icon (Actions) for a SQL plan and use the following options in the menu:
    • View details: Click to view the SQL plan.
    • Edit attributes: Click to set or edit the following attributes of the SQL plan.
      • Auto purge: Select this check box to automatically purge (drop) the SQL plan after the specified retention period.
      • Enabled: Select this check box to indicate that the SQL plan is an enabled plan.
      • Fixed: Select this check box to indicate that the SQL plan is a fixed plan.
    • Drop: Click to drop the SQL plan from the SQL plan baseline.

Load SQL Plans

You can submit a task to load SQL plans into SQL plan baselines on the Load SQL plans tab.

You can load SQL plans from the following sources:

  • AWR: Load plans from Automatic Workload Repository (AWR) snapshots. For information, see Load SQL Plans from AWR.
    Note

    Support for loading SQL plans from AWR is only available for Oracle Databases version 12.2 and later.
  • Cursor cache: Load plans from the shared SQL area (cursor cache). For information, see Load SQL Plans from Cursor Cache.

Load SQL Plans from AWR

  1. In the SQL plan management section, click the Load SQL plans tab.
  2. In the Load SQL plan from drop-down list, select AWR and click Load.
  3. In the Load SQL plans from AWR panel:
    1. Provide the following information to submit the task in the General section:
      1. Task name: Review the auto-populated name of the task and make changes to it, if required.
      2. Description: Optionally, enter a description for the task.
      3. Begin snapshot: Enter the number of the beginning snapshot in the range and select it from the drop-down list.
      4. End snapshot: Enter the number of the ending snapshot in the range and select it from the drop-down list.
      5. SQL text filter: Optionally, enter SQL text to only load the plans that meet the filtering criteria. If no value is provided, then all the plans within the specified snapshot range in AWR are selected.
      6. Plan attributes: Select the following check boxes to specify plan attributes:
        • Fixed: Select this check box to indicate that the loaded plans are fixed plans.
        • Enabled: Select this check box to indicate that the loaded plans are enabled plans.
    2. Select one of the available options in the Credential type drop-down list in the Credentials section to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
    3. Click Save changes.

Load SQL Plans from Cursor Cache

  1. In the SQL plan management section, click the Load SQL plans tab.
  2. In the Load SQL plan from drop-down list, select Cursor cache and click Load.
  3. In the Load SQL plans from cursor cache panel:
    1. Provide the following information to submit the task in the General section:
      1. Task name: Review the auto-populated name of the task and make changes to it, if required.
      2. Description: Optionally, enter a description for the task.
      3. Fetch baseline using: Use one of the following options to load the SQL plan:
        • SQL ID: Select this radio button to identify the SQL statement in the cursor cache whose SQL plans you want to load. On selecting this radio button, the following fields are displayed:
          • SQL ID: Enter the SQL statement ID.
          • Plan hash value: Optionally, enter the plan hash value of the SQL plan. If no value is provided, then all the plans present in the cursor cache for the SQL statement are loaded.
          • Parameter used to identify the SQL plan baseline into which the plans are loaded: Optionally, select either the SQL text or SQL handle radio buttons and enter the parameter value in the Parameter value field. If no value is provided, then the text of the identified SQL statement is extracted from the cursor cache and is used to identify the SQL plan baseline into which the plans are loaded. If the SQL plan baseline does not exist, it's created.
        • Filter name: Select this radio button to specify the filter to identify a SQL statement or a set of SQL statements. On selecting this radio button, the following fields are displayed:
          • Filter name: Select a filter name from the drop-down list.
          • Filter value: Enter the corresponding filter value.
      4. Plan attributes: Select the following check boxes to specify plan attributes:
        • Fixed: Select this check box to indicate that the loaded plans are fixed plans. A fixed plan is an accepted plan that is marked as preferred, so that the optimizer considers only the fixed plans in the baseline.
        • Enabled: Select this check box to indicate that the loaded plans are enabled plans. An enabled plan is eligible for use by the Oracle optimizer.
    2. Select one of the available options in the Credential type drop-down list in the Credentials section to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
    3. Click Save changes.

Perform SPM Configuration Tasks

You can perform SPM configuration tasks such as enabling or disabling SQL plan baseline and automatic plan capture parameters on the Configuration tab.

The Configuration tab has the following main sections:

  • SQL plan baseline: Provides the options to enable or disable SQL plan baseline. If SQL plan baseline is enabled, the following parameters are displayed in this section and to make changes to them, click Edit:
    • Plan retention (weeks): The number of weeks to retain unused SQL plans before they are purged. The period can range between 5 and 523 weeks and the default is 53 weeks.
    • Space budget (%): The maximum percentage of SYSAUX space that the SQL management base can use. The permissible range for this limit is between 1% and 50% and the default is 10%.
  • Automatic plan capture: Provides the options to enable or disable automatic plan capture. An automatic filter enables you to capture only statements that you want, and exclude non-critical statements. This technique saves space in the SYSAUX tablespace.

    If automatic plan capture is enabled, the following filters are displayed in this section and to make changes to them, click Edit:

    • Actions to include or Actions to exclude: The actions to be included or excluded from automatic capture.
    • Modules to include or Modules to exclude: The modules to be included or excluded from automatic capture.
    • Parsing schema names to include or Parsing schema names to exclude: The parsing schema names to be included or excluded from automatic capture.
    • SQL text to include or SQL text to exclude: The SQL text to be included or excluded from automatic capture.
    Note

    Selective plan capturing (filters) is only available for Oracle Databases version 12.2 and later.
  • Automatic SPM Evolve Advisor task: Provides the options to enable or disable the Automatic SPM Evolve Advisor task. If the Automatic SPM Evolve Advisor task is enabled, the following parameters are displayed in this section and to make changes to them, click Edit:
    • High-frequency Automatic SPM Evolve Advisor task: Displays whether the Automatic SPM Evolve Advisor task is enabled to occur more frequently.
    • Alternate plan sources: The sources to search for additional plans.
    • Alternate plan baselines: The alternative plans that must be loaded. The default value is Existing.
    • Alternate plan limit: The maximum number of plans to load in total. The default value is Unlimited.
    • Automatically accept plans: Displays whether the recommended plans must be accepted automatically.
    • Allowed time limit (sec): The global time limit in seconds. This is the total time allowed for the task.
    Note

    • For Autonomous Databases, SQL plan management configuration tasks cannot be performed.
    • SYSDBA privileges are required to edit the Automatic SPM Evolve Advisor task parameters.
    • Automatic SPM Evolve Advisor task is only available for Oracle Databases version 12.2 and later.
    • High-frequency Automatic SPM Evolve Advisor task is only available for Oracle Databases 19c and later running on the Oracle Exadata platform.