A Query Fails With ORA-4030 On "kkoutlCreatePh,logdef* : kkoabr"

刘泽宇

现象：
On Oracle 11.2.0.2, when attempting to run a large SELECT statement the following errors are raised:

ORA-04030: out of process memory when trying to allocate 188152 bytes (kkoutlCreatePh,logdef* : kkoabr)
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)=

From the incident trace the top memory consumption is in kkoutlCreatePh:

=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
95%   12 GB, 68061 chunks: "permanent memory          "  SQL
         kkoutlCreatePh  ds=0x2ad41a095230  dsprt=0x2ad4179a0200 <<<<< -- shows kkoutlCreatePh
4%  502 MB, 62493 chunks: "free memory               "
         top call heap   ds=0xb9203e0  dsprt=(nil)
0%   45 MB, 3349 chunks: "permanent memory          " SQL
         kxs-heap-c      ds=0x2ad4179a0200  dsprt=0xb9203e0
0%   27 MB, 282863 chunks: "opndef: qcopCreateOpnViaM "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0
0%   20 MB, 94734 chunks: "kccdef : qcsvwsci         "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0
0%   16 MB, 427981 chunks: "chedef : qcuatc           "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0
0%   13 MB, 189042 chunks: "logdef: qcopCreateLog     "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0
0%   11 MB, 94809 chunks: "optdef: qcopCreateOptInte "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0
0% 7041 KB, 47437 chunks: "coldef: qcopCreateCol     "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0
0% 3933 KB, 95020 chunks: "idndef : qcuAllocIdn      "
         TCHK^f34ec23f   ds=0x2ad4179aff48  dsprt=0xb91f4e0


原因：
The cause of this problem has been addressed in
Bug 13354720 - ORA-4030 DUE TO HIGH KKOUTLCREATEPH MEMORY ALLOCATION
which has been closed as 'Not a Bug'.


处理方法：
The following workaround is confirmed to prevent the ORA-4030 errors on this memory heap type from occurring:

ALTER SESSION SET "_b_tree_bitmap_plans"=false;

This parameter can also be set at the instance level, in case multiple sessions run into the problem.

Another possible workaround is to set _NO_OR_EXPANSION=TRUE.
Note: This parameter can cause performance issues and needs to be tested first.