【CBO Optimizer优化器】IX

ix_sel – Index selectivity 索引选择率是Oracle中CBO 基于成本优化器重要的参考指标，反应了符合谓词条件通过索引主导列访问表上数据行的比例。(ix_sel – Index selectivity. Fraction of the table rows accessed by the indexes leading column in order to meet the predicate supplied. (10053)。

注意仅仅leading column即索引的主导列用作计算ix_sel

举一个简单的计算ix_sel的例子：

SQL> create index ind_maclean on sh.sales( prod_id,CUST_ID,TIME_ID);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SH’,’SALES’,cascade=>true,method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10053 trace name context forever ,level 1;
Statement processed.
SQL> explain plan for select * from sh.sales where prod_id=13 and CUST_ID=987;

Explained.

BEGIN Single Table Cardinality Estimation
—————————————–
Column (#1): PROD_ID(NUMBER)
AvgLen: 4.00 NDV: 72 Nulls: 0 Density: 0.013889 Min: 13 Max: 148
Column (#2): CUST_ID(NUMBER)
AvgLen: 5.00 NDV: 5828 Nulls: 0 Density: 1.7159e-04 Min: 2 Max: 100989
Table: SALES Alias: SALES
Card: Original: 924076 Rounded: 2 Computed: 2.20 Non Adjusted: 2.20
—————————————–
END Single Table Cardinality Estimation
—————————————–
Access Path: TableScan
Cost: 414.20 Resp: 414.20 Degree: 0
Cost_io: 389.00 Cost_cpu: 215902675
Resp_io: 389.00 Resp_cpu: 215902675

www.askmac.cn
Access Path: index (RangeScan)
Index: IND_MACLEAN
resc_io: 5.00 resc_cpu: 37017
ix_sel: 2.3831e-06 ix_sel_with_filters: 2.3831e-06
Cost: 5.00 Resp: 5.00 Degree: 1

ix_sel= 1/ (72*5828)=2.3831e-06

对于 Equality predicates 且变量可见(硬绑定或绑定可窥视) IX_SEL=1 / (NDV1* NDV2*..)

同样变量可见情况下>、<开放范围 IX_SEL=(MAX- 代入的范围值) / (MAX-MIN)

而变量不可见(cursor_sharing=FORCE、_optim_peek_user_binds=false)的情况:

1、Equality predicates 等式谓词情况下，IX_SEL一般等于列的Density

2、对于> <大于、小于的开放范围谓词 ix_sel一般恒等于0.009，对于 (object_id>:i and object_id<:b;)的闭包则恒等于 0.0045

例如：

select count(*) from test where object_id>:i

Access Path: index (IndexOnly)
Index: TEST_IDX
resc_io: 3.00 resc_cpu: 160764
ix_sel: 0.009 ix_sel_with_filters: 0.009
Cost: 3.02 Resp: 3.02 Degree: 1
Best:: AccessPath: IndexRange Index: TEST_IDX
Cost: 3.02 Degree: 1 Resp: 3.02 Card: 3869.30 Bytes: 0

select count(*) from test where object_id>:i and object_id<:b

Access Path: index (IndexOnly)
Index: TEST_IDX
resc_io: 2.00 resc_cpu: 84043
ix_sel: 0.0045 ix_sel_with_filters: 0.0045
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange Index: TEST_IDX
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 193.47 Bytes: 0

ix_sel的 0.009和0.0045 都是写死在代码里的常数值，具体可以参考下表：

/* defaults */

/* Default selectivities are set low to

keep cost values low for future resource limiter use

keep cost values low for permutation cutoff in kko

Defaults are used for bind variables, general expressions and

unanalyzed tables, except for equality where defaults are not

needed for bind variables.

#define KKEDSREL 0.05 /* default selectivity for < <= > >= */

#define KKEDSEQ 0.01 /* default selectivity for = */

#define KKEDSNE 0.05 /* default selectivity for != */

#define KKEDSDF 0.05 /* default selectivity for all other ops */

#define KKEDSIRL 0.009 /* default selectivity for relation on indexed col */

#define KKEDSBRL 0.009 /* def sel for relation with bind var on index col*/

#define KKEDSIEQ 0.004 /* default selectivity for = on indexed col */

#define KKEDMBR 8 /* default multiblock read factor */

#define KKEDMBW 8 /* default multiblock write factor */

#define KKEDFNR 100.0 /* default – fixed table cardinality */

#define KKEDFRL 20 /* default – fixed table row length */

#define KKEDDNR 2000.0 /* default – remote table cardinality */

#define KKEDDRL 100 /* default – remote table avg row length */

#define KKEDDNB 100 /* default – default # of blocks */

#define KKEDDSC 13.0 /* default – default scan cost */

#define KKEDILV 1 /* default – default index levels */

#define KKEDILB 25 /* default – number of index leaf blocks */

#define KKEDLBK 1 /* default – number leaf blocks/key */

#define KKEDDBK 1 /* default – number of data blocks/key */

#define KKEDKEY 100 /* default – number of distinct keys */

#define KKEDCLF (KKEDDNB*8) /* default – clustering factor */

#define KKECRI 1.5 /* remote table access cost increase factor */

#define KKECFSC 1.0 /* fixed table scan cost */

#define KKECFNB 0 /* fixed table number of blocks */

#define KKECMXB 15 /* maximum byte length for normalization */

#define KKECBBS 256.0 /* base for byte sequence normalization */

#define KKECSPC ‘ ‘ /* space byte value */

#define KKECSPD 86400.0 /* seconds per day */

#define KKESROH 10.0 /* sort per row overhead in bytes */

#define KKESAUT 0.75 /* sort area utilization */

#define KKESROP 0.10 /* sort row overhead percent */

#define KKESRML 2.0 /* sort run multiple */

#define KKESTP 0x01 /* single table predicate */

#define KKETEQ 0x02 /* equi join */

#define KKETBCPJ 0x04 /* Cartesian product join */

#define KKESOK 0x08 /* input swap ok */

#define KKESWP 0x10 /* inputs swapped */

#define KKEEQP 0x20 /* equipartitioned */

#define KKELKNWC 0x01 /* LIKE no wild card */

#define KKELKTWC 0x02 /* LIKE trailing wild card */

#define KKELKEWC 0x04 /* LIKE embedded wild card */

#define KKELKLWC 0x08 /* LIKE leading wild card */

#define KKELKOWC 0x10 /* LIKE only wild card */

Comments

Ask_Maclean_liu_Oracle says

2013/04/29 at 23:34

alter system set “_optim_peek_user_binds”=false;Create table test as select * from dba_objects;Create index test_idx on test(object_id);Exec dbms_stats.gather_table_stats(USER,’test’,estimate_percent=>100,cascade=>true);alter session set events ‘trace[SQL_Costing] disk highest ‘;select count(*) from test where object_id>100; begin for i in 1000..10000 loop execute immediate ‘alter system flush shared_pool’; execute immediate ‘select count(*) from test where object_id>’|| i; end loop; end ; / 1=> 1 1000 => 0.988931 4000 => 0.955658 (x-1000)/y= 0.988931 (x-4000)/y= 0.955658 x-1000= 0.988931 y x-4000= 0.955658 y 3000= 0.033273y y=90163.1953836444 x=90165.17897394284 (90165-2001)/90163 0.977829 (90165-2002)/90163 resc_io: 194.00 resc_cpu: 18790559 ix_sel: 0.977818 ix_sel_with_filters: 0.977818 INGLE TABLE ACCESS PATH Single Table Cardinality Estimation for TEST[TEST] Column (#4): OBJECT_ID(NUMBER) AvgLen: 5 NDV: 89019 Nulls: 0 Density: 0.000011 Min: 2.000000 Max: 90165.000000 Table: TEST Alias: TEST Card: Original: 89019.000000 Rounded: 87044 Computed: 87044.38 Non Adjusted: 87044.38 Access Path: TableScan Cost: 398.85 Resp: 398.85 Degree: 0 Cost_io: 398.00 Cost_cpu: 33556485 Resp_io: 398.00 Resp_cpu: 33556485 Access Path: index (index (FFS)) Index: TEST_IDX resc_io: 55.00 resc_cpu: 16536154 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 55.42 Resp: 55.42 Degree: 1 Cost_io: 55.00 Cost_cpu: 16536154 Resp_io: 55.00 Resp_cpu: 16536154 Access Path: index (IndexOnly) Index: TEST_IDX resc_io: 194.00 resc_cpu: 18790559 ix_sel: 0.977818 ix_sel_with_filters: 0.977818 Cost: 194.48 Resp: 194.48 Degree: 1 Best:: AccessPath: IndexFFS Index: TEST_IDX Cost: 55.42 Degree: 1 Resp: 55.42 Card: 87044.38 Bytes: 0

回复
maclean says

2013/04/30 at 14:11

Column (#4): OBJECT_ID(NUMBER) AvgLen: 5.00 NDV: 77386 Nulls: 0 Density: 1.2922e-05 Min: 2 Max: 91353 Table: TEST Alias: TEST Card: Original: 77386 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 —————————————– END Single Table Cardinality Estimation —————————————– Access Path: TableScan Cost: 255.20 Resp: 255.20 Degree: 0 Cost_io: 251.00 Cost_cpu: 35977402 Resp_io: 251.00 Resp_cpu: 35977402 Access Path: index (index (FFS)) Index: TEST_IDX resc_io: 39.00 resc_cpu: 22119108 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 41.58 Resp: 41.58 Degree: 1 Cost_io: 39.00 Cost_cpu: 22119108 Resp_io: 39.00 Resp_cpu: 22119108 Access Path: index (AllEqRange) Index: TEST_IDX resc_io: 1.00 resc_cpu: 8171 ix_sel: 1.2922e-05 ix_sel_with_filters: 1.2922e-05 exec dbms_stats.gather_table_stats(user,’TEST’,cascade=>true, method_opt=>’FOR ALL COLUMNS SIZE 254′); Column (#4): OBJECT_ID(NUMBER) AvgLen: 5.00 NDV: 77386 Nulls: 0 Density: 1.2922e-05 Min: 2 Max: 91353 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255 Table: TEST Alias: TEST Card: Original: 77386 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 —————————————– END Single Table Cardinality Estimation —————————————– Access Path: TableScan Cost: 255.20 Resp: 255.20 Degree: 0 Cost_io: 251.00 Cost_cpu: 35977402 Resp_io: 251.00 Resp_cpu: 35977402 Access Path: index (index (FFS)) Index: TEST_IDX resc_io: 39.00 resc_cpu: 22119108 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 41.58 Resp: 41.58 Degree: 1 Cost_io: 39.00 Cost_cpu: 22119108 Resp_io: 39.00 Resp_cpu: 22119108 Access Path: index (AllEqRange) Index: TEST_IDX resc_io: 1.00 resc_cpu: 8171 ix_sel: 1.2922e-05 ix_sel_with_filters: 1.2922e-05 Cost: 1.00 Resp: 1.00 Degree: 1

回复
Ask_Maclean_liu_Oracle says

2013/08/08 at 16:57

drop table test; alter session set “_optim_peek_user_binds”=false;create table test as select * from dba_objects;create index test_idx on test(object_id);create index test_idx2 on test(owner);exec dbms_stats.gather_table_stats(user,’test’,cascade=>true,no_invalidate=>false);ALTER SESSION SET EVENTS ‘trace[rdbms.SQL_Optimizer.*]’;variable v varchar2(200);exec :v:=’MACLEAN’;select count(*) from test where owner>:v; ALTER SESSION SET EVENTS ‘trace[rdbms.SQL_Optimizer.*] off’; oradebug setmypid oradebug tracefile_name;

回复

【CBO Optimizer优化器】IX_SEL索引选择率

Comments

Trackbacks

Comment 取消回复