远邦昨天给我出了一道题，里面涉及到了用10053分析执行计划，很有趣，我这里paste一下分析过程，与大家共享。
远邦的题目是这样的：
create table tb0624 (type number,ts timestamp) pctfree 90;
begin
for i in 1..10000 loop
insert into tb0624 values (1,sysdate);
insert into tb0624 values (3,sysdate);
end loop;
commit;
end;
/
begin
for i in 1..10 loop
insert into tb0624 values (2,sysdate);
end loop;
commit;
end;
/
SELECT TYPE,COUNT(*) FROM TB0624 GROUP BY TYPE;
TYPE COUNT(*)
---------- ----------
1 10000
3 10000
2 10
exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS')
-------检查列是否有柱状图
SET LINESIZE 200 PAGESIZE 100
SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TB0624';
SELECT * FROM USER_HISTOGRAMS WHERE TABLE_NAME='TB0624';
SELECT TABLE_NAME,/* LOW_VALUE,HIGH_VALUE , */ COLUMN_NAME,NUM_NULLS,DENSITY,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB0624';
SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME='TB0624';
SELECT NUM_ROWS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS FROM USER_INDEXES WHERE INDEX_NAME='IDX_TYPE';
-------开始分析索引
create index idx_type on tb0624(type);
-------再次检查列是否有柱状图
SET LINESIZE 200 PAGESIZE 100
SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TB0624';
SELECT * FROM USER_HISTOGRAMS WHERE TABLE_NAME='TB0624';
SELECT TABLE_NAME,/* LOW_VALUE,HIGH_VALUE , */ COLUMN_NAME,NUM_NULLS,DENSITY,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB0624';
SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME='TB0624';
SELECT NUM_ROWS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS FROM USER_INDEXES WHERE INDEX_NAME='IDX_TYPE';---因为是
---开始测试
SQL> set autotrace on
SQL> select * from tb0624 where type=2;
Plan hash value: 4077598990
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 353 | 9178 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB0624 | 353 | 9178 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TYPE | 141 | | 1 (0)| 00:00:01 |
为什么oracle这里自己知道走索引？
10053显示索引选择率为0.004而不是1/3?
按照上述测试过程原封不动的做一遍，然后分析一下10053产生的trace文件，我这里称这个文件为trace文件1，因为后续我还会产生两个10053的trace文件2和trace文件3。
Trace文件1的部分内容：
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB0624 Alias: TB0624
#Rows: 20010 #Blks: 438 AvgRowLen: 14.00
Index Stats::
Index: IDX_TYPE
LVLS: 1 #LB: 40 #DK:
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): TYPE(NUMBER) NO STATISTICS (using defaults)
AvgLen: 22.00 NDV: 625 Nulls: 0 Density: 0.0015992
Table: TB0624 Alias: TB0624
Card: Original: 20010 Rounded: 200 Computed: 200.10 Non Adjusted: 200.10
Access Path: TableScan
Cost: 100.08 Resp: 100.08 Degree: 0
Cost_io: 98.00 Cost_cpu: 7125191
Resp_io: 98.00 Resp_cpu: 7125191
Access Path: index (AllEqGuess)
Index: IDX_TYPE
resc_io: 17.00 resc_cpu: 151884
ix_sel: 0.004 ix_sel_with_filters: 0.004
Cost: 17.04 Resp: 17.04 Degree: 1
Best:: AccessPath: IndexRange Index: IDX_TYPE
Cost: 17.04 Degree: 1 Resp: 17.04 Card: 200.10 Bytes: 0
这里index range scan的cost是17.04，table scan的cost是100.08，idx_type的DK是3，ix_sel确实是0.004。
其实远邦的问题上述trace文件已经给出答案了，就是我红字标明的部分，即：
上述测试案例里ix_sel=0.004而不是1/3，这是因为在type这一列上没有统计信息，所以oracle在这里用到了默认值来计算ix_sel，而不是用index上的DK来计算ix_sel（什么情况下也不会用DK来计算ix_sel）。换句话说，这里即使你用条件type=2（或者3）来查询的话，oracle这个地方也会用index range scan。
远邦，这个默认值不是固定的，感觉这里这个默认值取决于Blks，我这里证明给你看。
将上述表drop掉，插入数据的语句更改成
begin
for i in 1..10000 loop
insert into tb0624 values (1,sysdate);
insert into tb0624 values (3,sysdate);
insert into tb0624 values (4,sysdate);
end loop;
commit;
end;
/
begin
for i in 1..10 loop
insert into tb0624 values (2,sysdate);
end loop;
commit;
end;
/
然后再如法炮制分析10053产生的trace文件2：
Table Stats::
Table: TB0624 Alias: TB0624
#Rows: 30681 #Blks: 628 AvgRowLen: 14.00
Index Stats::
Index: IDX_TYPE
LVLS: 1 #LB: 59 #DK:
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): TYPE(NUMBER) NO STATISTICS (using defaults)
AvgLen: 22.00 NDV: 959 Nulls: 0 Density: 0.001043
Table: TB0624 Alias: TB0624
Card: Original: 30681 Rounded: 307 Computed: 306.81 Non Adjusted: 306.81
Access Path: TableScan
Cost: 142.09 Resp: 142.09 Degree: 0
Cost_io: 139.00 Cost_cpu: 10614604
Resp_io: 139.00 Resp_cpu: 10614604
Access Path: index (AllEqGuess)
Index: IDX_TYPE
resc_io: 22.00 resc_cpu: 203032
ix_sel: 0.0040894 ix_sel_with_filters: 0.0040894
Cost: 22.06 Resp: 22.06 Degree: 1
Best:: AccessPath: IndexRange Index: IDX_TYPE
Cost: 22.06 Degree: 1 Resp: 22.06 Card: 306.81 Bytes: 0
看到了吗，这里的ix_sel已经变成了0.0040894。
现在我们来让type这一列上产生统计信息：
将上述表drop掉，全部重来一遍，只不过在create index idx_type on tb0624(type)之后再执行一下
exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1')
或
exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE REPEAT')
上述两个之一执行完后，type这一列上已经有统计信息了：
SQL> SELECT TABLE_NAME,/* LOW_VALUE,HIGH_VALUE , */ COLUMN_NAME,NUM_NULLS,DENSITY,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB0624';
TABLE_NAME COLUMN_NAME NUM_NULLS DENSITY NUM_DISTINCT HISTOGRAM
------------------------------ ------------------------------ ---------- ---------- ------------ ---------------
TB0624 TYPE 0 0.25 4 NONE
TB0624 TS NONE
而且这里居然也产生了histogram信息，这和文档里描述的不一致，这里我的本意是不想产生histogram的：
SQL> SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TB0624';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ -------------------------------------------------------------------------------- --------------- -------------- --------------------------------------------------------------------------------
TB0624 TYPE 0 1
TB0624 TYPE 1 4
再次产生10053的trace文件3，发现现在oracle已经走全表扫描了，我们来看一下这个trace文件的内容：
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB0624 Alias: TB0624
#Rows: 29590 #Blks: 628 AvgRowLen: 14.00
Index Stats::
Index: IDX_TYPE
LVLS: 1 #LB: 59 #DK:
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): TYPE(NUMBER)
AvgLen: 3.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 1 Max: 4
Table: TB0624 Alias: TB0624
Card: Original: 29590 Rounded: 7398 Computed: 7397.50 Non Adjusted: 7397.50
Access Path: TableScan
Cost: 142.07 Resp: 142.07 Degree: 0
Cost_io: 139.00 Cost_cpu: 10538224
Resp_io: 139.00 Resp_cpu: 10538224
Access Path: index (AllEqRange)
Index: IDX_TYPE
resc_io: 466.00 resc_cpu: 6095551
ix_sel: 0.25 ix_sel_with_filters: 0.25
Cost: 467.78 Resp: 467.78 Degree: 1
Best:: AccessPath: TableScan
Cost: 142.07 Degree: 1 Resp: 142.07 Card: 7397.50 Bytes: 0
Leave a comment