vlambda博客
学习文章列表

子查询引起的oracle数据库bug分析

[
环境配置
]


操作系统:linux7 ,数据库为12.1.0.1.3单机,未应用任何补丁。


[
报错现象
]

现象描述:

使用plsql连接数据库执行语句(语句见下图),并点击获取最后一页后抛出如下截图报错,客户端连接被中断。

执行的语句信息如下,查询语句含多个子查询:


[
报错分析
]


看到上述报错的第一反应是在alert日志中搜索ora-03113和ora-03114关键字,但并未搜索到。于是查看是否有其它ora-类报错抛出,发现有ora-07445类报错如下:

Fri Nov 06 11:10:06 2020

Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7FF39EC1C7FB] [PC:0x5A9A990, __intel_ssse3_rep_memcpy()+8784] [flags: 0x0, count: 1]

Errors in file /home/oracle/app/oracle/diag/rdbms/ems/ems/trace/ems_ora_48178.trc  (incident=112759):

ORA-07445: exception encountered: core dump [__intel_ssse3_rep_memcpy()+8784] [SIGSEGV] [ADDR:0x7FF39EC1C7FB] [PC:0x5A9A990] [Invalid permissions for mapped object] []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Fri Nov 06 11:10:07 2020

Sweep [inc][112759]: completed

Fri Nov 06 11:12:11 2020

Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x7FE493E637FB] [PC:0x5A9A990, __intel_ssse3_rep_memcpy()+8784] [flags: 0x0, count: 1]

Errors in file /home/oracle/app/oracle/diag/rdbms/ems/ems/trace/ems_ora_50919.trc  (incident=112786):

ORA-07445: exception encountered: core dump [__intel_ssse3_rep_memcpy()+8784] [SIGSEGV] [ADDR:0x7FE493E637FB] [PC:0x5A9A990] [Invalid permissions for mapped object] []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Fri Nov 06 11:12:14 2020

Sweep [inc][112786]: completed

进一步分析trc并查询mos发现堆栈信息符合Bug20916723 - Dump/Wrong results with adaptive plan and correlatedsubqueries sharing same correlated column。


主要堆栈信息:

evanvl<- evaopn2 <- subecv <- subsr3 <- evaopn3 <-evaopn2 <- subsr1 <- subsr3 <- evaopn3 <- kpofcr <-qerghFetch <- opifch2 <- opifch <- opiodr <- ttcpip <-opitsk <- opiino <- opiodr <- opidrv <- sou2o <-opimai_real <- ssthrdmain <- main


[
解决方案
]


方案1.升级至12.2.0.1或者更高版本


方案2.使用workaround:


SQL> conn / as sysdba

SQL> alter system set optimizer_adaptive_features=false;


optimizer_adaptive_features即时生效,无需重启数据库。

本次选择解决方案2,optimizer_adaptive_features特性关闭后,再次执行上述sql,不再抛出报错,连接也未中断,确认解决方案有效。


[
总结
]


Adaptivestatisticsfeatures优化器自适应特性默认是打开的,该特性比较适合于一些OLAP类业务系统,因在这类系统中sql的执行时间较长,且sql比较复杂,优化比较麻烦,启用Adaptivestatistics features 可以带来好处。但在OLTP系统上建议关闭该特性,可以规避该特性引起的很多bug。


在12.1中optimizer_adaptive_features一个参数控制所有优化器自适应特性。在12.2中oracle引入了2个独立参数来控制optimizeradaptivefeatures(optimizer_adaptive_plans和optimizer_adaptive_statistics),目的应该是为了更加灵活的,独立管理新功能。