我认为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; /
旧库执行计划,
新库执行计划,
原因可能是什么?
比较了下,table表在新库和旧库的索引状态,都是USABLE,查看统计信息,发现旧库统计信息为空,
新库,按照owner采集了统计信息,因此不为空,
再比较下新旧库的优化器模式,均为CHOOSE,
这能说明什么?
首先,9i下优化器模式默认的是CHOOSE,这个值是表示,Oracle既可以采用基于规则的优化器(RBO),也可以采用基于成本代价的优化器(CBO),到底使用那个值,取决于当前SQL的被访问的表中是不是有可以使用的统计信息。如果有多个被访问的表,其中有一个或多个有统计信息,那么Oralce会对没有统计信息的表进行采样统计(即不全部采样),统计完成后,用基于成本代价的优化器CBO。如果所有被访问的表都没有统计信息,Oracle就会采用基于规则的优化器RBO,优先级别如下,
再回到这个问题,由于是9i,没有自动采集统计信息的定时任务,除非手工或脚本,确认之后,这个库是没有任何统计信息采集的脚本,又未能手工采集,因此旧库的统计信息为空,CHOOSE下会选择RBO,按照优先级,索引要高于全表,因此采用了c字段的索引,作为执行计划,而新库,手工采集了,统计信息不为空,因此选择CBO,根据成本计算,选择代价最小的,于是选择了全表扫描。
可能的解决方案,
1. 9i用outline锁定执行计划。 2. 对SQL采用hint,强制使用索引。 3. 删除新库这张表的统计信息,让他选择RBO。
针对上面的问题,以及解决方案,不知道是否有更合适的?还请路过的各位高人给出一些指点,谢谢。