日前在客户现场对一些sp进程程序调优，中间碰到不少问题
列举其一：
select item_code from inv_master where NVL (COST_FLAG,'N') = 'Y';
其中在表inv_master的quota_hours字段创建函数索引
create index idx_inv_master_02 on inv_master(nvl(COST_FLAG,'N') online tablespace idxdata;
select count(*) from inv_master;
————
636687
select count(*) from inv_master where NVL (COST_FLAG,'N') = 'Y';
————
123
根据单表选择率这里语句
select item_code from inv_master where NVL (QUOTA_HOURS,'N') = 'Y';
应该会走indx_inv_master_02这个索引按照 range scan方式扫描，但是在客户的库中还是会全表扫描，由于这个sql在sp中要被反复执行3万多次，这个是一个非常严重的IO问题，
在现场首先做了一次
analyze table INV_MASTER compute statistics;
analyze index IDX_INV_MASTER_02 compute statistics;
sql还是不走索引，没办法只好做一次10053
——————————————————————————————————–
oradebug setmypid
oradebug unlimit
oradebug event 10053 trace name context forever,level 10
select item_code from inv_master where NVL (QUOTA_HOURS,'N') = 'Y';
oradebug event 10053 trace name context off;
oradebug tracefile_name
TRC文件底部显示:
Table Stats::
Table: INV_MASTER Alias: INV_MASTER
#Rows: 63487 #Blks: 3268 AvgRowLen: 364.00
Index Stats::
Index: IDX_INV_MASTER_BFLAG Col#: 68
LVLS: 1 #LB: 70 #DK: 2 LB/K: 35.00 DB/K: 1451.00 CLUF: 2903.00
Index: IDX_INV_MASTER_COOP_FLAG Col#: 169
LVLS: 1 #LB: 116 #DK: 2 LB/K: 58.00 DB/K: 2229.00 CLUF: 4458.00
Index: IDX_INV_MASTER_COST_FLAG Col#: 167
LVLS: 1 #LB: 116 #DK: 2 LB/K: 58.00 DB/K: 1618.00 CLUF: 3236.00
Index: IDX_INV_MASTER_FLAG_FC Col#: 168 167
LVLS: 1 #LB: 133 #DK: 4 LB/K: 33.00 DB/K: 1098.00 CLUF: 4395.00
Index: IDX_INV_MASTER_FLAG_PHFL Col#: 168
LVLS: 1 #LB: 116 #DK: 3 LB/K: 38.00 DB/K: 1461.00 CLUF: 4384.00
Index: PK_INV_MASTER Col#: 3
LVLS: 1 #LB: 236 #DK: 63487 LB/K: 1.00 DB/K: 1.00 CLUF: 28932.00
***************************************
SINGLE TABLE ACCESS PATH
—————————————–
BEGIN Single Table Cardinality Estimation
—————————————–
Column (#167): SYS_NC00167$(NVARCHAR2)
AvgLen: 1.00 NDV: 2 Nulls: 0 Density: 0.5
Column (#56): COST_FLAG(NVARCHAR2)
AvgLen: 1.00 NDV: 2 Nulls: 16 Density: 0.5
Table: INV_MASTER Alias: INV_MASTER
Card: Original: 63487 Rounded: 31751 Computed: 31751.50 Non Adjusted: 31751.50
—————————————–
END Single Table Cardinality Estimation
—————————————–
Access Path: TableScan
Cost: 725.29 Resp: 725.29 Degree: 0
Cost_io: 717.00 Cost_cpu: 107074906
Resp_io: 717.00 Resp_cpu: 107074906
kkofmx: index filter:NVL("INV_MASTER"."COST_FLAG",U'N')=U'N'
Access Path: index (AllEqRange)
Index: IDX_INV_MASTER_COST_FLAG
resc_io: 1678.00 resc_cpu: 57991026
ix_sel: 0.50013 ix_sel_with_filters: 0.50013
Cost: 1682.49 Resp: 1682.49 Degree: 1
Access Path: index (FullScan)
Index: IDX_INV_MASTER_FLAG_FC
resc_io: 2333.00 resc_cpu: 69001720
ix_sel: 1 ix_sel_with_filters: 0.50013
Cost: 2338.69 Resp: 2338.69 Degree: 1
CBO在选择执行计划时候是根据统计信息来选择的，在这里可以发现统计信息中走这个索引的消耗为1682.49，走全表扫才717的消耗，
可以看出这里的索引统计信息肯定有问题，可以从dba_segements可以计算出索引idx_inv_master_cost_flag总的块为10多个，和这里明显不符，重新收集统计信息后，全表扫改为INDEX RANGE SCAN
over.
exec dbms_stats.gather_table_stats('RS10','INV_MASTER');
