DBA手记 - optimizer_mode影响一个SQL语句是否可以执行

今天现场人员报告说:同样的数据,同样的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跟没有修改前一样,自然也是可以正常执行的

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页