求执行计划变动的可能原因和解决方案

xiaoxiao1984 2010-02-25 10:23:32
1. 数据库版本:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

2. 操作系统版本：Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
内核 2.6.9-42.ELsmp

3. 问题现象，SQL的执行计划有的时候会随机变动，某个分区表上的SQL执行计划会突然变动，系统通过调用 dbms_stats 包定期收集该分区表的统计信息和表上索引的统计信息，表上索引较多，在执行计划发生变动的时候，通常是使用了一个预料中不应该使用的索引。通过hints能够保证SQL一直走正确的执行计划，但是需要应用做调整，并且加hints不是特别放心。发生执行计划变动的时候，通过flush shared pool来重新生成执行计划就能解决问题，生成正确的执行计划，囧囧的。

请各位大大帮忙想想有啥可能会导致执行计划的变动，目前想到的可能：
1. 执行计划定期失效后或者由于share pool大小不足导致重新生成执行计划，而重新生成执行计划的时候，bind的值比较偏，导致生成执行计划糟糕；目前已经考虑增加shared pool的大小，pink绑定变量的值这个在10g没有什么好方法来确认和解决。

2. dbms_stats 先更新表的统计信息，后更新索引的统计信息，而重新生成执行计划正好在更新表的统计信息之后，更新索引的统计信息之前，导致CBO生成执行计划的时候选择了错误的索引？

3. 木知，发愁ing

求各位大大帮忙分析
...全文
727 28 打赏 收藏 转发到动态 举报
写回复
用AI写文章
28 条回复
切换为时间正序
请发表友善的回复…
发表回复
License2Kill 2011-08-21
  • 打赏
  • 举报
 回复
晕，怎么又看到2010年的帖子。 一切皆有可能，分析了很久有可能就是个简单原因导致。我们有一次是由于小分区，比如只有1条或0条的分区先执行，导致走全表扫描，执行大分区时便也按这个执行计划来，导致问题。目前通过大分区先跑来解决
xiaoxiao1984 2010-03-24
  • 打赏
  • 举报
 回复
最后我们通过创建outline解决，目前短期观察未出现执行计划异常变动
谢谢各位帮忙的GJDM，特别谢谢inthirties老大
鞠躬，感谢
结贴了
inthirties 2010-03-09
  • 打赏
  • 举报
 回复
引用 24 楼 sleepzzzzz 的回复:
上次遇到你类似的问题，无论如何都不走索引，加大内存就解决了，也迷惑ING!!!!


hint也不走么。
sleepzzzzz 2010-03-08
  • 打赏
  • 举报
 回复
上次遇到你类似的问题，无论如何都不走索引，加大内存就解决了，也迷惑ING!!!!
inthirties 2010-03-08
  • 打赏
  • 举报
 回复
引用 20 楼 inthirties 的回复:
引用 18 楼 xiaoxiao1984 的回复:
引用 15 楼 inthirties 的回复:
出现这样的情况多是你说的1这样的情况。

如果是可以改应用的话，最好是加上hint，或者是不用bind这样的方式。

如果是应用不好改的话，试试修改cursor_sharing=exact或者simliar试试，不过不知道行不行，感觉可行性不是很大 或者做直方图的分析。

现在考虑确认完所有执行计划之后做outline来固定执行计划，虽然这个不是啥好方法，不得不迫使偶们固执的认为偶们比oracle聪明……

传说中11g已经解决bind的值偏的问题……

这个方法也可以，但是不是好方法，这样还是会导致有错误的执行计划出现。


不过也没有好的方法了哟。

xiaoxiao1984 2010-03-08
  • 打赏
  • 举报
 回复
今天做另外一个库的AWR分析报告时候，突然想起当时没有想到的地方，没有检查 V$SQL_SHARED_CURSOR 视图确认不共享SQL的原因，如果重新解析SQL的话，应该在这个视图里有不共享SQL的原因，同时version count的值应该不低

如果当时关注了这个视图，也许就可以获取一点执行计划变动的原因，这个教训以后记住了
tangren 2010-03-02
  • 打赏
  • 举报
 回复
oracle这个执行计划不稳定的问题比较烦人，没办法，自己在使用时通常都是加hint来解决。
fuyou001 2010-03-02
  • 打赏
  • 举报
 回复
关注 学习 回复内容还短吗
inthirties 2010-03-01
  • 打赏
  • 举报
 回复
引用 18 楼 xiaoxiao1984 的回复:
引用 15 楼 inthirties 的回复:
出现这样的情况多是你说的1这样的情况。

如果是可以改应用的话，最好是加上hint，或者是不用bind这样的方式。

如果是应用不好改的话，试试修改cursor_sharing=exact或者simliar试试，不过不知道行不行，感觉可行性不是很大 或者做直方图的分析。

现在考虑确认完所有执行计划之后做outline来固定执行计划，虽然这个不是啥好方法，不得不迫使偶们固执的认为偶们比oracle聪明……

传说中11g已经解决bind的值偏的问题……


这个方法也可以，但是不是好方法，这样还是会导致有错误的执行计划出现。
xiaoxiao1984 2010-03-01
  • 打赏
  • 举报
 回复
如约散分，各位UP的老大来领分
http://topic.csdn.net/u/20100301/23/6cee6e82-118f-4ca5-bb8f-6dd5ab5c112c.html

问题解决后两贴一起结

继续UP等待更好的方法
xiaoxiao1984 2010-03-01
  • 打赏
  • 举报
 回复
引用 15 楼 inthirties 的回复:
出现这样的情况多是你说的1这样的情况。

如果是可以改应用的话，最好是加上hint，或者是不用bind这样的方式。

如果是应用不好改的话，试试修改cursor_sharing=exact或者simliar试试，不过不知道行不行，感觉可行性不是很大 或者做直方图的分析。


现在考虑确认完所有执行计划之后做outline来固定执行计划，虽然这个不是啥好方法，不得不迫使偶们固执的认为偶们比oracle聪明……

传说中11g已经解决bind的值偏的问题……
2到20个英文 2010-03-01
  • 打赏
  • 举报
 回复
通过AWR将两个执行计划比较，确定哪个执行计划使用更优。
然后，将按好的执行计划所应用的表统计信息单独收集，进行锁定，主要考虑基数及聚集因子的值。
同时，注意统计信息收集时不要进行直方图的统计信息收集。
oraclemch 2010-02-28
  • 打赏
  • 举报
 回复
楼主的执行计划截图好乱啊！
inthirties 2010-02-28
  • 打赏
  • 举报
 回复
出现这样的情况多是你说的1这样的情况。

如果是可以改应用的话，最好是加上hint，或者是不用bind这样的方式。

如果是应用不好改的话，试试修改cursor_sharing=exact或者simliar试试，不过不知道行不行，感觉可行性不是很大 或者做直方图的分析。
inthirties 2010-02-28
  • 打赏
  • 举报
 回复
看看show parameter cursor_sharing的结果
sorry0481 2010-02-27
  • 打赏
  • 举报
 回复
顶啊，有分拿还有知识学，哈哈
xiaoxiao1984 2010-02-26
  • 打赏
  • 举报
 回复
to inthirties 老大：
应用是绑定变量的
执行计划和索引笨猫明天贴上来，稍等，表上索引较多

to suiziguo 大大：
dbms_xplan.display_awr(&sql_id) 能够看到历史上正确和错误的执行计划
dbms_xplan.display_cursor(&sql_id) 看到当前的执行计划

出现问题的时候执行
explain plan for ....
select * from table(dbms_xplan.display);
看到的执行计划就是错误的，这个时候检查 v$session_longops视图能看到查询等待
比对正常情况下的执行计划，发现走的索引不同，所以可以确认出现错误的时候执行计划不正确
xiaoxiao1984 2010-02-26
  • 打赏
  • 举报
 回复
引用 9 楼 suiziguo 的回复:
你是依据什么判断执行计划的正确与错误的？


这个SQL出现问题的时候，系统load暴涨至40-50，平常load不超过10；此时awr报告上能看到这个SQL在cpu和gets的排名No1，在系统里查询这个SQL的执行计划，是走 IDX_COLUMN_D_E_F 的；
正常情况下上系统查询这个SQL的执行计划，是走 IDX_COLUMN_A_D 和 IDX_COLUMN_C_D 的

通过flush shared pool来重新生成执行计划就是走 IDX_COLUMN_A_D 和 IDX_COLUMN_C_D 的

这样应该能比较肯定是走IDX_COLUMN_D_E_F 执行计划有问题的

xiaoxiao1984 2010-02-26
  • 打赏
  • 举报
 回复
to inthirties 老大：
1. 业务逻辑涉及相关保密，所以替换了表名和字段名，请理解；
2. SQL很简单，前期设计不完善，所以导致使用了or来进行查询，现在再设计肯定不会选择这样的设计方式，教训
3. 也许表上索引过多，oracle迷惑鸟…… 说实话，偶很困惑鸟
suiziguo 2010-02-26
  • 打赏
  • 举报
 回复
你是依据什么判断执行计划的正确与错误的？
加载更多回复（8）
Node + Redis 接口性能优化实战
为什么要使用Redis优化接口... 我们在碰到需要执行耗时特别久，且结果不频繁变动的SQL，就特别适合将运行结果放入缓存，这样，后面的请求就去缓存中读取，请求使得能够非常迅速的响应，极大的提升我们的API接口的性能。
项目延期的4大原因及解决方案！
项目从启动到收尾涉及到的工作非常多，在过程中的某一处因为某个原因可能便会导致项目延期 一、前期评估偏差 评估偏差，由于项目成员经验不足或者粗心大意没有详细了解项目内容，一个月的工作量可能评估了20天，那么项目延期是必然的。 在项目成员经验足或者不足的情况下，都需要去详细的过一遍项目内容，没注意到的一个点可能还是需要比较多的工作量。 遇到评估原因导致项目即将延期，处理方式一般有以下几种 1. 申请延期。重新评估后将方案提交项目干系人进行开会讨论，达成一致后按照新计划进行...
Visual Studio下包含多项目的解决方案及项目间引用
一般的解决方案都包含两个以上项目。其中一些生成dll等链接库，一些生成应用程序。本文简单介绍了如何创建包含多个项目的解决方案。     1). 参考http://blog.sina.com.cn/s/blog_6e0693f70100sn4a.html一文，执行此文中的步骤1）到步骤6），建立dll文件。     2). 在解决方案资源管理器中右键单击解决方案图标，Add ->
数据中心解决方案之灾备方案设计（上）
1.数据中心容灾备份解决方案 随着社会的发展和科技的进步，政府日常工作越来越依赖于数据处理来进行，政务系统的连续性依赖于数据中心系统的稳定运行。然而，灾难就像灰尘一样伏击在运营环境周围，政务系统的数据中心可能正在一个充满风险和威胁的环境下运行。如果不能对这些风险采取有效治理，一旦数据由于某种原因丢失，就很有可能对政府的日常工作造成严重的影响。如果核心数据丢失，将会使得某些核心功能陷入瘫
高危漏洞 log4j漏洞，产生原因和解决方案
2021年12月09日，360CERT监测发现Apache Log4j 2存在JNDI远程代码执行，漏洞等级：**严重**，漏洞评分：**9.8**。
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章