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.askmaclean.com
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 */
Leave a Reply