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

阅读(2270) | 评论(0) | 转发(0) |