重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: A Query Fails With ORA-4030 On "kkoutlCreatePh,logdef* : kkoabr" [打印本页]

作者: 刘泽宇 时间: 2025-9-19 19:16
标题: 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.

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)