9i下优化器模式的选择

落日映苍穹つ 2022-04-24 05:42 115阅读 0赞

这是杂货铺的第461篇文章

我认为Oracle最重要、最核心、智能化程度最高的技术之一，就是优化器。他决定了一条SQL，在现有条件下，用什么执行计划，是最优的。有高人说过“Oracle中80%的性能问题都是来自SQL语句”，因此，优化器的好坏，一定程度上就决定了SQL语句的执行效率，进而影响整个数据库的性能。

最近，兄弟部门有套老的应用，需要做次数据库迁移，从一台旧的9i，迁到一台新的9i，某条SQL语句，从一张分区表table，数据量超过1亿，使用了分区索引字段c，按照like做模糊检索，构造模拟如下：

> select a, b  
> 
> 
> from (select \* from table where c like :1 order by p desc) t  
> 
> 
> where rownum < 100;

发现，在旧库使用了分区局部索引，而在新库，就用了全表扫描，已经对新库手工采集了统计信息，按照owner收集，

> begin  
> dbms\_stats.gather\_schema\_stats(  
> ownname => 'USER',  
> cascade => true,  
> degree => 8,  
> estimate\_percent =>  dbms\_stats.auto\_sample\_size,  
> method\_opt => 'for all columns size auto'  
> );  
> end;  
> /

旧库执行计划，

![640?wx\_fmt=png][640_wx_fmt_png]

新库执行计划，

![640?wx\_fmt=png][640_wx_fmt_png 1]

原因可能是什么？

比较了下，table表在新库和旧库的索引状态，都是USABLE，查看统计信息，发现旧库统计信息为空，

![640?wx\_fmt=png][640_wx_fmt_png 2]

新库，按照owner采集了统计信息，因此不为空，

![640?wx\_fmt=png][640_wx_fmt_png 3]

再比较下新旧库的优化器模式，均为CHOOSE，

![640?wx\_fmt=png][640_wx_fmt_png 4]

这能说明什么？

首先，9i下优化器模式默认的是CHOOSE，这个值是表示，Oracle既可以采用基于规则的优化器(RBO)，也可以采用基于成本代价的优化器(CBO)，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计(即不全部采样)，统计完成后，用基于成本代价的优化器CBO。如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化器RBO，优先级别如下，

![640?wx\_fmt=png][640_wx_fmt_png 5]

再回到这个问题，由于是9i，没有自动采集统计信息的定时任务，除非手工或脚本，确认之后，这个库是没有任何统计信息采集的脚本，又未能手工采集，因此旧库的统计信息为空，CHOOSE下会选择RBO，按照优先级，索引要高于全表，因此采用了c字段的索引，作为执行计划，而新库，手工采集了，统计信息不为空，因此选择CBO，根据成本计算，选择代价最小的，于是选择了全表扫描。

可能的解决方案，

1. 9i用outline锁定执行计划。  
2. 对SQL采用hint，强制使用索引。  
3. 删除新库这张表的统计信息，让他选择RBO。

针对上面的问题，以及解决方案，不知道是否有更合适的？还请路过的各位高人给出一些指点，谢谢。

关于优化器CBO和RBO的文章，

《[Oracle CBO选错执行计划的一种场景][Oracle CBO]》

《[CBO如何选择相同cost的索引][CBO_cost]》  
《[返璞归真 - Oracle 9i的RBO执行计划案例][- Oracle 9i_RBO]》

[640_wx_fmt_png]: /images/20220218/ce6303ef737043d2b6e54f5667258c15.png
[640_wx_fmt_png 1]: /images/20220218/eaf3587d2f2e4a5a93c305d8ab0aeea6.png
[640_wx_fmt_png 2]: /images/20220218/5093972e746441b3b0d2f6e7d39a0efb.png
[640_wx_fmt_png 3]: /images/20220218/ad2aac9e397c423ab6501f0323e35dd5.png
[640_wx_fmt_png 4]: /images/20220218/c6762da0c0084efc9a7eec84fb14cb80.png
[640_wx_fmt_png 5]: /images/20220218/383eee917212436b9e89c1dd2eeaad59.png
[Oracle CBO]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247485348&idx=1&sn=4fc3ee688e3ebc4f767bbc315f685e80&chksm=ea814257ddf6cb410c71c1bc0a6e6f160acad89a3d3b103774b65bd6b3f28e3fe33209e338fe&scene=21#wechat_redirect
[CBO_cost]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247485286&idx=1&sn=a1bf15ddf9cedee5bcdb41050a323f5f&chksm=ea814295ddf6cb836131b8e40eabcee0a57812ff676166bf4f145941eb6981b75894ec30c0dd&scene=21#wechat_redirect
[- Oracle 9i_RBO]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247486548&idx=1&sn=92379ff5a155ada64decab097738d355&chksm=ea8149a7ddf6c0b12ca3750bea725ef49fc7d524fedb9ed0352cc69142c795f0154596a9b536&scene=21#wechat_redirect