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

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

求各位大大帮忙分析
...全文
635 28 打赏 收藏 举报
写回复
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)
相关推荐
发帖
Oracle 高级技术

3472

社区成员

Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
帖子事件
创建了帖子
2010-02-25 10:23
社区公告
暂无公告