18C 也不能避免 SQL 解析的 Bug

作者简介

苏星开

云和恩墨南区交付技术顾问,曾服务过通信、能源生产、金融等行业客户,擅长 SQL 审核和优化,DataGuard 容灾等。

1

概述

在 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

2

触发 Bug 的现象

2.1 报 ora-07445

后台 alert 日志:

Sqlplus 显示:

2.2 报 ora-00600

Sqlplus 显示:

该报错,是在测试重现 ora-07445 时发现的。以下我们一起来重现一下 ora-7445 的报错。

3

Bug 重现测试

读者可以按照以下的语句,可以在 Oracle 12.2 和18.0的版本中测试,重现这个 SQL 解析的 bug,观察报错情况。重现这个 bug 重点符合以下条件:

  1. 表中有一个运行为空的字段;
  2. 该字段的统计信息被收集过;
  3. 该字段中存在空值和非空值。

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 的库上测试。

4

报错的信息追踪和影响

[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 版本中取得的,只作为参考。测试数据为根据上述条件创建的。

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: 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 住,影响生产。

5

避开解析 Bug 的方法

根据上述的条件,经过另外的测试数据,使用变量控制法,模拟了一系列的测试实验得出触发该解析 bug 同时满足以下条件:

  1. 为 with 子句形式;
  2. where 子句中字段的统计信息被收集过;
  3. 数据库表 D 的字段可以不存在空值,但在临时表 A 从表 D 获取数据的 where 字句中存在关于 null 判断运算。则上述中分别为:decode(d1,null,val1,val2[,...]),nvl(expr1,expr2)或者nvl2(expr1,expr2,expr3);
  4. decode、nvl 或者 nvl2 内嵌套有 Oracle 数据库内部函数,如to_number,round,trunc 等;
  5. With 子句结构中,临时表 B 和临时表 C 都经临时表A产生;
  6. 临时表 B 和临时表 C 中有至少有一个临时表 A 和伪表 dual 构成笛卡尔连接查询,并且通过 distinct 去重;
  7. 最终的结果通过临时表 B 和临时表 C 做连接查询而得。

语句形式:

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:

Alter session set optimizer_mode=RULE;

解析一语句已成功解析:

解析二语句已成功解析:

这里跟上述,同样的语句,但看不到报 ora-00600 的错。

5.3 方法三:添加 hint 指示法

当前会话的优化模式为 ALL_ROWS,同样使用以上两个语句进行测试验证。

解析一:

解析二:

以上两个语句使用了 hint 指示之后,原来不能正常解析的,没有触发正常解析,其实原理和方法二是类似的。

6

总结

以上展示部分的测试实验情况,没有完全展现出来。本次的测试使用了变量控制法,逐个因素地测试,尝试找出触发 Oracle 12.2 SQL 解析 bug 的条件。个人技术知识方面有限,难免还存在一些不足,希望得到更多一些的指点。虽然目前未能很准确定位该 bug,目前我们能够确定的就是以上几个条件,能触发这个解析的 bug,相信在日常生产的业务应用中,是很难遇到这个 bug 的。就算你很幸运在维护数据库中遇到 SQL 解析的 bug,不妨可以尝试按照以上介绍的三种方法,去绕开这个 bug。可以是在 session 级别设置 optimizer_mode 参数值为 RULE,也可以是加上 RULE 指示,最终这两个的作用是一样的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-03-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IMWeb前端团队

Unix Pipes to Javascript Pipes

本文作者:IMWeb 杨文坚 原文出处:IMWeb社区 未经同意,禁止转载 Unix Pipes Unix管道扫描稿 ? 简单样例: $ ne...

17210
来自专栏斑斓

漂亮的with,鱼与熊掌可以兼得

假设要加载磁盘上的一个文件,并以二进制形式读取文件的数据。若要从健壮性的角度考虑,需得考虑两种异常情况: 加载文件失败,例如给定的文件路径并不存在该文件 读取文...

3498
来自专栏喵了个咪的博客空间

phalapi-进阶篇4(notrom进阶以及事务操作)

#phalapi-进阶篇4(notrom进阶以及事务操作)# ? ##前言## 先在这里感谢phalapi框架创始人@dogstar,为我们提供了这样一个优秀的...

3026
来自专栏逸鹏说道

C# 温故而知新:Stream篇(四)下

上面的例子是将一个文件作为整体进行操作,这样会带来一个问题,当文件很大或者网络不是很稳定的时候会发生意想不到的错误 那我们该怎么解决...

3145
来自专栏专知

【分享】Java 9正式发布,9个新特性解读

转自:开源中国, www.oschina.net/translate/java-9-new-features Java 8 发布三年多之后,即将快到2017年7...

3325
来自专栏架构师小秘圈

设计和实现一款轻量级的爬虫框架

作者:王爵nice ,来自架构文摘(ID:ArchDigest) 说起爬虫,大家能够想起 Python 里赫赫有名的 Scrapy 框架, 在本文中我们参考这...

2745
来自专栏祝威廉

ElasticSearch Recovery 分析

org.elasticsearch.indices.cluster.IndicesClusterStateService.clusterChanged 被触发后...

753
来自专栏源哥的专栏

基于linux的嵌入IPv4协议栈的内容过滤防火墙系统(5)-包过滤模块和内容过滤模块所采用的各种技术详述

3。1 module编程 module可以说是 Linux 的一大革新。有了 module 之后,写 device driver 不再是一项恶梦,修改 ker...

783
来自专栏Java开发者杂谈

分布式改造剧集之Redis缓存踩坑记

1514
来自专栏逆向与安全

学习JVM虚拟机原理总结

1991年,在Sun公司工作期间,詹姆斯·高斯林和一群技术人员创建了一个名为Oak的项目,旨在开发运行于虚拟机的编程语言,允许程序多平台上运行。后来,这项工作就...

650

扫描关注云+社区