一个复杂SQL的调优过程

 

        在oracle10g开发环境，同事找我说有个SQL执行的特别的慢，可否给优化一下。

照例，先在pl/sql 中看执行计划，先看全表扫描的那些表，发现了一个全表扫描的大表，有160万条记录，和其它的表做了merge join。

看这个大表没有索引，给它加上索引，再执行，发现执行计划还是走全表扫描，没有用到新建的索引。

给sql先加上hint，看看建立索引的效果，

select /*+ INDEX ( table别名,index名字 ) */ ,执行的很快，看计划发现已经用到了新建的索引。

去掉hint，发现还是走全表扫描。

单独执行  select * from table where index_col='111111' 发现居然是走全表扫描，按照我们的设想应该是走索引扫描的。

看来是oralce的数据统计出了问题了。

用system用户进去看看表的索引的统计信息吧。

表的统计信息

select * from dba_tab_statistics t where t.table_name='TABLE名称'

发现 num_rows=0,  就是说 系统认为这个表是空表。

看索引的统计信息

select * from dba_inx_statistics t where t.index_name='INDEX名称'

distinct_keys 有160万，num_rows有163万，系统认为这是个大表。

问题就来了，oracle10g 在做执行计划时，会看统计信息，如果它觉得全表扫描比索引扫描更快，那就说啥也不会走索引扫描啦。

那就再统计一下的正确的表信息吧。

执行

exec dbms_stas.gather_table_status(ownname=>'模式名',tabname=>‘table名’);

然后再看表统计信息

select * from dba_tab_statistics t where t.table_name='TABLE名称'

发现 num_rows=163万, OK。

再执行原来的复制sql，发现以前执行几十秒的sql，现在2秒就搞定啦，问题解决。

总结：

所以说，有时候oracle特别的傻，自作聪明的选择错误的执行计划。

首先我们要保证收集的统计信息的及时和准确，否则oracle自己就会犯傻啦。

对于那些记录变化很大的表，要单独针对这个表做统计信息收集。