本文介绍Oracle数据库11g中的SQL计划管理器(SPM)如何管理和优化执行计划。包括如何手动捕获执行计划、如何使计划生效及如何通过演化使更优计划成为默认计划。
在 Oracle Database 11g 中，当一个已经计算好的优化器计划由于底层因素的更改而需要更新时，新计划不会立即实施。Oracle 会对这个新计划进行评估。仅当它比原有计划更有效时，Oracle 才会实施新计划。此外，还可以使用工具和接口来查看为每个查询计算的计划的历史，以及这些计划的对比情况。
当 Oracle 将一个语句确定为多次执行或“可重复的”语句，声明周期开始。一旦确定了一个可重复语句，数据库即会捕获它的计划，并将该计划作为 SQL 计划基线存储在数据库一个称为 SQL 管理库 (SMB) 的逻辑结构中。当出于任何原因为该查询计算新计划时，新计划也存储在 SMB 中。因此，SMB 用于存储查询的每个计划、计划的生成方式等等。
计划不会自动存储在 SMB 中。如果上述情况属实，SMB 将存储每类查询的所有计划，并将变得十分庞大。因此，您可以并且应该控制 SMB 存储的查询的数量。执行该操作有两种方法：自动为 SMB 中的所有可重复查询设定基线，或手动加载应设定基线的查询。
SPM 相关的语句日志、计划历史记录和计划基线都存储在SQL 管理库(SMB) 中，该库还包含SQL概要文件。SMB 是数据库字典的一部分，存储在SYSAUX 表空间中。默认情况下，SMB 的空间预算限制被设置为SYSAUX 大小的10%。但是，可以使用DBMS_SPM.CONFIGURE 过程配置SMB，将空间预算更改为介于1% 和50%之间的一个值。
如果SMB 空间超过了定义的百分比限制，则会向预警日志中写入警告。通过清除一些SQL管理对象（如SQL 计划基线或SQL概要文件）来增加SMB 空间限制、增加SYSAUX 大小或者减小SMB 大小之前，将按周生成警报。
下面就做一个示例说明一下spm的作用，不过用的是手动捕捉执行计划
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数用来控制SPM的自动捕获，该参数默认值为FALSE。当该参数设置为TRUE时，对于重复执行的SQL 都会被观测，其对应的执行计划也会被加入Plan History。生成的第一个执行计划被标记为ENABLED并且是ACCEPTED，后续的执行计划会被标记为ENABLED但不是ACCEPTED。 仅当在演化的过程中，性能最优的Plan (即标记为ACCEPTED)才会被添加到 SQL Plan baseline。
SQL> show parameter optimizer_capture_sql_plan_baselines --默认没有自动收集
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES; --这个值默认就是TRUE的，就不需要更改
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines boolean TRUE
创建测试表及插入数据：
SQL> create table spm_test_tab(
2 id number,
3 description varchar2(50));
Table created.
[oracle@qht131 ~]$ cat spm.sql
DECLARE
TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
l_tab t_tab := t_TAB();
BEGIN
FOR i IN 1 .. 10000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO spm_test_tab VALUES l_tab(i);
COMMIT;
END;
/
SQL> @spm.sql
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);
PL/SQL procedure successfully completed.
目前这个表没有建立索引，通过全表扫描进行查询：
SQL> set autot trace
SQL> 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 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------
获取这条sql的sql_id
SQL> set autot off
SQL> select sql_id from v$sql where sql_text like 'select description from spm_test_tab where id=99%'
2 and sql_text not like '%dba_sql_plan_baselines%' and sql_text not like '%EXPLAIN%';
SQL_ID
-------------
0m8y1uv3csxwr
使用SQL_ID 从cursorcache中手工捕获执行计划：
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
5 sql_id =>'0m8y1uv3csxwr');
6 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
7 END;
8 /
Plans Loaded: 1
PL/SQL procedure successfully completed.
执行计划被捕获后，可以使用DBA_SQL_PLAN_BASELINES视图查看SPM 信息：
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 120
SQL> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE 'select description from spm_test_tab where id=99%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
手动捕获的执行计划的enabled和accepted都为YES，说明让oracle接受这个执行计划
接着刷新Share Pool，使下次SQL 执行时必须进行硬解析：
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
创建索引及收集信息：
SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER,'SPM_TEST_TAB', cascade=>TRUE);
PL/SQL procedure successfully completed.
执行相同的sql：
SQL> set autot trace
SQL> 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 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------
id列已创建了索引，明显走索引要更加的合理
查看一下SPM视图的信息：
SQL> set autot off
SQL> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE SQL_HANDLE='SQL_7b76323ad90440b9';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5ted3324c0 YES NO
通过baselines查询的结果，可以看到我们的SQL 产生了2条执行计划。但是我们认为最优的执行计划并没有被标记为ACCEPT，所以没有使用。
演化执行计划： 演化就是将cost低的执行计划标记为accept
那么试一下如果再执行一次这条sql，会自动变为accept?因为自适应游标在第二次执行时就会走正确的执行计划
SQL> set autot trace
SQL> 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 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------
SQL> set autot off
SQL> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE SQL_HANDLE='SQL_7b76323ad90440b9';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5ted3324c0 YES NO
再次执行此sql时，还是没有走正确的执行计划，说明全表扫描的执行计划已被绑定了，oracle不会考虑accepted为NO的执行计划。
演化执行计划： 演化就是将cost低的执行计划标记为accept
SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle =>'SQL_7b76323ad90440b9') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_7b76323ad90440b9
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
COMMIT = YES
Plan: SQL_PLAN_7qxjk7bch8h5ted3324c0
------------------------------------
Plan was verified: Time used .06 seconds.
Plan passed performance criterion: 15 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
Rows Processed: 1 1
Elapsed Time(ms): .135 .025 5.4
CPU Time(ms): .111 0
Buffer Gets: 45 3 15
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
再次查看DBA_SQL_PLAN_BASELINES视图：
SQL> SELECT sql_handle, plan_name,enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE SQL_HANDLE='SQL_7b76323ad90440b9';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5ted3324c0 YES YES
plan_name为“SQL_PLAN_7qxjk7bch8h5ted3324c0”的accepted已变为YES了
再次执行sql：
SQL> set autot trace;
SQL> select description from spm_test_tab where id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 3121206333
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------这次正确的使用了索引。 因为只有标记为ENABLE和 ACCEPT的plan 才可以被使用。
参考：https://blog.youkuaiyun.com/tianlesoftware/article/details/8296524
扫一扫
690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
