Oracle SQL Plan Baseline的实现方式

                          Oracle SQL Plan Baseline的实现方式

     SQL Plan Baseline Management(以下简称SPM)的实现方式一直存在争议,主要争论的
有两种,一种论点认为是不是通过sql profile一样的原理,内部也是通过hint的方式来实现; 
另一种论述是CBO在选择执行计划的时候,如果生成的多个计划中,其中有一个计划的签名如果匹
配,那就使用该计划,如果没有,则使用CBO认为最好的执行计划.

  创建一个表,并且创建一个SPM.

SQL> create table t1
  as select level id from dual connect by level<=100000;

Table created.

SQL> create index ix_id on t1(id);  

Index created.

执行一个查询,并且把计划加到SPM中

SQL> select * from t1 where id=10;
ID
----------
10

SQL>

declare
 i int;
  begin
     i:=dbms_SPM.load_plans_from_cursor_cache(sql_id=>'cndym9jkpxsu6',
                                           plan_hash_value=>531999125
                                          );
        end;

SQL>  select * from t1 where id=11;

ID
----------
11

再次执行查询,发现SPM已经使用了

SQL> set autotrace on;

SQL>  select * from t1 where id=11;
ID
----------
11

Execution Plan
----------------------------------------------------------
Plan hash value: 531999125
--------------------------------------------------------------------------
| Id  | Operation | Name  | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT | |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IX_ID |     1 |    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=11)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline "SQL_PLAN_ck4cntcrzwn0he204a75d" used for this statement

我们使用10053事件来分析一个CBO是怎么判断的

SQL> alter session set events '10053 trace name context forever, level 8';

Session altered.

SQL> select * from t1 where id=11;

ID
----------
11

Registered qb: SEL$1 0x60369898 (PARSER)

--------------------
QUERY BLOCK SIGNATURE
--------------------- 
signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=91981
 hint_alias="T1"@"SEL$1"
SPM: statement found in SMB

oracle在进行优化之前进行签名查找,发现该语句在SPM中已经存在了

    Access Path: TableScan
    Cost:  418.310231  Resp: 418.310231  Degree: 0
      Cost_io: 413.000000  Cost_cpu: 210856739
      Resp_io: 413.000000  Resp_cpu: 210856739
  Access Path: index (index (FFS))
    Index: IX_ID
    resc_io: 606.000000  resc_cpu: 185887933
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  610.681415  Resp: 610.681415  Degree: 1
      Cost_io: 606.000000  Cost_cpu: 185887933
      Resp_io: 606.000000  Resp_cpu: 185887933
 ****** Costing Index IX_ID
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  Access Path: index (AllEqRange)
    Index: IX_ID
    resc_io: 3.000000  resc_cpu: 21564
    ix_sel: 9.9984e-07  ix_sel_with_filters: 9.9984e-07 
    Cost: 3.000543  Resp: 3.000543  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IX_ID
         Cost: 3.000543  Degree: 1  Resp: 3.000543  Card: 1.000000  Bytes: 0.000000

优化器会检查各种计划,最后认为IndexRange的方式为最好的计划

SPM: finding a match for the generated plan, planId = 3791955805
SPM: generated plan found in the plan baseline, planId = 3791955805
SPM: generated plan successfully matched, planId = 3791955805
Starting SQL statement dump

user_id=8 user_name=SYSTEM module=SQL*Plus action=
sql_id=cndym9jkpxsu6 plan_hash_value=531999125 problem_type=3

CBO找到在这些plan_hash_value中找到一个和SPM中相匹配的,最后使用的
计划就是和SPM能够进行匹配的那个计划

如果我们现在删除ix_id这个索引,那么这个sql就只能走全表扫描了,因为签名已经
不匹配所以SPM中没有合适的计划了,于是CBO自行决定执行计划

Registered qb: SEL$1 0x3cbd98b0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=91981 hint_alias="T1"@"SEL$1"

SPM: statement found in SMB

CBO只找到了一个执行计划

  Access Path: TableScan
    Cost:  418.310231  Resp: 418.310231  Degree: 0
      Cost_io: 413.000000  Cost_cpu: 210856739
      Resp_io: 413.000000  Resp_cpu: 210856739
  Best:: AccessPath: TableScan
         Cost: 418.310231  Degree: 1  Resp: 418.310231  Card: 16.723411  Bytes: 0.000000

但是已经不能使用索引扫描的执行计划了

SPM: planId in plan baseline = 3791955805, planId of reproduced plan = 3688435342
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : SYSTEM
  plan_baseline signature  : 14488525076632064016
  plan_baseline plan_id    : 3791955805
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS 
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('12.1.0.2') 
    hint num  3 len 22 text: DB_VERSION('12.1.0.2') 
    hint num  4 len  8 text: ALL_ROWS 
    hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1") 
    hint num  6 len 40 text: INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."ID")) 
SPM: generated non-matching plan:

总结:使用SPM的时候,如果有签名匹配的话,就是使用CBO中生成的对应计划,如果签名没有和CBO中
任意一个计划匹配,那么就由CBO自行决定执行计划.而SPM不能强制CBO去使用某个计划.