概述
在 Oracle 12.2 版本和新发布的 18.0 版本中存在一个 SQL 解析的 bug, 导致了数据库后台报 ora-07445 或者 ora-00600 错误报 ora-07445 时, 可导致数据库断开当前会话连接, 无法进行 SQL 操作, 当报 ora-00600 时, 会话没有断开, 但无法完成解析返回结果
该 bug 的发现敬请参考: http://www.hellodba.com/reader.php?ID=221&lang=CN
触发 Bug 的现象
2.1 报 ora-07445
后台 alert 日志:
Sqlplus 显示:
2.2 报 ora-00600
Sqlplus 显示:
该报错, 是在测试重现 ora-07445 时发现的以下我们一起来重现一下 ora-7445 的报错
Bug 重现测试
读者可以按照以下的语句, 可以在 Oracle 12.2 和 18.0 的版本中测试, 重现这个 SQL 解析的 bug, 观察报错情况重现这个 bug 重点符合以下条件:
表中有一个运行为空的字段;
该字段的统计信息被收集过;
该字段中存在空值和非空值
3.1 创建测试表并插入测试数据
3.2 收集表的统计信息
3.3 尝试解析以下语句
读者可以按照这个测试过程, 在自己的测试环境重现 ora-07445 报错, 记住, 决不能在生产环境的 12.2 的库上测试
报错的信息追踪和影响
- oracle@susu ~]$ oerr ora 07445
- 07445, 00000, "exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]"
- // *Cause: An operating system exception occurred which should result in the
- // creation of a core file. This is an internal error.
通过服务器上看这个 ora-07445 为核心存储内部的错误
4.1 后台日志报错
4.2 使用 adrci 工具分析
1> show incident 查看报错信息摘要:
adrci> show incident
2> 查看比较接近的一个 incident_id 的摘要详情:
adrci> show incident -mode detail -p "incident_id=155499"
4.3 语句的执行计划
这个执行计划是从 Oracle 12.1.0.2 版本中取得的, 只作为参考测试数据为根据上述条件创建的
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: exception encountered: core dump [__intel_ssse3_rep_memcpy()+8260] in oracle 12.2.0.1
- An Error document for ORA-7445 [type:] is not registered with the tool.
- Your request for information on this error has been recorded and will be used for publishing prioritization.
- Things to try:
- Check the error message and confirm that this is an ORA-7445 error and not an ORA-600 or ORA-700 error.
- Use 'Do a general Search for Knowledge' to begin a search for any published documents and bugs that mention the error.
由于这个 ora-07445 报错出现在之前的多个版本的不同场景, 以下为 12.1 版本中有类似的 bug 可以参考一下:
Bug 18463985 - ORA-7445 [__intel_ssse3_rep_memcpy()+8912] for stmt with adaptive plans and cfb (Doc ID 18463985.8)
以及官网文档:
Bug 21856417 - Wrong Result: null values with partial join evaluation , Filter Push Down and fix for bug 18463985 (Doc ID 21856417.8)
两篇文章中分别提到了: 查询当中循环多次使用到某个对象, 以及空值参与到了部分连判断运算
4.5 该 SQL 解析 bug 的影响
这个 SQL 解析的 bug 的影响可以从两方面来看
4.5.1 SQL 层面
SQL 层面的影响就是不能解析执行的 SQL, 没有返回结果当报 ora-07445 时候, 还中断了当前的会话
4.5.2 系统层面
经过多次的测试实验的观察, 在解析语句到会话中断这个过程, 消耗比较多的 CPU 资源和内存资源, 如果在比较繁忙的生成系统, 有可能导致数据库被 hang 住, 影响生产
避开解析 Bug 的方法
根据上述的条件, 经过另外的测试数据, 使用变量控制法, 模拟了一系列的测试实验得出触发该解析 bug 同时满足以下条件:
为 with 子句形式;
where 子句中字段的统计信息被收集过;
数据库表 D 的字段可以不存在空值, 但在临时表 A 从表 D 获取数据的 where 字句中存在关于 null 判断运算则上述中分别为: decode(d1,null,val1,val2[,...]),nvl(expr1,expr2) 或者 nvl2(expr1,expr2,expr3);
decodenvl 或者 nvl2 内嵌套有 Oracle 数据库内部函数, 如 to_number,round,trunc 等;
With 子句结构中, 临时表 B 和临时表 C 都经临时表 A 产生;
临时表 B 和临时表 C 中有至少有一个临时表 A 和伪表 dual 构成笛卡尔连接查询, 并且通过 distinct 去重;
最终的结果通过临时表 B 和临时表 C 做连接查询而得
语句形式:
5.1 方法一: 避开触发条件法
根据上述触发该 bug 的条件, 要同时满足那些条件, 才能触发, 这还是比较容易避开的也就是说, 在日常遇到这个 Oracle12.2 的解析 bug, 是比较难的事情比如以下两个例子:
例 1: 上述条件 6, 将伪表 dual 和 distinct 分别放在两个临时表中:
例 2: 上述条件 4,nvl() 函数内不嵌套 Oracle 内部函数, 直接使用标量 5100
5.2 方法二: 设置参数法
在当前测试版本的数据库, 优化器默认是使用基于成本的模式, 而使用基于规则的模式可以避开该解析 bug, 成功解析语句
默认优化模式参数:
调整该优化参数为 rule:
Alter session set optimizer_mode=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 指示, 最终这两个的作用是一样的
来源: https://yq.aliyun.com/articles/511728