稳定ORACLE的执行计划-低调大师

稳定ORACLE的执行计划

2019-04-19 801

原文发表在:http://l4j.cc/2019/04/19/oracle-sql-plan-management/

很多时候可能我们都希望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语句的执行计划了。

微信关注我们

原文链接：https://my.oschina.net/u/3255159/blog/3039553

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

承载每天10万级任务的数据调度系统的架构是如何设计的

在对调度系统架构说明之前，我们先来认识一下调度系统常用的名词 1.名词解释 DAG：全称Directed Acyclic Graph，简称DAG。工作流中的Task任务以有向无环图的形式组装起来，从入度为零的节点进行拓扑遍历，直到无后继节点为止。举例如下图：流程定义：通过拖拽任务节点并建立任务节点的关联所形成的可视化DAG 流程实例：流程实例是流程定义的实例化，可以通过手动启动或定时调度生成任务实例：任务实例是流程定义中任务节点的实例化，标识着具体的任务执行状态任务类型：目前支持有SHELL、SQL、SUB_PROCESS、PROCEDURE、MR、SPARK、PYTHON、DEPENDENT，同时计划支持动态插件扩展，注意：其中子 SUB_PROCESS 也是一个单独的流程定义，是可以单独启动执行的调度方式：系统支持基于cron表达式的定时调度和手动调度。命令类型支持：启动工作流、从当前节点开始执行、恢复被容错的工作流、恢复暂停流程、从失败节点开始执行、补数、调度、重跑、暂停、停止、恢复等待线程。其中恢复被容错的工作流和恢复等待线程两种命令类型是由调度内部控制使...

2019-04-19

673

再谈使用开源软件搭建数据分析平台

三年前，我写了这篇博客使用开源软件快速搭建数据分析平台，当时收到了许多的反馈，有50个点赞和300+的收藏。到现在我还能收到一些关于dataplay2的问题。在过去的三年，开源社区和新技术的发展可谓日新月异，我希望试试利用最新的技术来帮助没有数据科学背景的人也能够轻松的进行数据分析和预测，于是就有了dataplay3 。架构老规矩，先上架构图：为了构建一个最简单的开箱即用的数据分析平台，我使用了如下的技术栈：服务器端： sanic基于Python3的web服务器 pandasPython上最流行的数据分析库 auto-sklearn基于sklearn的自动机器学习库 prophet非死不可开源的时间序列分析库 pandassql能够在Panda数据框上运行SQL的库 gunicorn基于python的WSGI HTTP服务器客户端： React前端框架 Ant Design Pro蚂蚁开源的企业级应用框架套装 Ant Design UmiJS Dva BizCharts基于G2和React的可视化库 G2The Grammar of Graphics in JavaScri...

2019-04-20

757

发表评论

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。