作者简介 云和恩墨南区交付技术顾问,曾服务过通信、能源生产、金融等行业客户,擅长 SQL 审核和优化,DataGuard 容灾等。 在 Oracle 12.2 版本和新发布的18.0版本中存在一个 SQL 解析的 bug,导致了数据库后台报 ora-07445 或者 ora-00600 错误。报 ora-07445 时,可导致数据库断开当前会话连接,无法进行 SQL 操作,当报 ora-00600 时,会话没有断开,但无法完成解析返回结果。 该 bug 的发现敬请参考:http://www./reader.php?ID=221&lang=CN 2.1 报 ora-07445后台 alert 日志: Sqlplus 显示: 2.2 报 ora-00600Sqlplus 显示: 该报错,是在测试重现 ora-07445 时发现的。以下我们一起来重现一下 ora-7445 的报错。 读者可以按照以下的语句,可以在 Oracle 12.2 和18.0的版本中测试,重现这个 SQL 解析的 bug,观察报错情况。重现这个 bug 重点符合以下条件:
3.1 创建测试表并插入测试数据create table tt1 (c1 number, c2 date); insert into tt1 values(1, sysdate); insert into tt1 values(1, null); commit; 3.2 收集表的统计信息exec dbms_stats.gather_table_stats('SYS', 'TT1', METHOD_OPT=>'for all columns'); 3.3 尝试解析以下语句explain plan for with ut as (select c1 from tt1 where nvl (c2 , trunc (sysdate ) ) >= trunc (sysdate ) ), txo as (select distinct c1 from ut , dual), u as (select * from ut) select * from u , txo ; 读者可以按照这个测试过程,在自己的测试环境重现 ora-07445 报错,记住,决不能在生产环境的 12.2 的库上测试。
通过服务器上看这个 ora-07445 为核心存储内部的错误。 4.1 后台日志报错4.2 使用 adrci 工具分析1> show incident 查看报错信息摘要:
2> 查看比较接近的一个 incident_id 的摘要详情:
4.3 语句的执行计划这个执行计划是从 Oracle 12.1.0.2 版本中取得的,只作为参考。测试数据为根据上述条件创建的。with sal as (select empno,name,salary from mytest where nvl(signdate,trunc (sysdate ) ) >= trunc (sysdate ) ), inc as (select distinct salary from sal,dual), mark as (select empno,name from sal) select * from inc,mark; Oracle 12.1中基于成本模式的 SQL 执行计划: 4.4 Oracle 官网对报错号的描写通过 Oracle 官网文档 ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)查看报错号对应的 bug 信息,发现官网未对 oracle 12.2 或者 18.0 这个 SQL 解析的 bug 作发布,如下:
由于这个 ora-07445 报错出现在之前的多个版本的不同场景,以下为12.1版本中有类似的 bug 可以参考一下:
以及官网文档:
两篇文章中分别提到了:查询当中循环多次使用到某个对象,以及空值参与到了部分连判断运算。 4.5 该 SQL 解析 bug 的影响这个 SQL 解析的 bug 的影响可以从两方面来看。 4.5.1 SQL 层面SQL 层面的影响就是不能解析执行的 SQL,没有返回结果。当报 ora-07445 时候,还中断了当前的会话。4.5.2 系统层面经过多次的测试实验的观察,在解析语句到会话中断这个过程,消耗比较多的 CPU 资源和内存资源,如果在比较繁忙的生成系统,有可能导致数据库被 hang 住,影响生产。 根据上述的条件,经过另外的测试数据,使用变量控制法,模拟了一系列的测试实验得出触发该解析 bug 同时满足以下条件:
语句形式:with A as (select d1,d2,d3,[...] from D where [decode] nvl(d3,trunc (val1 ) ) >= [trunc (]val2 ) ), B as (* from A,dual), C as (select * from A) select * from B,C [where B.b1=C.c1]; 5.1 方法一:避开触发条件法根据上述触发该 bug 的条件,要同时满足那些条件,才能触发,这还是比较容易避开的。也就是说,在日常遇到这个 Oracle12.2 的解析 bug,是比较难的事情。比如以下两个例子:例1:上述条件6,将伪表 dual 和 distinct 分别放在两个临时表中: 例2:上述条件4,nvl() 函数内不嵌套 Oracle 内部函数,直接使用标量 5100: 5.2 方法二:设置参数法在当前测试版本的数据库,优化器默认是使用基于成本的模式,而使用基于规则的模式可以避开该解析 bug,成功解析语句。 默认优化模式参数: 调整该优化参数为 rule:
解析一语句已成功解析: 解析二语句已成功解析: 这里跟上述,同样的语句,但看不到报 ora-00600 的错。 5.3 方法三:添加 hint 指示法当前会话的优化模式为 ALL_ROWS,同样使用以上两个语句进行测试验证。 解析一: 解析二: 以上两个语句使用了 hint 指示之后,原来不能正常解析的,没有触发正常解析,其实原理和方法二是类似的。 以上展示部分的测试实验情况,没有完全展现出来。本次的测试使用了变量控制法,逐个因素地测试,尝试找出触发 Oracle 12.2 SQL 解析 bug 的条件。个人技术知识方面有限,难免还存在一些不足,希望得到更多一些的指点。虽然目前未能很准确定位该 bug,目前我们能够确定的就是以上几个条件,能触发这个解析的 bug,相信在日常生产的业务应用中,是很难遇到这个 bug 的。就算你很幸运在维护数据库中遇到 SQL 解析的 bug,不妨可以尝试按照以上介绍的三种方法,去绕开这个 bug。可以是在 session 级别设置 optimizer_mode 参数值为 RULE,也可以是加上 RULE 指示,最终这两个的作用是一样的。 |
|