ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] 解析

ALLSTARS_ORACLE

ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] 解析


Alert Log的相关内容如下
Sat Aug 13 07:08:10 2011
Errors in file /db164/app/oracle/admin/mpvs/udump/mpvs_ora_504430.trc:
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFD0] [] []

相关trace文件内容如下
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /db164/app/oracle/product/10.2.0
System name:        AIX
Node name:        pdccsmpvs0dbs01
Release:        3
Version:        5
Machine:        00C7D7E64C00
Instance name: mpvs
Redo thread mounted by this instance: 1
Oracle process number: 158
Unix process pid: 504430, image: oracle@pdccsmpvs0dbs01

*** 2011-08-13 07:08:10.455
*** ACTION NAME:() 2011-08-13 07:08:10.454
*** MODULE NAME:(JDBC Thin Client) 2011-08-13 07:08:10.454
*** SERVICE NAME:(SYS$USERS) 2011-08-13 07:08:10.454
*** SESSION ID:(1321.48005) 2011-08-13 07:08:10.454

*** 2011-08-13 07:08:10.455
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFD0] [] []
Current SQL statement for this session:
CUST_ID IN ('390190000010872','390190000034405','390190000039802','390190000043717','390190000051730','390190000054126','390190000058014','390190000062039','390190000064040','390190000064363','390190000068835','390190000073022','390190000089216','390190000092447','390190000094063','390190000118875','390190000127046','390190000144096','390190000266512','390190000271273','390190000272445','390190000279248','390190000279451','390190000469294','390190000470672'………………..
其中in的硬编码值的列表有49万多个

问题分析

这是因为语句in的硬编码值过多引起的。
这样的语句解析将消耗的很多的shared_pool中的空间，从而导致系统异常，并且可能导致shared pool空间耗尽，共享池中大量语句被清出，进而导致ORA-4031异常
应用应该避免此类语句的出现


建议和意见
请应用改写语句，如果in的硬编码值的超过100个，请将这些值存放到某个表，比如T_CUST_ID当中，那么上述语句可以改写为类似以下语句，避免类似情况的出现

goodwzb

我记忆中where xxx in (......)顶多1000个，怎么会有49万之多？这是哪个参数可以调整的吗？

wengtf

goodwzb 发表于 2013-12-18 10:21
我记忆中where xxx in (......)顶多1000个，怎么会有49万之多？这是哪个参数可以调整的吗？ ...

以前也碰到这个问题，长期关注！

weiranth

in 49W 个，这也行啊？？