从一条SQL看基于ORACLE的SQL优化_PART2

接PART1：

2.4 统计信息与cardinality

2.4.1解决cardinality估算不准确问题

1）找出入口操作Id=6,由于Id=6操作的cardinality估算为1导致后续走一系列NESTED LOOPS影响效率。

2）cardinality的计算与谓词紧密相关，所以要找出Id=6的谓词，根据谓词手动计算真实card与估算card之间的区别

3）尝试收集统计信息，检验效果
现在的问题，也就是转为对表DEALREC_ERR_201608统计信息准确性的问题，特别是统计信息对谓词计算的准确性。

2.4.2 解决cardinality估算不准确问题-扩展统计信息收集

尝试更新统计信息：

发现使用size auto,size repeat,对other_class收集直方图均无效果，执行计划中对other_class的查询条件返回行估算还是1（实际返回2000w行）.如何解决？card的计算和谓词紧密相关，查看谓词：

substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)

怎么办？思绪万千，灵光乍现!

Hints:cardinality(a,20000000)，use_hash等可以

还有更好的办法吗？

突然想起11g有个统计信息收集新特性：扩展统计信息收集

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>‘xxx',tabname=>‘DEALREC_ERR_201608',method_opt=>'for columns (substr(other_class, 1, 3)) size skewonly',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);

扩展统计信息一收集，执行计划如下：

1）DEALREC_ERR_201608与B_DEALING_DONE_TYPE原来走NL的现在正确走HASH JOIN。Build table是小结果集，probe table是ERR表大结果集，正确。

2）但是ID=2与ID=11到14，也就是与TMI_NO_INFOS的OR子查询，还是FILTER,驱动数千万次子节点查询，下一步优化要解决的问题。

3）性能从12小时到2小时。到这里结束了吗？
统计信息的问题还是很多的，一个表的统计信息收集，特别是自动收集，不一定能让所有相关SQL找到最佳执行路径，特别是SQL条件复杂、数据倾斜、表类型定义不准确等情况，特别是使用了复杂条件，CBO无法准确计算对应谓词的card,或者类型定义不准确，本来是日期的用了VARCHAR2，内部全部要转为数字来计算选择性，很显然，乱定义列类型也是有问题的。所以有针对性地修正收集的统计信息，是很有必要的。

2.4.3 解决cardinality估算不准确问题-有关统计信息的那些疑问

疑问1：100%收集为什么还没有走正确执行计划？

统计信息收集比例高不代表就可以反应对应谓词的特征，而且统计信息内部有很多算法限制以及不完善的情况，比如11g的扩展统计信息来继续完善，12c也有很多统计信息完善的特性，所以并不是比例低就不好，比例高就好！统计信息的收集要满足核心SQL的执行效率，对于非核心SQL一定程度上可以不用过度关注，因为统计信息很难满足所有相关SQL的最佳执行。

疑问2：统计信息各种维度收集了包括直方图都收集了怎么不起作用？

直方图有很多限制，12c之前，只有频度直方图和等高直方图两种，对很多值的分布不能精确表示，所以有很多限制。因此,12c又增加了2种直方图：顶级频度直方图和混合直方图。另外直方图还有只存储前32位字符的限制。

疑问3：直方图只对走索引的有作用？

很显然不对，直方图只是反应数据的分布，数据的分布正确，对应谓词可以查询出比较准确的cardinality,从而影响执行计划，所以，对全表也是有用的。

疑问4：收集或更新了统计信息，执行计划怎么变得更差了？

很有可能，比如把原来的直方图给去掉了可能导致执行计划变差，因此，一般更新使用size repeat，除非确认需要修改某些直方图，另外谓词和统计信息紧密相关，某些谓词条件一旦收集统计信息，可能就计算不准确了。

疑问5：执行计划中cardinality显示的和已有统计信息计算不一致？

oracle cbo内部算法很复杂，而且BUG众多，遇到问题要大胆怀疑。

疑问6：统计信息应该按照Oracle建议自动收集？

具体问题具体分析，是让Oracle自动还是自己写脚本收集，都需要长期实践总结，对于一个复杂系统来说采样比例和method_opt很多需要定制设置。

疑问7：为什么唯一性很好的列，还需要收集直方图？

选择性的内部计算是要转成数字的：CBO内部计算选择性会先将字符串转为RAW，然后RAW转为数字，左起ROUND 15位。如果字符串的唯一性好，但是计算成数字后唯一性不好，则会导致执行计划错误，这时候也需要收集直方图。

疑问8：我需要根据统计信息以及CBO公式去计算COST吗？

不需要，除非你很喜欢研究，得不偿失，了解各种JOIN算法、查询转换特性、索引等效率和哪些有关即可，COST不是最需要关心的指标，我们应该关心SQL高效运行所需的执行路径和执行方法，是否可以达到及早过滤大量数据，JOIN方法和顺序是否正确，是否可以建立高效访问对象等。

未完待续（PART3:）