稳定ORACLE的执行计划

 很多时候可能我们都希望CBO能够帮我们生成正确、高效的执行计划,但是很多时候事实并非如此,可能因为各种各样的原因(如,统计信息不正确或者CBO天生的缺陷等)都会导致生成的执行计划特别的低效。之前的一家公司有一台专门用于批量做数据校验清洗的数据库,每次校验清洗完成数据就会清理掉,统计信息经常会发生较大的变更,之前跑得好好的SQL,可能有时候跑5-6个小时都跑不完了,这时候查看执行计划,发现不正确的统计信息导致了执行计划的变更。

 这时候我们就希望数据库中运行的SQL都能有正确、稳定的执行计划,在10g开始的版本中可以通过SQL Profile来稳定执行计划或者在不改变SQL的情况下修改执行计划。11g开始可以使用偏主动的稳定执行计划的手段——SPM(SQL PLAN MANAGEMENT),保证只有被验证过的执行计划才会被启用。

SQL Profile

 SQL Profile是包含特定于SQL语句的辅助统计信息的数据库对象,可以改进优化器基数估计,从而选择更好的执行计划。
 当选择执行计划时优化器会考虑以下信息:

  1. SQL Profile提供的辅助统计信息
  2. 当时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的例子:

1
2
3
4
5
6
7
8
9
10
11
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。

1
2
3
4
5
6
7
8
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相应的参数值。

1
2
3
4
5
6
7
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

1
2
3
4
5
6
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,并查看其执行计划。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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的执行计划。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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语句的执行计划,可以看到此时已经是做的索引范围扫描了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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来实现的,他们之间的区别如下:

  1. SQL Plan BaseLine是一种偏主动的机制,可以在性能问题出现之前就创建SQL基线,避免优化器在未来某个时刻选择次优的执行计划。而SQL Profile只能等到发现SQL的性能问题时,调用SQL Tuning Advisor来对有问题的SQL进行调整。
  2. 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函数可以查看对应的执行计划。

1
2
3
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库的基本流程:

  1. 使用DBMS_SPM.CREATE_STGTAB_BASELINE创建中间表。

    1
    2
    3
    4
    5
    BEGIN
    DBMS_SPM.CREATE_STGTAB_BASELINE (
    table_name => 'stage1');
    END;
    /
  2. 将SQL Plan Baseline打包到刚刚创建的中间表stage1

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    DECLARE
    v_plan_cnt NUMBER;
    BEGIN
    v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
    table_name => 'stage1'
    , enabled => 'yes'
    , creator => 'spm'
    );
    END;
    /
  3. 将中间表传输到目标数据库中,可以通过数据泵等手段。

  4. 在目标库中,将中间表的数据解压。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    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:

  1. 首先通过DBA_SQL_PLAN_BASELINES获取到对应的SQL_HANDLE
  2. 执行删除操作
    1
    SQL> EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle => 'SQL_b6b0d1c71cd1807b');

实例1——自动捕获

  1. 修改optimizer_capture_sql_plan_baselines参数启用自动捕获

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    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
  2. 第一次执行SQL,可以看到在DBA_SQL_PLAN_BASELINES中并未查到相应的SQL基线,再次执行才能查询到。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    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%';
    未选定行
  3. 再次执行上面的SQL。查询DBA_SQL_PLAN_BASELINES,对应的SQL Plan Baseline的ACCEPTED和ENABLE的值均为YES。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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$'
  4. 在表上创建一个TABLE_NAME列的B-TREE索引,然后再次执行上面的SQL语句,通过DBA_SQL_PLAN_BASELINES查询,此时多了一条SQL Plan Baseline,ENABLE=YES,ACCEPTED=NO

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    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$'
  5. 在TABLE_NAME列创建索引之后,根据此列查询,应该是走INDEX RANGE SCAN,但是事实是怎么样呢?看下该语句的执行计划。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    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的执行计划。

  1. 但是实际上此时应该是走索引范围扫描才是最高效的,即”SQL_PLAN_7hy1htv240ayz01f095c0”这个SQL Plan Baseline,如何启用改baseline呢?首先创建一个evole任务,并执行该任务。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    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
  2. 执行完成之后,通过REPORT_EVOLVE_TASK可以查看到相应的任务报告。Findings部分告诉我们发现了一个比基线效率更高的执行计划,并提供了建议方案。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    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$')
    ---------------------------------------------------------------------------------------------
  3. 可以通过上面报告中建议的方式来接受这个计划,也可以使用IMPLEMENT_EVOLVE_TASK函数。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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$'
  4. 最后我们再来看下执行计划

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    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中加载执行计划。

  1. 首先对前面例子生成的内容进行清理。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    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;
    系统已更改。
  2. 执行SQL并查看执行计划

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    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 行。
  3. 因为此时自动捕获是关闭的,所以不管执行多少次SQL,都没有对应的SQL Plan Baseline。可以手动的将对应的执行计划加入到SQL Plan Baseline中。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    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$'
  4. 在TABLE_NAME列上创建索引,然后再次执行SQL,并查看执行计划。可以看到虽然在TABLE_NAME这个列上存在索引,但是已经存在对应SQL语句的一个走全表扫描的执行基线,所以此时依旧走的全表扫描,使用的是刚刚加载进去的’SQL_PLAN_7hy1htv240ayzc127edb7’这个SQL Plan Baseline。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    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 行。
  5. 这个时候我们再来查看下DBA_SQL_PLAN_BASELINES这个数据字典,可以看到SPM帮我们自动捕获了一条ENABLE为YES,ACCEPTED为NO的SQL Plan Baseline。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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$'
  6. 将’SQL_PLAN_7hy1htv240ayz01f095c0’的ACCEPTED的值改为YES。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    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$'
  7. 再次执行SQL,此时使用了’SQL_PLAN_7hy1htv240ayz01f095c0’这个SQL Plan Baseline了。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    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 行。
  8. 前面的SQL都是没有HINTS的,有时候我们需加入HINTS对SQL的执行计划进行调整。如何用加入了hints的SQL执行计划来替换之前的SQL,生成新的SQL Plan Baseline呢?首先删除之前做全表扫描的SQL Plan Baseline,然后加入一个FULL的hints来执行SQL,此时SQL_ID和之前是不同的。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    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

  1. 此时的DBA_SQL_PLAN_BASELINES依然之后刚刚那一条记录。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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$'
  2. 现在用上述改写后的SQL的新的执行计划来手工生成SQL Plan Baseline

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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$'
  3. 由于此时所对应的SQL Plan Baseline的ENABLED和ACCEPTED的值都为YES,我们将之前的走索引范围扫描的ENABLED属性改为NO,然后执行SQL语句。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    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语句的执行计划了。