04

跟踪：调整索引后分析

4.1发现很多涉及调整表的SQL跑的异常缓慢

新建11和41号索引后，发现大量涉及B表查询的SQL使用上了11和41号的索引，但执行却异常缓慢，结合业务逻辑和执行计划判断其应该使用其他更合适的已有索引。怀疑是统计信息不准确报的错误，于是收集表统计信息，执行如下SQL：

exec dbms_stats.gather_table_stats(ownname => ' &OWNER ',tabname => ' B表 ',estimate_percent => '0.001',degree=>8,cascade => true,no_invalidate=>false);

确定成功收集统计信息后，发现还是没有效果，在当时操作过程中认为收集统计信息后，oracle没有走上正确的索引就是成本优化器判断错误，于是决定手工绑定走错索引的sql，这也是一般的处理思路，如下示：

成功绑定后，通过以下SQL查杀当前跑错的sql：

--查询索引前缀字段统计信息

select num_distinct,density, histogram,last_analyzed,num_buckets from dba_tab_columns where table_name = 'table_name' and column_name = 'col_name'

而这个表有36亿行纪录，eventname的唯一值也只有几十个，基数不可能这么少，密度也不可能这么小，eventname字段的密度很低，也就是对应的选择度高，适合做索引，所以041索引创建后，很多原先跑其他索引很优的SQL也跑这个索引上了。

于是手动修改密度值

exec dbms_stats.set_column_stats(ownname =>'&OWNER',tabname => '&TABLENAME ',colname => 'eventname',density => 0.01);

修改密度值后，sql执行正常了，但此时发现其他大表也存在密度不准确的问题

4.2 密度思考

Query Optimizer: What is Density? (文档 ID 43041.1)

Density is a statistic used by the Cost Based Optimizerto give selectivityestimates for columns where better information is unavailable (i.e. fromhistograms etc.).

即当直方图不可用的时候，CBO优化器会使用密度来估计列的选择率，经过一翻测试得出以下结论：收集直方图信息才会改变密度，不收集则不会改变密度，Density的出现是为了分析高频率出现的值的影响，没有histograms信息的时候，DENSITY永远等于1/NUM_DISTINCT，当我们统计收集了histograms之后，DENSITY就会发生改变。在本次调整操作中，只指定cascade => true方式收集，这个字段密度值没有改变，没有选择收集字段直方图信息，推荐以后使用以下sql收集统计信息（指定自动收集直方图信息）：

exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T',estimate_percent => '0.001',degree=>4,method_opt=>'for all columns size auto',cascade => true, no_invalidate=>false);

或针对特定字段需要收集直方图信息：

exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T',estimate_percent => '0.001',degree=>4,method_opt=>'for columns size 254 OBJECT_ID',cascade => true ,no_invalidate=>false);

如果表是组合分区表，在创建索引之后，需要加上granularity => 'ALL'或'APPROX_GLOBAL AND PARTITION'来收集统计信息：

exec DBMS_STATS.GATHER_TABLE_STATS(ownname =>'ROBINSON', tabname =>'T_SUB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>'for all columns size repeat',degree=> DBMS_STATS.AUTO_DEGREE, granularity =>'ALL',cascade=>TRUE, ,no_invalidate=>false);

此处需要了解一些oracle执行计划基数(cardinality)计算方式：

1.单列无直方图的计算方式

DENSITY=1/NDV --Density值存储在数据字典表中，参与基数计算

Sel= DENSITY*非比例

或Sel=(1/num_distinct)*(num_rows-num_s)/num_rows

Computed Cardinality= Card Original * Sel

2.单列有直方图的计算方式：

频率直方图：

Card :=num_rows*(Sum(Bucketsize)/(num_rows-num_s)) --等值查询

Card :=num_rows*(Sum(Bucketsize)/(2*num_rows-num_s)) –不等值查询

Bucketsize: 桶内的rowcount dba_tab_histograms.endpoint_value --唯一值存放在一个桶里的记录数量

Density = 1 / ( 2 * Num_Rows * _Adjust) --Density值存储在数据字典表中，没有参与基数计算

_Adjust=(Num_Rows-Num_s)/Num_Rows

高度均衡直方图：

1）popular value值基数计算方式: --Density值存储在数据字典表中，没有参与基数计算

Comp_Card = Orig_Card * Sel Sel = (该Popular值的桶数 /总的桶数) * 非比例

非比例=(Orig_Card - Ns) / Orig_Card

2）非popular value值基数计算方式: --Density值存储在数据字典表中，参与基数计算

Card =num_rows * New Density * (num_rows-num_s)/num_rows

New Density= (Buckets_total - Buckets_all_popular_values) / Buckets_total

/ (NDV - popular_values.COUNT) --10.2.0.4及以上参与基数计算

OldDensity=Sum(NP.COUNT(i)*NP.COUNT(i))/((NUM_ROWS-NUM_S)*SUM(NP.COUNT(i))) --OldDensity值存储在数据字典表中，10.2.0.4以下没有参与基数计算

popular_values.COUNT表示popular_values的个数，NP.COUNT(i)表示的是每个nonpopular value在表中的记录数

在计算Cardinality的时候，ORACLE首先会利用到DENSITY。如果手工修改了NUM_DISTINCT那么DENSITY也会跟着变化，但是反过来，如果修改了DENSITY，NUM_DISTINCT就不会改变。

注：优化器最多会生成数千个执行计划，这些成本计算有时是很头痛的事情，且oracle12c直方图上限不再是254个height balance桶。

4.3继续跟踪

客户在第二天报还是有异常使用索引的SQL，于是通过10053事件，发现如下问题：

从10053跟踪文件中可以清楚看到，新建的11、41号索引没有统计信息，进一步通过dba_ind_statistics表查看索引统计信息，发现17号的索引分区有收集，而16号的索引分区没收集统计信息，收集这个索引分区的统计信息之后，异常SQL用上了正确的索引。

exec dbms_stats.gather_index_stats(ownname=>'&owner',indname =>'index_name',partname =>'index_part_name',estimate_percent =>'0.01', method_opt=>'for all columns size auto',degree => 4, no_invalidate=>false);

到这里才弄清楚事情原因，在新建的11、41号索引后虽然已经执行统计信息收集，但因收集的方式不对，造成基数和密度不正确，导致很多不使用11、41号索引的SQL也使用这个索引而造成的故障，因此对于大表分区，在统计信息收集后，还需要进一步通过dba_ind_statistics等视图查看索引及索引分区的统计信息是否存在和相对准确，确保表和分区的统计信息都准确后，才考虑使用绑定执行计划的方法绑定异常SQL，使其用使用正确的索引。

05

总结：问题总结

1.在手工重新收集完统计信息后，还需要检查条件字段唯一值数量、密度和直方图信息，确保表字段统计信息的正确性，以判断sql走上正确的索引。

2.我们知道创建索引的时候会自动收集统计信息，但在创建大表索引之后，仍需要详细检查新建索引是否有统计信息，特别是分区索引，可能存在跨日时间部分分区统计信息不全的情况，导致成本错误，使其他sql走错索引。

3.遇上极端的问题不要轻易放弃和回退，需要继续思考可能原因，不能主观判断，一定要有根据，对于成本计算，10053可以辅助分析问题，不能主观认为执行完统计信息收集就认为统计信息是准确的，需要考虑使用一些方法来查询验证。

来都来了，走啥走，留个言呗~

IT大咖说 | 关于版权

由“IT大咖说（ID：itdakashuo）”原创的文章，转载时请注明作者、出处及微信公众号。投稿、约稿、转载请加微信：ITDKS10（备注：投稿），茉莉小姐姐会及时与您联系！

感谢您对IT大咖说的热心支持！

相关推荐

推荐文章

• 敢问出路在何方？——给资深程序员的一封推荐信

• 当“码农”遇上 Tony 老师：程序员理发时都在想些什么？

• 一次分区大表索引整改的案例分析(上)

点击【阅读原文】更多IT技术圈干货等你挖掘

阅读原文