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

IT大咖说

发布于 2019-05-15 10:50:28

6020

发布于 2019-05-15 10:50:28

文章被收录于专栏：IT大咖说IT大咖说

跟踪：调整索引后分析

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 selectivity estimates 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*非NULL比例或Sel=(1/num_distinct)*(num_rows-num_nulls)/num_rowsComputed Cardinality= Card Original * Sel2.单列有直方图的计算方式：频率直方图：Card :=num_rows*(Sum(Bucketsize)/(num_rows-num_nulls)) --等值查询Card :=num_rows*(Sum(Bucketsize)/(2*num_rows-num_nulls)) –不等值查询Bucketsize: 桶内的rowcount dba_tab_histograms.endpoint_value --唯一值存放在一个桶里的记录数量Density = 1 / ( 2 * Num_Rows * Null_Adjust) --Density值存储在数据字典表中，没有参与基数计算Null_Adjust=(Num_Rows-Num_Nulls)/Num_Rows高度均衡直方图：1）popular value值基数计算方式: --Density值存储在数据字典表中，没有参与基数计算Comp_Card = Orig_Card * Sel Sel = (该Popular值的桶数 /总的桶数) * 非NULL比例非NULL比例=(Orig_Card - NNulls) / Orig_Card2）非popular value值基数计算方式: --Density值存储在数据字典表中，参与基数计算Card =num_rows * New Density * (num_rows-num_nulls)/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_NULLS)*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，使其用使用正确的索引。