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去使用某个计划.
