很多时候可能我们都希望 CBO 能够帮我们生成正确、高效的执行计划，但是很多时候事实并非如此，可能因为各种各样的原因 (如，统计信息不正确或者 CBO 天生的缺陷等) 都会导致生成的执行计划特别的低效。之前的一家公司有一台专门用于批量做数据校验清洗的数据库，每次校验清洗完成数据就会清理掉，统计信息经常会发生较大的变更，之前跑得好好的 SQL，可能有时候跑 5-6 个小时都跑不完了，这时候查看执行计划，发现不正确的统计信息导致了执行计划的变更。
这时候我们就希望数据库中运行的 SQL 都能有正确、稳定的执行计划，在 10g 开始的版本中可以通过 SQL Profile 来稳定执行计划或者在不改变 SQL 的情况下修改执行计划。11g 开始可以使用偏主动的稳定执行计划的手段 ——SPM (SQL PLAN MANAGEMENT)，保证只有被验证过的执行计划才会被启用。
SQL Profile
SQL Profile 是包含特定于 SQL 语句的辅助统计信息的数据库对象，可以改进优化器基数估计，从而选择更好的执行计划。 当选择执行计划时优化器会考虑以下信息：
- SQL Profile 提供的辅助统计信息
- 当时 SQL 的运行环境，如数据库配置，变量绑定，与优化器相关的统计信息等。
所以，上面两个条件的任意一个发生变化，都有可能导致执行计划的改变。下面看下 SQL Profile 的一些基本操作以及如何在线进行 SQL 的调整。
accepting sql profile
通过 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 存储过程可以接受一个 SQL Profile，只有在我们接受了一个 SQL Profile 之后，优化器才能使用他作为产生执行计划的输入。这个存储过程有两个比较重要的参数：
- profile_type 这个参数用于控制是否改变并行执行行为，
REGULAR_PROFILE不更改为并行执行，PX_PROFLE用于更改并行执行的 SQL Profile。 - force_match 该参数用于控制 SQL 语句匹配，有两个值 ——TRUE 和 FALSE。对于 SQL 语句中 where 条件的字面值，当 force_match=TRUE 时，会将其替换为变量绑定，所以当字面值不同时也可以重用该 SQL Profile。值为 FALSE 时，where 条件的字面值则不会替换。
下面是 ACCEPT_SQL_PROFILE 的例子：
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'STA_SPECIFIC_EMP_TASK'
, name => 'my_sql_profile'
, profile_type => DBMS_SQLTUNE.PX_PROFILE
, force_match => true
);
END;
/
Listing SQL Profile
可以通过 DBA_SQL_PROFILES 数据字典视图来查看存储在数据库中的 SQL Profile。
SQL> SELECT NAME,CATEGORY,SQL_TEXT,FORCE_MATCHING,STATUS FROM DBA_SQL_PROFILES;
NAME CATEGORY SQL_TEXT FOR STATUS
------------------------------ ---------- ------------------------- --- --------
SYS_SQLPROF_016986bccd640000 DEFAULT select /*+ use_nl(a b) in NO ENABLED
dex(b) */a.brwyid,a.yljgd
m,a.jzlsh,b.mzzddm from t
est_e
Altering SQL Profile
通过 ALTER_SQL_PROFILE 中的 attribute_name 参数可以修改 SQL Profile 相应的参数值。
BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name => 'my_sql_profile'
, attribute_name => 'FORCE_MATCH'
, value => 'TRUE'
);
END;
/
Droping SQL Profile
通过 DROP_SQL_PROFILE 存储过程可以删除特定的 SQL Profile
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name => 'my_sql_profile'
);
END;
/
通过 SQL Profile 调整线上 SQL 执行计划
通过手工创建 SQL Profile 的方式，可以在不更改目标 SQL 的 SQL 文本的情况下修改 SQL 的执行计划，而且可以很好的稳定 SQL 的执行计划。 下面是手工创建 SQL Profile 的例子，在 TEST_ENV.TB_TABLE_LIST 的列 TABLE_NAME 上有一个名为 IDX_TB_TABLE_LIST_TBNAME 的 B 树索引： 1、首先加一个全表扫描的 HINTS 来执行下面的 SQL，模拟线上的一个执行低效的 SQL，并查看其执行计划。
SQL> SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
------------------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 1a319c1c2b3rz, child number 0
-------------------------------------
SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 1475094007
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TB_TABLE_LIST | 1 | 18 | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]
已选择 43 行。
2、然后加入走索引的 HINTS 来更正这个 SQL 的执行计划，得到下面的执行计划相关信息，此时我们就需要用这个执行计划来替换掉上面走全表扫描的 SQL 的执行计划。
SQL> SELECT /*+ INDEX(T IDX_TB_TABLE_LIST_TBNAME) */TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
------------------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 44j1ysb93cwdq, child number 0
-------------------------------------
SELECT /*+ INDEX(T IDX_TB_TABLE_LIST_TBNAME) */TABLE_NAME FROM
TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$'
Plan hash value: 3318876060
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| IDX_TB_TABLE_LIST_TBNAME | 1 | 18 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TABLE_NAME"[VARCHAR2,128]
已选择 43 行。
3、下面查看对应的 SQL_ID。
SQL> SELECT SQL_TEXT,SQL_ID FROM V$SQLAREA WHERE SQL_TEXT LIKE '%TABLE_NAME FROM TEST_ENV.TB_TABLE%';
SQL_TEXT SQL_ID
------------------------- -------------
SELECT /*+ INDEX(T IDX_TB 44j1ysb93cwdq
_TABLE_LIST_TBNAME) */TAB
LE_NAME FROM TEST_ENV.TB_
TABLE_LIST T WHERE TABLE_
NAME='ACCESS$'
SELECT SQL_TEXT,SQL_ID FR g4v1sg4ycf96y
OM V$SQLAREA WHERE SQL_TE
XT LIKE '%TABLE_NAME FROM
TEST_ENV.TB_TABLE%'
SQL_TEXT SQL_ID
------------------------- -------------
SELECT /*+FULL(T)*/ TABLE 1a319c1c2b3rz
_NAME FROM TEST_ENV.TB_TA
BLE_LIST T WHERE TABLE_NA
ME='ACCESS$'
4、创建 SQL PROFILE，用正确的执行计划的 OUT LINE DATA 来创建 SQL Profile
SQL> declare
2 v_hints sys.sqlprof_attr;
3 clsql_text clob;
4 begin
5 v_hints := sys.sqlprof_attr('BEGIN_OUTLINE_DATA',
6 'IGNORE_OPTIM_EMBEDDED_HINTS',
7 'OPTIMIZER_FEATURES_ENABLE(''18.1.0'')',
8 'DB_VERSION(''18.1.0'')',
9 'ALL_ROWS',
10 'OUTLINE_LEAF(@"SEL$1")',
11 'INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))',
12 'END_OUTLINE_DATA');
13
14 select sql_fulltext into clsql_text from v$sqlarea where sql_id='1a319c1c2b3rz';
15
16 dbms_sqltune.import_sql_profile(clsql_text,v_hints,'my_sql_profile',force_match=>true,replace=>true);
17 end;
18 /
PL/SQL 过程已成功完成。
5、最后再来看加 FULL 这个 HINTS 的 SQL 语句的执行计划，可以看到此时已经是做的索引范围扫描了。
SQL> SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
------------------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 1a319c1c2b3rz, child number 0
-------------------------------------
SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 3318876060
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| IDX_TB_TABLE_LIST_TBNAME | 1 | 18 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TABLE_NAME"[VARCHAR2,128]
Note
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----
- SQL profile my_sql_profile used for this statement
已选择 47 行。
SPM
SQL Plan Management (SPM) 可以有效避免执行计划变更而导致的性能下降的问题，只有被验证和接受的执行计划才是可用的。SPM 采用了一种叫 SQL PLAN BASELINE 的机制，它是一系列被验证性能良好的 SQL 执行计划的集合。不管 SQL Plan Baseline 还是 SQL Profile 都是通过内部使用 hints 来实现的，他们之间的区别如下：
- SQL Plan BaseLine 是一种偏主动的机制，可以在性能问题出现之前就创建 SQL 基线，避免优化器在未来某个时刻选择次优的执行计划。而 SQL Profile 只能等到发现 SQL 的性能问题时，调用 SQL Tuning Advisor 来对有问题的 SQL 进行调整。
- SQL Plan Baseline 会从新产生一个具体的执行计划，不会随着其他相关统计信息的变更而变更，但是 SQL Profile 只是为优化器提供一个辅助信息。 SQL Plan Baseline 的 hints 会指定生成一个具体的执行计划，但是 SQL Profile 的 hints 只是帮助调整优化器错误的计算
初始化参数配置
可以通过以下两个参数来控制 SPM 的行为:
- OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE|FALSE 该参数用于控制是否启用自动捕获 SQL Plan Baseline，默认值为 FALSE。该参数可以在 SESSION 或者 SYSTEM 级别进行修改，修改为 TRUE 后，ORACLE 会对其影响的范围内的所有重复执行的 SQL 自动捕获其 SQL Plan Baseline。对于第一次捕获到的结果，其 ENABLED 和 ACCEPTED 的值均为 'YES'。当执行计划变更，被再次捕获到时，其 ENABLED=YES 但是 ACCEPTED 的值为 'NO'，表示该 SQL 依然会使用第一次捕获到的 SQL 执行计划。
- OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE|FALSE 该参数用于控制是否启用 SQL Plan Baseline，默认值为 TRUE，该参数也可以在 SESSION 和 SYSTEM 级别设置。
查看 SQL Plan Baseline 中的执行计划
通过数据字典 DBA_SQL_PLAN_BASELINES 可以查询到存储在数据库中的 SQL Plan Baselines，然后使用 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE 函数可以查看对应的执行计划。
SELECT SIGNATURE,SQL_TEXT,SQL_HANDLE,PLAN_NAME FROM DBA_SQL_PLAN_BASELINES;
SELECT * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_787830cec4402bdf','SQL_PLAN_7hy1htv240ayz01f095c0','advanced'));
批量加载 SQL Plan Baseline
通过 DBMS_SPM 包提供的相关函数，我们可以从 SQL Tuning Set、 Shared SQL Area 和 Staging Table 中加载 SQL Plan Baseline。比较常用的是通过 Staging Table 的方式来进行不同数据库之间 SQL Plan Baseline 的迁移，比如我们在测试库中调试好了一批 SQL，需要将其执行计划导入到生产库中。 下面是通过 Staging Table 方式将 A 库的 SQL Plan Baseline 迁移到 B 库的基本流程：
- 使用 DBMS_SPM.CREATE_STGTAB_BASELINE 创建中间表。
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE (
table_name => 'stage1');
END;
/
- 将 SQL Plan Baseline 打包到刚刚创建的中间表 stage1
DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
table_name => 'stage1'
, enabled => 'yes'
, creator => 'spm'
);
END;
/
- 将中间表传输到目标数据库中，可以通过数据泵等手段。
- 在目标库中，将中间表的数据解压。
DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'stage1'
, fixed => 'yes'
);
END;
/
删除 SQL Plan Baseline
通过 DBMS_SPM.DROP_SQL_PLAN_BASELINE 函数可以删除已经保存的 SQL Plan Baseline：
- 首先通过
DBA_SQL_PLAN_BASELINES获取到对应的 SQL_HANDLE - 执行删除操作
SQL> EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle => 'SQL_b6b0d1c71cd1807b');
实例 1—— 自动捕获
- 修改
optimizer_capture_sql_plan_baselines参数启用自动捕获
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> alter system set optimizer_capture_sql_plan_baselines=TRUE;
系统已更改。
SQL> show parameter sql_pla
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
- 第一次执行 SQL，可以看到在 DBA_SQL_PLAN_BASELINES 中并未查到相应的 SQL 基线，再次执行才能查询到。
SQL> SET PAGES 10000 LINES 140
SQL> SET SERVEROUTPUT ON
SQL> COL SQL_TEXT FOR A20
SQL> COL SQL_HANDLE FOR A20
SQL> COL PLAN_NAME FOR A30
SQL> COL ORIGIN FOR A12
SQL> COL TABLE_NAME FOR A20
SQL> SET LONGC 60535
SQL> SET LONG 60535
SQL> SET ECHO ON
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES W
HERE SQL_TEXT LIKE '%TEST_ENV.TB_TABLE_LIST%';
未选定行
- 再次执行上面的 SQL。查询 DBA_SQL_PLAN_BASELINES，对应的 SQL Plan Baseline 的 ACCEPTED 和 ENABLE 的值均为 YES。
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES W
HERE SQL_TEXT LIKE '%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT
-------------------- -------------------- ------------------------------ ------------ --- --- --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 AUTO-CAPTURE YES YES NO YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
- 在表上创建一个 TABLE_NAME 列的 B-TREE 索引，然后再次执行上面的 SQL 语句，通过 DBA_SQL_PLAN_BASELINES 查询，此时多了一条 SQL Plan Baseline，ENABLE=YES，ACCEPTED=NO
SQL> CREATE INDEX test_env.IDX_TB_TABLE_LIST_TBNAME ON TEST_ENV.TB_TABLE_LIST(TABLE_NAME);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST_ENV','TB_TABLE_LIST',CASCADE=>TRUE);
PL/SQL 过程已成功完成。
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES W
HERE SQL_TEXT LIKE '%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT
-------------------- -------------------- ------------------------------ ------------ --- --- --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE YES NO NO YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 AUTO-CAPTURE YES YES NO YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
- 在 TABLE_NAME 列创建索引之后，根据此列查询，应该是走 INDEX RANGE SCAN，但是事实是怎么样呢？看下该语句的执行计划。
SQL> explain plan for SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------
Plan hash value: 1475094007
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TB_TABLE_LIST |
-------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7hy1htv240ayzc127edb7" used for this statement
已选择 12 行。
可以看到，执行这个 SQL 依然走的是全表扫描，注意 note 部分，表示这个执行计划使用了 "SQL_PLAN_7hy1htv240ayzc127edb7" 的 SQL Plan Baseline。表明了 SPM 可以很好的固定特定 SQL 的执行计划。 6. 但是实际上此时应该是走索引范围扫描才是最高效的，即 "SQL_PLAN_7hy1htv240ayz01f095c0" 这个 SQL Plan Baseline，如何启用改 baseline 呢？首先创建一个 evole 任务，并执行该任务。
SQL> var cnt NUMBER
SQL> var tk_name VARCHAR2(50)
SQL> var exe_name VARCHAR2(50)
SQL> var evol_out CLOB
SQL> EXEC :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_787830cec4402bdf', plan_name => 'SQL_PLAN_7hy1htv2
40ayz01f095c0');
PL/SQL 过程已成功完成。
SQL> print :tk_name
TK_NAME
--------
任务_31
SQL> EXEC :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);
PL/SQL 过程已成功完成。
SQL> print :exe_name
EXE_NAME
--------
EXEC_151
- 执行完成之后，通过
REPORT_EVOLVE_TASK可以查看到相应的任务报告。Findings 部分告诉我们发现了一个比基线效率更高的执行计划，并提供了建议方案。
SQL> EXEC :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
PL/SQL 过程已成功完成。
SQL> SELECT :evol_out FROM DUAL;
:EVOL_OUT
------------------------------------------------------------------------------------------------------------------------
--------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : 任务_31
Task Owner : SYS
Execution Name : EXEC_151
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 04/19/2019 16:45:33
Finished : 04/19/2019 16:45:33
Last Updated : 04/19/2019 16:45:33
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_7hy1htv240ayz01f095c0
Base Plan Name : SQL_PLAN_7hy1htv240ayzc127edb7
SQL Handle : SQL_787830cec4402bdf
Parsing Schema : SYS
Test Plan Creator : SYS
SQL Text : SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000016 .000001
CPU Time (s): 0 0
Buffer Gets: 10 0
Optimizer Cost: 31 1
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (1):
-----------------------------
1. 计划已在 0.03200 秒内验证完毕。此计划符合收益标准, 这是因为其验证性能比基线计划的性能高 50.50000 倍。
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => '任务_31', object_id => 2,
task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 201
Plan Hash Value : 3240619447
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 31 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TB_TABLE_LIST | 1 | 18 | 31 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("TABLE_NAME"='ACCESS$')
Test Plan
-----------------------------
Plan Id : 202
Plan Hash Value : 32544192
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 1 | 00:00:01 |
| * 1 | INDEX RANGE SCAN | IDX_TB_TABLE_LIST_TBNAME | 1 | 18 | 1 | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("TABLE_NAME"='ACCESS$')
---------------------------------------------------------------------------------------------
- 可以通过上面报告中建议的方式来接受这个计划，也可以使用
IMPLEMENT_EVOLVE_TASK函数。
SQL> EXEC :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
PL/SQL 过程已成功完成。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE='
SQL_787830cec4402bdf';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE YES YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 AUTO-CAPTURE YES YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
- 最后我们再来看下执行计划
SQL> explain plan for SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3318876060
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| IDX_TB_TABLE_LIST_TBNAME |
-----------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7hy1htv240ayz01f095c0" used for this statement
已选择 12 行。
可以看到此时已经走了索引范围扫描，使用的是 "SQL_PLAN_7hy1htv240ayz01f095c0" 这个 SQL Plan Baseline。当存在多个计划的 ACCEPTED 和 ENABLE 的值都为 YES 的时候，优化器会选择一个成本更低的计划来执行。
实例 2—— 手工生成
接下来看下手工生成 SQL Plan Baseline 的方法。其实非常简单，核心就是通过 DBMS_STATS.LOAD_PLANS_FROM_CURSOR_CACHE 来从 Shared SQL Area 中加载执行计划。
- 首先对前面例子生成的内容进行清理。
SQL> alter system set optimizer_capture_sql_plan_baselines=false;
系统已更改。
SQL> show parameter sql_pla
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> EXEC :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_787830cec4402bdf');
PL/SQL 过程已成功完成。
SQL> EXEC :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_061becb140fad607');
PL/SQL 过程已成功完成。
SQL> EXEC :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_ecca2815c7166fb6');
PL/SQL 过程已成功完成。
SQL> EXEC :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_9049245213a986b3');
PL/SQL 过程已成功完成。
SQL> EXEC :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_85372e07e425b213');
PL/SQL 过程已成功完成。
SQL> DELETE FROM SQLLOG$;
已删除 9 行。
SQL> commit;
提交完成。
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
- 执行 SQL 并查看执行计划
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID drmkgq2ppg7kg, child number 0
-------------------------------------
SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 1475094007
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TB_TABLE_LIST | 1 | 18 | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]
已选择 43 行。
- 因为此时自动捕获是关闭的，所以不管执行多少次 SQL，都没有对应的 SQL Plan Baseline。可以手动的将对应的执行计划加入到 SQL Plan Baseline 中。
SQL> var cnt number
SQL> exec :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'drmkgq2ppg7kg', plan_hash_value=>'1475094007');
PL/SQL 过程已成功完成。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 MANUAL-LOAD- YES YES
OM TEST_ENV.TB_TABLE FROM-CURSOR-
_LIST T WHERE TABLE_ CACHE
NAME='ACCESS$'
- 在 TABLE_NAME 列上创建索引，然后再次执行 SQL，并查看执行计划。可以看到虽然在 TABLE_NAME 这个列上存在索引，但是已经存在对应 SQL 语句的一个走全表扫描的执行基线，所以此时依旧走的全表扫描，使用的是刚刚加载进去的 'SQL_PLAN_7hy1htv240ayzc127edb7' 这个 SQL Plan Baseline。
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID drmkgq2ppg7kg, child number 1
-------------------------------------
SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 1475094007
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TB_TABLE_LIST | 1 | 18 | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]
Note
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
-----
- SQL plan baseline SQL_PLAN_7hy1htv240ayzc127edb7 used for this statement
已选择 47 行。
- 这个时候我们再来查看下 DBA_SQL_PLAN_BASELINES 这个数据字典，可以看到 SPM 帮我们自动捕获了一条 ENABLE 为 YES，ACCEPTED 为 NO 的 SQL Plan Baseline。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE
'%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE YES NO
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 MANUAL-LOAD- YES YES
OM TEST_ENV.TB_TABLE FROM-CURSOR-
_LIST T WHERE TABLE_ CACHE
NAME='ACCESS$'
- 将 'SQL_PLAN_7hy1htv240ayz01f095c0' 的 ACCEPTED 的值改为 YES。
SQL> var result clob;
SQL> EXEC :result :=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_787830cec4402bdf', PLAN_NAME=>'SQL_PLAN_7hy1htv24
0ayz01f095c0', VERIFY=>'NO', COMMIT=>'YES');
PL/SQL 过程已成功完成。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE
'%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE YES YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 MANUAL-LOAD- YES YES
OM TEST_ENV.TB_TABLE FROM-CURSOR-
_LIST T WHERE TABLE_ CACHE
NAME='ACCESS$'
- 再次执行 SQL，此时使用了 'SQL_PLAN_7hy1htv240ayz01f095c0' 这个 SQL Plan Baseline 了。
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID drmkgq2ppg7kg, child number 0
-------------------------------------
SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 3318876060
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| IDX_TB_TABLE_LIST_TBNAME | 1 | 18 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TABLE_NAME"[VARCHAR2,128]
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----
- SQL plan baseline SQL_PLAN_7hy1htv240ayz01f095c0 used for this statement
已选择 47 行。
- 前面的 SQL 都是没有 HINTS 的，有时候我们需加入 HINTS 对 SQL 的执行计划进行调整。如何用加入了 hints 的 SQL 执行计划来替换之前的 SQL，生成新的 SQL Plan Baseline 呢？首先删除之前做全表扫描的 SQL Plan Baseline，然后加入一个 FULL 的 hints 来执行 SQL，此时 SQL_ID 和之前是不同的。
SQL> exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle=>'SQL_787830cec4402bdf', plan_name=>'SQL_PLAN_7hy1htv240ayz
c127edb7');
PL/SQL 过程已成功完成。
SQL> SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
SQL_ID 1a319c1c2b3rz, child number 0
-------------------------------------
SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 1475094007
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TB_TABLE_LIST | 1 | 18 | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]
已选择 43 行。
此时从索引范围扫描变成了全表扫描，SQL_ID 为 1a319c1c2b3rz，PLAN_HASH_VALUE 为 1475094007 9. 此时的 DBA_SQL_PLAN_BASELINES 依然之后刚刚那一条记录。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE
'%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE YES YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
- 现在用上述改写后的 SQL 的新的执行计划来手工生成 SQL Plan Baseline
SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '1a319c1c2b3rz', plan_hash_value=>1475094007, sql_handle=>'SQL_787830cec4402bdf')
PL/SQL 过程已成功完成。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE
'%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE YES YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 MANUAL-LOAD- YES YES
OM TEST_ENV.TB_TABLE FROM-CURSOR-
_LIST T WHERE TABLE_ CACHE
NAME='ACCESS$'
- 由于此时所对应的 SQL Plan Baseline 的 ENABLED 和 ACCEPTED 的值都为 YES，我们将之前的走索引范围扫描的 ENABLED 属性改为 NO，然后执行 SQL 语句。
SQL> EXEC :CNT := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_787830cec4402bdf', PLAN_NAME=>'SQL_PLAN_7hy1htv240ay
z01f095c0', ATTRIBUTE_NAME=>'ENABLED', ATTRIBUTE_VALUE=>'NO');
PL/SQL 过程已成功完成。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE
'%TEST_ENV.TB_TABLE_LIST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayz01f095c0 AUTO-CAPTURE NO YES
OM TEST_ENV.TB_TABLE
_LIST T WHERE TABLE_
NAME='ACCESS$'
SQL_787830cec4402bdf SELECT TABLE_NAME FR SQL_PLAN_7hy1htv240ayzc127edb7 MANUAL-LOAD- YES YES
OM TEST_ENV.TB_TABLE FROM-CURSOR-
_LIST T WHERE TABLE_ CACHE
NAME='ACCESS$'
SQL> SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
--------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID drmkgq2ppg7kg, child number 1
-------------------------------------
SELECT TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 1475094007
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TB_TABLE_LIST | 1 | 18 | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----
- SQL plan baseline SQL_PLAN_7hy1htv240ayzc127edb7 used for this statement
已选择 47 行。
可以看到此时的执行计划就是刚刚加 HINTS 的 SQL 语句的执行计划了。
