今天现场人员报告说:同样数据同样SQL在个产品数据库中可以执行但是在测试数据库中总是报错

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

上面是发现个问题时候我个人大致处理思路方法也许可以给bies些帮助

下面是本次案例中些SQL操作记录和备注

eriorid字段是varchar2(100)类型存储着些数字或者下面SQL在使用to_number时报错

SQL> alter session optimizer_mode=choose;

Session altered.

SQL> select eriorid, constdisplayname
2 from (select eriorid, constdisplayname
3 from globalconst
4 where globalconst = \'status\')
5 where to_number(eriorid) < 4
6 order by to_number(eriorid);
where to_number(eriorid) < 4
*
ERROR at line 5:
ORA-01722: invalid number

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

SQL> alter session optimizer_mode=first_rows;

Session altered.

SQL> select eriorid, constdisplayname
2 from (select eriorid, constdisplayname
3 from globalconst
4 where globalconst = \'status\')
5 where to_number(eriorid) < 4
6 order by to_number(eriorid);

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+eriorid构成联合主键globalconst= \'status\'所有记录eriorid字段都确实是数字所以这次SQL正常执行了

SQL> alter session optimizer_mode=choose;

Session altered.

SQL> select eriorid, constdisplayname
2 from globalconst
3 where globalconst = \'status\'
4 and to_number(eriorid) < 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(eriorid) < 4而globalconst = \'status\'记录eriorid字段都是数字所以SQL正常执行


假设我们再insert条globalconst = \'status\'并且eriorid不是数字记录再次执行SQL会发现又报1722

SQL> alter session optimizer_mode=first_rows;

Session altered.

SQL> select to_number(eriorid), constdisplayname
2 from globalconst
3 where globalconst = \'status\'
4 and to_number(eriorid) < 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跟没有修改前样自然也是可以正常执行