About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(172)
分类: Oracle
2020-06-10 16:00:30
1)找出入口操作Id=6,由于Id=6操作的cardinality估算为1导致后续走一系列NESTED LOOPS影响效率。
2)cardinality的计算与谓词紧密相关,所以要找出Id=6的谓词,根据谓词手动计算真实card与估算card之间的区别
3)尝试收集统计信息,检验效果
现在的问题,也就是转为对表DEALREC_ERR_201608统计信息准确性的问题,特别是统计信息对谓词计算的准确性。
尝试更新统计信息:
发现使用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,内部全部要转为数字来计算选择性,很显然,乱定义列类型也是有问题的。所以有针对性地修正收集的统计信息,是很有必要的。
疑问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:)