Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1373431
  • 博文数量: 172
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3831
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

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)

文章存档

2024年(27)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2020-06-10 16:00:30

接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估算不准确问题-有关统计信息的那些疑问

疑问1100%收集为什么还没有走正确执行计划?

统计信息收集比例高不代表就可以反应对应谓词的特征,而且统计信息内部有很多算法限制以及不完善的情况,比如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:)
阅读(1204) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~