问题描述
特定SQL遭遇 ORA-00604 / ORA-01008, 导致某些报表无法正常运行。
数据库版本为 11.2.0.4.4,操作系统 Linux,执行的错误信息如下:
ORA-604: error occurred at recursive SQL level 1 ORA-1008: not all variables bound
专家解答
仔细检查了 n 次 SQL,并未发现绑定变量赋值问题,但是SQL操作的主要表是复合分区表,启用 10046 事件跟踪 或者 Errorstack,可以通过后台日志找到了可疑的问题SQL和详细信息。
原来出现问题的并不是用户SQL,而是在后台执行的一条递归SQL语句,这个数据库自身的SQL语句调用,因为缺少变量输入,而出现了 ORA-01008 错误:
----- Error Stack Dump ----- ORA-01008: not all variables bound ----- Current SQL Statement for this session (sql_id=7ughmqbx14mfz) ----- SELECT distinct TBL$OR$IDX$PART$NUM("TERRY", 0, 2, 0, "ID") FROM (SELECT "B"."ID" "ID" FROM "G_DAW" "B" WHERE "B"."SALE_END_TIME">=TO_DATE(:B1,'yyyy-mm-dd hh24:mi:ss') AND "B"."RDC_ID"=TO_NUMBER(:B2) AND "B"."RDC_ID"=TO_NUMBER(:B3) AND "B"."SALE_BEGIN_TIME"<=TO_DATE(:B4,'yyyy-mm-dd hh24:mi:ss')) ORDER BY 1
注意:在很多数据库问题的诊断过程中,我们都能发现递归SQL的身影,当递归 SQL 出现在 AWR 报告的 TOP SQL中时,就需要注意,是否触发了BUG。
通过 TBL$OR$IDX$PART$NUM 关键字和 ORA-1008 错误号,在 MOS 上很容易找到这个问题的相关BUG:
Bug 17258090 Recursive SQL fails with ORA-1008
这个BUG的描述大致是:
对于复合分区表的某些分区剪裁,可能会发生这种情况。如果子分区级别的子查询剪裁在执行时出现 ORA-604 / ORA-1008,那就可能和这个BUG相关。
其典型特征就是,出错时指向如下SQL:
SELECT distinct TBL$OR$IDX$PART$NUM ....
简单来说,就是Oracle在子分区级别执行子查询分区剪裁时,调用的内部递归SQL,因为缺少变量传入而出错,其解决方案是,禁用子查询谓词分区剪裁。
这个特性是由参数控制的,在会话级别禁用:
alter session set "_subquery_pruning_enabled"=false;
或者修改初始化参数,在全局禁用之:
alter system set “_subquery_pruning_enabled”=false;
这个特性是在 Oracle 10g 引入的,在执行计划中,当出现 PARTITION RANGE SUBQUERY ,我们可以看到在 Pstart 和 Pstop 执行了 分区剪裁,就是使用到了这个特性。当禁用这个参数,将会影响这一部分执行计划:
通过 x$ksppi 表,可以找到所有隐含参数,及了解其含义:
SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm='_subquery_pruning_enabled'; KSPPINM -------------------------------------------------------------------------------- KSPPDESC -------------------------------------------------------------------------------- _subquery_pruning_enabled enable the use of subquery predicates to perform pruning
该参数的作用就是:允许使用子查询谓词来执行分区剪裁。和这个参数相关的还有几个参数,通过参数描述就可以知道其作用:
SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like '_subquery_pruning%'; KSPPINM ----------------------------------- KSPPDESC ------------------------------------------------------ _subquery_pruning_cost_factor subquery pruning cost factor _subquery_pruning_reduction subquery pruning reduction factor _subquery_pruning_enabled enable the use of subquery predicates to perform pruning _subquery_pruning_mv_enabled enable the use of subquery predicates with MVs to perform pruning
这个BUG的主要影响范围是 12.1.0.1 (Base Release) 和 11.2.0.4。