optimizer_mode影响一个SQL语句是否可以执行

原文地址：http://blog.youkuaiyun.com/whqcfp/archive/2006/12/05/1430653.aspx

 

Oracle 在执行SQL语句时，有两种优化方法：即基于规则的RBO和基于代价的CBO。 在SQL执教的时候，到底采用何种优化方法，就由Oracle参数 optimizer_mode 来决定。

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      CHOOSE

optimizer_mode 参数值共有以下四个：

第一：CHOOSE

  这个是Oracle的默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于代价的CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。

    如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于代价的优化方法CBO。

   如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。

第二：ALL_ROWS

不管是不是有统计信息，全部采用基于成本的优化方法CBO。

第三：FIRST_ROWS_n

不管是不是有统计信息，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录。

第四：FIRST_ROWS

使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向后兼容。

第五：RULE

这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则的优化方法。

如何更改 optimizer_mode 的参数呢？可以用以下的方法。

SQL> alter session set optimizer_mode='RULE';

会话已更改。

 

 

 

原文地址：http://blog.113e.com/382686.shtml

 

今天现场人员报告说：同样的数据，同样的sql，在一个产品数据库中可以执行，但是在测试数据库中总是报错。

检查步骤如下：
1。在两个数据库中分别运行sql，验证是否如现场人员报告的情况，结果属实。
2。查看sql语句，了解sql的含义，此时发现该sql编写不太理想，改写以后在两个数据库中都运行正常，不过这是其它的问题，此处不表
3。检查在两个库中，该sql的执行计划是否相同，结果不同。
4。检查两个库的版本是否相同，结果相同。
5。检查两个库中的优化模式是否相同，结果不同，此时用alter session修改运行报错的那个数据库的优化模式，再次查看执行计划，发现已经相同了，再次运行sql，发现可以正常运行。
6。对于此案例，到上面第5步已经可以结束了，如果第5步中发现优化模式相同，那么这步就继续可以查看两个库中两张表的统计信息是否不同
7。如果第6步中还是相同，那么继续检查其它优化相关的参数，比如optimizer_index_cost_adj等
8。如果还相同，那么去查metalink，google，通常可以发现这是一个oracle的bug，确认自己的情况是否属于这个bug。。。

上面是发现一个问题时候我个人的大致处理方法，也许可以给newbies一些帮助。

下面是本次案例中的一些sql操作记录和备注。

interiorid字段是varchar2(100)的类型，存储着一些数字或者字符，下面的sql在使用to_number函数时报错。

sql> alter session set optimizer_mode=choose;

session altered.

sql> select interiorid, constdisplayname
  2    from (select interiorid, constdisplayname
  3            from globalconst
  4           where globalconst = @#status@#)
  5   where to_number(interiorid) < 4
  6   order by to_number(interiorid);
 where to_number(interiorid) < 4
       *
error at line 5:
ora-01722: invalid number

此时的执行计划是全表扫描，而且由于报1722错误，所以很明显是因为oracle第一步执行的是全表扫描查询所有to_number(interiorid) < 4的记录，而由于interiorid字段中含有非数字字符，所以报错。

sql> alter session set optimizer_mode=first_rows;

session altered.

sql> select interiorid, constdisplayname
  2    from (select interiorid, constdisplayname
  3            from globalconst
  4           where globalconst = @#status@#)
  5   where to_number(interiorid) < 4
  6   order by to_number(interiorid);

interiorid constdisplayname
---------- ----------------------------------------
0          正常
1          销户
2          冻结
3          锁定


execution plan
----------------------------------------------------------
   0      select statement optimizer=first_rows (cost=5 card=1 bytes=2
          2)

   1    0   sort (order by) (cost=5 card=1 bytes=22)
   2    1     table access (by index rowid) of @#globalconst@# (cost=3 c
          ard=1 bytes=22)

   3    2       index (range scan) of @#pk_globalconst@# (unique) (cost=
          2 card=1)

修改优化模式，sql开始使用pk进行索引扫描，该索引是globalconst+interiorid构成的联合主键，因为globalconst= @#status@#的所有记录interiorid字段都确实是数字，所以这次sql正常执行了。

sql> alter session set optimizer_mode=choose;

session altered.

sql> select interiorid, constdisplayname
  2    from globalconst
  3   where globalconst = @#status@#
  4     and to_number(interiorid) < 4
  5   order by 1;

interiorid constdisplayname
---------- ----------------------------------------
0          正常
1          销户
2          冻结
3          锁定


execution plan
----------------------------------------------------------
   0      select statement optimizer=choose (cost=3 card=1 bytes=22)
   1    0   sort (order by) (cost=3 card=1 bytes=22)
   2    1     table access (full) of @#globalconst@# (cost=1 card=1 byte
          s=22)

我们把sql换一种写法，虽然执行计划显示的仍然是全表扫描，但是可以推测此时oracle使用了globalconst = @#status@#作为filter的条件，满足条件的再判断是否to_number(interiorid) < 4，而因为globalconst = @#status@#的记录interiorid字段都是数字，所以sql正常执行。
假设我们再insert一条globalconst = @#status@#并且interiorid不是数字的记录，再次执行sql，会发现又报1722错误。

sql> alter session set optimizer_mode=first_rows;

session altered.

sql> select to_number(interiorid), constdisplayname
  2    from globalconst
  3   where globalconst = @#status@#
  4     and to_number(interiorid) < 4
  5   order by 1;

to_number(interiorid) constdisplayname
--------------------- ----------------------------------------
                    0 正常
                    1 销户
                    2 冻结
                    3 锁定


execution plan
----------------------------------------------------------
   0      select statement optimizer=first_rows (cost=5 card=1 bytes=2
          2)

   1    0   sort (order by) (cost=5 card=1 bytes=22)
   2    1     table access (by index rowid) of @#globalconst@# (cost=3 c
          ard=1 bytes=22)

   3    2       index (range scan) of @#pk_globalconst@# (unique) (cost=
          2 card=1)

修改优化模式，sql开始使用pk进行索引扫描，此时sql跟没有修改前一样，自然也是可以正常执行的