自适应SQL计划管理（AdaptiveSQLPlanManagement）在Oracle12c中的应用

在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 SYSDBA COLUMN client_name FORMAT A35 COLUMN task_name FORMAT a30 SELECT 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_prog SQL> 2.2 手动干预与任务管理

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

CONN sys@pdb1 AS SYSDBA COLUMN parameter_name FORMAT A25 COLUMN parameter_value FORMAT a15 SELECT 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 TRUE SQL>

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

BEGIN DBMS_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 100 SELECT DBMS_SPM.report_auto_evolve_task FROM dual; REPORT_AUTO_EVOLVE_TASK ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve Task Execution Name : EXEC_461 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 02/19/2025 15:53:33 Finished : 02/19/2025 15:53:35 Last Updated : 02/19/2025 15:53:35 Global Time Limit : 3600 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 0 Number of findings : 0 Number of recommendations : 0 Number 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@pdb1 DROP 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 TRACE SELECT 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 SYSDBA SELECT sql_id FROM v$sql WHERE plan_hash_value = 1107868462 AND sql_text NOT LIKE 'EXPLAIN%'; SQL_ID ------------- gat6z1bc6nc2d SQL>

3.2 使用此SQL_ID手动加载SQL计划基线。  SET SERVEROUTPUT ON DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => 'gat6z1bc6nc2d'); DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); END; / Plans Loaded: 1 PL/SQL procedure successfully completed. SQL>

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

COLUMN sql_handle FORMAT A20 COLUMN plan_name FORMAT A30 SELECT 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@pdb1 CREATE INDEX spm_test_tab_idx ON spm_test_tab(id); EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE); SET AUTOTRACE TRACE SELECT 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 A30 SELECT 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 YES SQL>

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

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

3.4 创建新的进化任务

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

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

执行进化任务。 

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

进化任务结果报告。 

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100 SELECT 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_21 Task Owner : SYS Execution Name : EXEC_471 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 02/19/2025 16:38:15 Finished : 02/19/2025 16:38:16 Last Updated : 02/19/2025 16:38:16 Global Time Limit : 2147483646 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 2 Number of findings : 2 Number of recommendations : 2 Number of errors : 0 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test Plan Name : SQL_PLAN_7qxjk7bch8h5t3652c362 Base Plan Name : SQL_PLAN_7qxjk7bch8h5tb65c37c8 SQL Handle : SQL_7b76323ad90440b9 Parsing Schema : SYS Test Plan Creator : SYS SQL Text : SELECT description FROM spm_test_tab WHERE id = 99 Execution Statistics: ----------------------------- Base Plan Test Plan ---------------------------- ---------------------------- Elapsed Time (s): .00001 .000001 CPU Time (s): .000016 .000001 Buffer Gets: 4 0 Optimizer Cost: 13 2 Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 0 0 Executions: 10 10 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The plan was verified in 0.01700 seconds. It passed the benefit criterion because its verified performance was 14.66841 times better than that of the baseline plan. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_21', object_id => 2, task_owner => 'SYS'); EXPLAIN PLANS SECTION --------------------------------------------------------------------------------------------- Baseline Plan ----------------------------- Plan Id : 101 Plan 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 : 102 Plan 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 : 3 Test Plan Name : SQL_PLAN_7qxjk7bch8h5t3652c362 Base Plan Name : SQL_PLAN_7qxjk7bch8h5tb65c37c8 SQL Handle : SQL_7b76323ad90440b9 Parsing Schema : SYS Test Plan Creator : SYS SQL Text : SELECT description FROM spm_test_tab WHERE id = 99 Execution Statistics: ----------------------------- Base Plan Test Plan ---------------------------- ---------------------------- Elapsed Time (s): .000009 .000001 CPU Time (s): 0 0 Buffer Gets: 4 0 Optimizer Cost: 13 2 Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 0 0 Executions: 10 10 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The plan was verified in 0.00600 seconds. It passed the benefit criterion because its verified performance was 14.66667 times better than that of the baseline plan. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_21', object_id => 3, task_owner => 'SYS'); EXPLAIN PLANS SECTION --------------------------------------------------------------------------------------------- Baseline Plan ----------------------------- Plan Id : 103 Plan 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 : 104 Plan 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 DECLARE l_return NUMBER; BEGIN l_return := DBMS_SPM.implement_evolve_task(task_name => 'TASK_21'); DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return); END; / Plans Accepted: 2 PL/SQL procedure successfully completed. SQL>

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

CONN sys/pdb1 AS SYSDBA SELECT 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 YES SQL>

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

CONN test/test@pdb1 SET AUTOTRACE TRACE LINESIZE 130 SELECT 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 SYSDBA SET SERVEROUTPUT ON DECLARE l_plans_dropped PLS_INTEGER; BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_7b76323ad90440b9'); DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped); END; / Plans Dropped: 2 PL/SQL procedure successfully completed. SQL>

查看执行计划

SET AUTOTRACE TRACE LINESIZE 130 SELECT 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)

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

三、总结

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