自适应SQL计划管理（Adaptive SQL Plan Management）在Oracle 12c中的应用

在Oracle Database 12c Release 1 (12.1)版本中，引入了对SQL计划管理（SPM）功能的增强，特别是关于SQL计划基线的自动进化机制。这一改进允许数据库更加智能地管理和优化SQL查询的执行计划，确保即使数据分布发生变化，也能维持高效的查询性能。

ASPM的核心理念在于其能够根据实时运行时统计信息来评估不同执行计划的表现，并据此决定是否需要采用新的执行计划。这意味着，即使是在数据库重启之后，优化器也能利用之前收集到的信息做出更加明智的选择。

一、SQL计划管理的基础

最初在Oracle 11g中引入的SQL计划管理，旨在通过固定一组已验证为高效的执行计划来稳定查询性能。这种方法被称为“保守的计划选择策略”，因为它倾向于使用经过验证的执行计划，而不是每次都让优化器重新评估所有可能的执行路径。到了Oracle 12c，虽然这个核心理念没有改变，但引入了一些重要的更新以进一步提升自动化程度和灵活性。

二、SYS_AUTO_SPM_EVOLVE_TASK介绍

2.1 自动化SQL计划基线的进化

在Oracle数据库12C中，现有基线的演变被自动化为称为SYS_AUTO_SPM_EVOVES_TASK的顾问任务，该任务是由自动化数据库维护任务下现有的“ SQL Tuning Advisor”客户端触发的。

CONN sys@pdb1 AS SYSDBACOLUMN client_name FORMAT A35
COLUMN task_name FORMAT a30SELECT client_name, task_name
FROM   dba_autotask_task;CLIENT_NAME                         TASK_NAME
----------------------------------- ------------------------------
sql tuning advisor                  AUTO_SQL_TUNING_PROG
auto optimizer stats collection     gather_stats_prog
auto space advisor                  auto_space_advisor_progSQL>

2.2 手动干预与任务管理

在Oracle 12c中，一个名为SYS_AUTO_SPM_EVOLVE_TASK的新任务被引入，用于自动进化现有的SQL计划基线。这个任务作为数据库维护任务的一部分，由现有的"sql tuning advisor"客户端触发。这意味着，一旦发现有更优的执行计划，系统可以自动对其进行评估，并在确认其优势后将其纳入基线之中。

CONN sys@pdb1 AS SYSDBACOLUMN parameter_name FORMAT A25
COLUMN parameter_value FORMAT a15SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
AND    parameter_value != 'UNUSED'
ORDER BY parameter_name;PARAMETER_NAME            PARAMETER_VALUE
------------------------- ---------------
ACCEPT_PLANS              TRUE
DAYS_TO_EXPIRE            UNLIMITED
DEFAULT_EXECUTION_TYPE    SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE  30
JOURNALING                INFORMATION
MODE                      COMPREHENSIVE
TARGET_OBJECTS            1
TIME_LIMIT                3600
_SPM_VERIFY               TRUESQL>

尽管自动化的进化过程极大简化了管理负担，但在某些情况下，手动干预仍然是必要的。例如，如果想要阻止新的计划自动被接受，可以调整ACCEPT_PLANS参数为FALSE：

BEGINDBMS_SPM.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',parameter => 'ACCEPT_PLANS',value     => 'FALSE');
END;
/

DBMS_SPM 包有一个名为 REPORT_AUTO_EVOLVE_TASK 的函数，用于显示自动进化任务所采取的行动的信息。如果没有指定参数，则会为最新的任务运行生成文本报告。

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100SELECT DBMS_SPM.report_auto_evolve_task
FROM   dual;REPORT_AUTO_EVOLVE_TASK
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------Task Information:---------------------------------------------Task Name            : SYS_AUTO_SPM_EVOLVE_TASKTask Owner           : SYSDescription          : Automatic SPM Evolve TaskExecution Name       : EXEC_461Execution Type       : SPM EVOLVEScope                : COMPREHENSIVEStatus               : COMPLETEDStarted              : 02/19/2025 15:53:33Finished             : 02/19/2025 15:53:35Last Updated         : 02/19/2025 15:53:35Global Time Limit    : 3600Per-Plan Time Limit  : UNUSEDNumber of Errors     : 0
---------------------------------------------------------------------------------------------SUMMARY SECTION
---------------------------------------------------------------------------------------------Number of plans processed  : 0Number of findings         : 0Number of recommendations  : 0Number of errors           : 0
---------------------------------------------------------------------------------------------
SQL>

三、手工演进SPB(SQL Plan Baselines)

在之前的版本中，使用EVOLVE_SQL_PLAN_BASELINE函数来实现SQL计划基线的演变。在12c中，这已被基于任务的方法所取代，通常涉及以下步骤。

CREATE_EVOLVE_TASK

EXECUTE_EVOLVE_TASK

REPORT_EVOLVE_TASK

IMPLEMENT_EVOLVE_TASK

此外，以下函数可以与进化任务进行交互：

CANCEL_EVOLVE_TASK

RESUME_EVOLVE_TASK

RESET_EVOLVE_TASK

为了展示这一点，我们需要创建一个SQL计划基线：

3.1 创建并填充测试表。

CONN test/test@pdb1DROP TABLE spm_test_tab PURGE;CREATE TABLE spm_test_tab (id           NUMBER,description  VARCHAR2(50)
);INSERT /*+ APPEND */ INTO spm_test_tab
SELECT level,'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

使用未索引的列查询表，这会导致对整个表进行扫描。

SET AUTOTRACE TRACESELECT description
FROM   spm_test_tab
WHERE  id = 99;Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    25 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------

通过查询V$SQL视图来识别SQL语句的SQL_ID。

CONN sys@pdb1 AS SYSDBASELECT sql_id
FROM   v$sql
WHERE  plan_hash_value = 1107868462
AND    sql_text NOT LIKE 'EXPLAIN%';SQL_ID
-------------
gat6z1bc6nc2dSQL>

3.2 使用此SQL_ID手动加载SQL计划基线。

SET SERVEROUTPUT ON
DECLAREl_plans_loaded  PLS_INTEGER;
BEGINl_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'gat6z1bc6nc2d');DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1PL/SQL procedure successfully completed.SQL>

DBA_SQL_PLAN_BASELINES 视图提供了有关 SQL 计划基线的信息。我们可以看到有一个与我们的基准关联的计划，该计划已启用并接受。

COLUMN sql_handle FORMAT A20
COLUMN plan_name FORMAT A30SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

3.3刷新共享池以强制进行另一个硬解析

然后在ID列上创建索引，然后重复查询以查看对执行计划的影响。

CONN sys@pdb1 AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;CONN test/test@pdb1CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);
EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);SET AUTOTRACE TRACESELECT description
FROM   spm_test_tab
WHERE  id = 99;Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    25 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID"=99)Note
------ SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement

请注意，查询没有使用新创建的索引，即使我们强制执行硬解析。注释解释了SQL计划基线被使用的原因。查看DBA_SQL_PLAN_BASELINES视图我们可以看到原因。

CONN sys@pdb1 AS SYSDBA
COLUMN sql_handle FORMAT A20
COLUMN plan_name FORMAT A30SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SQL_7b76323ad90440b9';SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES NO
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YESSQL>

SQL 计划基准线现在包含第二个计划，但尚未被接受。

如果您在DBA_SQL_PLAN_BASELINES视图中没有看到新行，请返回并重新运行"spm_test_tab"中的查询，直到您这样做。有时服务器需要几次尝试才能注意到对其他计划的需求。

3.4 创建新的进化任务

要使用新计划，我们需要等待维护窗口或手动发展SQL计划基线。为此基线创建一个新的进化任务。

SET SERVEROUTPUT ON
DECLAREl_return VARCHAR2(32767);
BEGINl_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_7b76323ad90440b9');DBMS_OUTPUT.put_line('Task Name: ' || l_return);
END;
/
Task Name: TASK_21PL/SQL procedure successfully completed.SQL>

执行进化任务。

SET SERVEROUTPUT ON
DECLAREl_return VARCHAR2(32767);
BEGINl_return := DBMS_SPM.execute_evolve_task(task_name => 'TASK_21');DBMS_OUTPUT.put_line('Execution Name: ' || l_return);
END;
/
Execution Name: EXEC_471PL/SQL procedure successfully completed.SQL>

进化任务结果报告。

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100SELECT DBMS_SPM.report_evolve_task(task_name => 'TASK_21', execution_name => 'EXEC_471') AS output
FROM   dual;OUTPUT
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------Task Information:---------------------------------------------Task Name            : TASK_21Task Owner           : SYSExecution Name       : EXEC_471Execution Type       : SPM EVOLVEScope                : COMPREHENSIVEStatus               : COMPLETEDStarted              : 02/19/2025 16:38:15Finished             : 02/19/2025 16:38:16Last Updated         : 02/19/2025 16:38:16Global Time Limit    : 2147483646Per-Plan Time Limit  : UNUSEDNumber of Errors     : 0
---------------------------------------------------------------------------------------------SUMMARY SECTION
---------------------------------------------------------------------------------------------Number of plans processed  : 2Number of findings         : 2Number of recommendations  : 2Number of errors           : 0
---------------------------------------------------------------------------------------------DETAILS SECTION
---------------------------------------------------------------------------------------------Object ID          : 2Test Plan Name     : SQL_PLAN_7qxjk7bch8h5t3652c362Base Plan Name     : SQL_PLAN_7qxjk7bch8h5tb65c37c8SQL Handle         : SQL_7b76323ad90440b9Parsing Schema     : SYSTest Plan Creator  : SYSSQL Text           : SELECT description FROM spm_test_tab WHERE id = 99Execution Statistics:
-----------------------------Base Plan                     Test Plan----------------------------  ----------------------------Elapsed Time (s):  .00001                        .000001CPU Time (s):      .000016                       .000001Buffer Gets:       4                             0Optimizer Cost:    13                            2Disk Reads:        0                             0Direct Writes:     0                             0Rows Processed:    0                             0Executions:        10                            10FINDINGS SECTION
---------------------------------------------------------------------------------------------Findings (1):
-----------------------------1. The plan was verified in 0.01700 seconds. It passed the benefit criterionbecause its verified performance was 14.66841 times better than that of thebaseline plan.Recommendation:
-----------------------------Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline(task_name => 'TASK_21', object_id => 2,task_owner => 'SYS');EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------Baseline Plan
-----------------------------Plan Id          : 101Plan Hash Value  : 3059496904-----------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |    1 |    25 |   13 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | SPM_TEST_TAB |    1 |    25 |   13 | 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=99)Test Plan
-----------------------------Plan Id          : 102Plan Hash Value  : 911393634---------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |    1 |    25 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB     |    1 |    25 |    2 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SPM_TEST_TAB_IDX |    1 |       |    1 | 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=99)---------------------------------------------------------------------------------------------Object ID          : 3Test Plan Name     : SQL_PLAN_7qxjk7bch8h5t3652c362Base Plan Name     : SQL_PLAN_7qxjk7bch8h5tb65c37c8SQL Handle         : SQL_7b76323ad90440b9Parsing Schema     : SYSTest Plan Creator  : SYSSQL Text           : SELECT description FROM spm_test_tab WHERE id = 99Execution Statistics:
-----------------------------Base Plan                     Test Plan----------------------------  ----------------------------Elapsed Time (s):  .000009                       .000001CPU Time (s):      0                             0Buffer Gets:       4                             0Optimizer Cost:    13                            2Disk Reads:        0                             0Direct Writes:     0                             0Rows Processed:    0                             0Executions:        10                            10FINDINGS SECTION
---------------------------------------------------------------------------------------------Findings (1):
-----------------------------1. The plan was verified in 0.00600 seconds. It passed the benefit criterionbecause its verified performance was 14.66667 times better than that of thebaseline plan.Recommendation:
-----------------------------Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline(task_name => 'TASK_21', object_id => 3,task_owner => 'SYS');EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------Baseline Plan
-----------------------------Plan Id          : 103Plan Hash Value  : 3059496904-----------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |    1 |    25 |   13 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | SPM_TEST_TAB |    1 |    25 |   13 | 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=99)Test Plan
-----------------------------Plan Id          : 104Plan Hash Value  : 911393634---------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |    1 |    25 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB     |    1 |    25 |    2 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SPM_TEST_TAB_IDX |    1 |       |    1 | 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=99)---------------------------------------------------------------------------------------------

3.5 接受新的进化任务

如果演进任务已完成并已报告建议,建议使用 ACCEPT_SQL_PLAN_BASELINE，但您应该真正使用 IMPLEMENT_EVOLVE_TASK。

SET SERVEROUTPUT ON
DECLAREl_return NUMBER;
BEGINl_return := DBMS_SPM.implement_evolve_task(task_name => 'TASK_21');DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return);
END;
/
Plans Accepted: 2PL/SQL procedure successfully completed.SQL>

DBA_SQL_PLAN_BASELINES 视图显示第二个计划已被接受。

CONN sys/pdb1 AS SYSDBASELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SQL_7b76323ad90440b9';SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YESSQL>

重复之前的测试表明，现在可以使用更高效的计划。

CONN test/test@pdb1SET AUTOTRACE TRACE LINESIZE 130SELECT description
FROM   spm_test_tab
WHERE  id = 99;Execution Plan
----------------------------------------------------------
Plan hash value: 2338891031--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB     |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("ID"=99)Note
------ SQL plan baseline "SQL_PLAN_7qxjk7bch8h5t3652c362" used for this statement

3.6 删除计划

如果您想删除计划，使用 DROP_SQL_PLAN_BASELINE 函数将其丢弃。

CONN sys@pdb1 AS SYSDBASET SERVEROUTPUT ON
DECLAREl_plans_dropped  PLS_INTEGER;
BEGINl_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_7b76323ad90440b9');DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/
Plans Dropped: 2PL/SQL procedure successfully completed.SQL>

查看执行计划

SET AUTOTRACE TRACE LINESIZE 130SELECT descriptionFROM   spm_test_tabWHERE  id = 99;Execution Plan
----------------------------------------------------------
Plan hash value: 2338891031--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB     |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("ID"=99)

通过这种方式，管理员能够细致地控制哪些计划被采纳以及何时采纳，从而确保数据库性能始终处于最优状态。

三、总结

综上所述，Oracle 12c中的自适应SQL计划管理不仅增强了数据库自我优化的能力，还提供了足够的灵活性让管理员根据具体需求进行定制化配置。这对于保持复杂环境下的高效运行至关重要。