Ora-07445[Qcsanalyzebooleanexpr()]

今日巡检遇到了Ora-07445[Qcsanalyzebooleanexpr()]错误，trace文件除了发现上篇的waring提示，就是该错误涉及的SQL了，查看MOS：Ora-07445[Qcsanalyzebooleanexpr()] may occur due to Large Size SQL Query (Doc ID 417784.1)，可以确定是由于SQL的长度超过了限制引起，这是一个未公开的Bug 2837580引起。

SYMPTOMS
The ORA-07445:[qcsAnalyzeBooleanExpr()] When Number of literals in the Sql Text is > ~35,000
Call Stack :
ksedmp ssexhd sigacthandler qcsAnalyzeBooleanExpr
复制

 
原因：

This Problem is due to an Unpublished Bug 2837580
Abstract: Sigsegv In Server Process With Large Query, Larger Than Documented Limit Of 64KB
复制

 
处理办法：

The Bug has been fixed in 11g RDBMS Release
To resolve this Error, Please do any of the Following
1. Apply 10.2.0.4 patchset 
or 
10.2.0.3 Patch 13 or higher on Windows platform
2. Upgrade the DB to 11g 
Or
3. Workaround:
    1. Reduce the size of SQL query .
    2. Put the long list of numbers in another table and convert the query with IN condition.
You may be able to work around the problem by re-writing the query. 
Just put the long list of numbers in another table,and convert the IN condition.
For example, create a table as follows:
create table ID_LIST1 ( ID number );
Then insert the numbers 138848, 138849, 138850, etc. into that table.
Then change the IN condition above as follows:
or D1.DGID in (select ID from ID_LIST1)
That should help you to substantially reduce the size of the original 2.7 MB query.
复制