当前位置： 首页 > article >正文

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

article 2025/2/21 9:40:26

在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计划管理不仅增强了数据库自我优化的能力，还提供了足够的灵活性让管理员根据具体需求进行定制化配置。这对于保持复杂环境下的高效运行至关重要。


http://www.kler.cn/a/554500.html

相关文章：

  • 企业软件合规性管理：构建高效、安全的软件资产生态
  • Linux系统配置阿里云yum源，安装docker
  • mac开发环境配置笔记
  • 什么是跨站脚本攻击（XSS）？
  • python：多重继承、MRO（方法解析顺序）
  • 茶叶叶片叶子品相识别检测数据集VOC+YOLO格式5631张2类别
  • TensorFlow深度学习实战——构建卷积神经网络实现CIFAR-10图像分类
  • 2025华为OD机试真题-猜字谜(C++)-E卷-100分
  • Ubuntu 服务器Llama Factory 搭建DeepSeek-R1微调训练环境
  • 【分布式理论16】分布式调度2：资源划分和调度策略
  • Zookeeper应用案例-服务器列表动态更新实现
  • 网关断网缓存：让网络连接更可靠
  • 电脑机箱散热风扇声音大的影响因素
  • 学习记录 DAY10 Tensorflow，神经网络视觉
  • 工控自动化领域：数字量信号与模拟量信号的差异解析
  • 2024华为OD机试真题-恢复数字序列(C++/Java/Python)-E卷-100分
  • 深入理解 Rust 中的 `Box＜T＞`：堆上的数据与递归类型
  • Grok 3 官宣免费使用
  • 2025鸿蒙开发面试题汇总——通俗易懂
  • leetcode刷题第十三天——二叉树Ⅳ