ORA-4031 관련

 

1      ORA-4031 관련 참고 MOS문서 리스트

1.1      Indexing Fails with the Exception Ora-04031 Unable To Allocate 32 Bytes Of Shared Memory and kglHeapInitializetemp (Doc ID 1449691.1)
Master Note for Diagnosing ORA-4031 (Doc ID 1088239.1)

1.1.1     원인
- 메모리 부족으로 인하여 발생한 에러
- SGA 구성 요소가 워크로드에 비해 너무 작은 경우 발생
- APP 설계 단에 문제로 인하여 메모리내 단편화로 인해 에러 발생
- 메모리 할당에 대한 버그

1.1.2     솔루션
- MEMORY_TARGET, SGA_TARGET이 워크로드를 수용하기에 충분히 큰지 확인하고 수정하여야 많은 상황에 대처 가능하다.

1.2      How To Deal With SGA allocation forcing component growth Wait Events (Doc ID 1270867.1)

1.2.1     원인
- 'SGA: allocation forcing component growth' 로그 메시지 발생으로 인한 4031 발생 여부
- MEMORY_TARGET 및 SGA_TARGET을 사용하는 경우에 발생 할 수 있는 메시지이다.
- 위 2개의 파라미터는 SGA영역 안에 세부 영역을 자동으로 관리하면서, 필요한 세부 영역에 메모리 공간으로 메모리 공간들이 이동하게 되는 동작을 수행한다, 이 과정에서 사용하는 인스턴스에 비해 TARGET 값이 너무 작게 설정 되어 있는 경우, 메모리 이동이 빈번하게 발생하여, 에러가 발생할 수 있다.

1.2.2     솔루션
- 메모리 값을 증가시켜 다른 세부영역간에 메모리를 stealing 현상을 줄일 수 있다.

1.3      ORA-4031 unable to allocate 4160 bytes of shared memory (shared pool,unknown object,sga heap(4,0),modification ) (Doc ID 1675470.1)

1.3.1     원인
- Shared pool 안에 Sub pool에 heap memory 획득에 있어 문제가 발생 할 수 있다.
- SGA라는 개념을 통해 Shared pool, buffer cache pool 등에 대한 영역이 서로 동적으로 메모리가 이동되면서 크기가 달라지는 매커니즘이 있는 것처럼 Shared pool 안에도 작은 Sub pool이 구성되어 동적으로 메모리 크기가 이동되면서, 정상적으로 동작하지만, 정상적으로 동작되지 않을 경우 개선사항이 필요하다.

1.3.2     솔루션
- 히든 파라미터 "_enable_shared_pool_durations"= false 설정 또는
- SGA_TARGET=0 설정
- 자동 공간 관리로 인한 메모리 이동의 기능을 제한하여 원인을 없애는 방안

 

1.4      Bug 20370037 - Shared Pool from KGLH0 constantly growing causing ORA-04031 and Latch contention (Doc ID 2119923.1)

1.4.1     원인
- latch contention 이슈로 인하여 Shared pool의 Sub 영역인 KGLH0의 영역이 지속적으로 증가하면서, 해당 KGLH0프로세스가 모든 메모리를 소비하여 메모리 부족 및 충돌이 발생하여 ORA-4031가 발생하게 된다.
- Oracle은 어떠한 이유로 KGLH0은 자식 커서에 메모리를 할당한 후 사용한 뒤, 이후 자식 커서를 재사용하지 않고, 더 많은 커서를 생성하도록 동작하며 결국 에러가 발생하게 된다.
- 이러한 에러의 경우 메모리 추가는 일시적인 증상 해결일 뿐 다시 메모리 사용률 100%에 도달하기 때문에 아래 솔루션을 검토해야 한다.
- 해당 이상 현상은 "Bug 20370037 - KGLH0 GROWTH LEADING TO ORA-4031" 에서 아직 개발 조사 중 이다.

1.4.2     솔루션

1.4.2.1     파라메터를 조정하여 아래 이슈 사항에 대한 내용과 연관된 파라메터를 수정한다.

가)              Open "Bug 20370037 - KGLH0 GROWTH LEADING TO ORA-4031"

①                     _optimizer_use_feedback = false /* disables the use of statistics feedback for subsequent executions. Default is True. */

②                     _optimizer_gather_feedback = false /* disables the gathering of execution feedback in the optimizer. Default is True. */

나)              "Bug 13456573 - Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing (Doc ID 13456573.8)" > adaptive cursor sharing 기능 제한

①                     _optimizer_extended_cursor_sharing = none   or   _optimizer_extended_cursor_sharing_rel = none

다)              "Adaptive Query Optimization (Doc ID 2031605.1)"

①                     optimizer_adaptive_features = false /* disables all adaptive features */
 

라)              "High Amount Of Shared Memory Allocated Into KGLH0 Heap In 12.1.0.2 (Doc ID 2096561.1)"
> 필요 하지 않은 상위커서에 대하여 자식커서의 양을 제한하는 값

①                     by setting the parameter "_cursor_obsolete_threshold" to a lower value than the default one (1024):
alter system set "_cursor_obsolete_threshold"=200 scope=both;

1.4.2.2     파라메터 조정이 아닌 해당 패치 적용 > Apply patch 20370037 and if this patch is not available for your platform/release then request a backport of Bug 20370037

1.5      ORA-04031 Errors Occurring with High ges resource dynamic & ges enqueues Memory Usage In The Shared Pool (Doc ID 2063751.1)

1.5.1     원인
Bug: 21373473에 보고된 것으로 DX(Distributed Transaction) 및 BB Lock이 캐시 되지만 지워지지 않기 때문에 발생한 것이며 해당 버그의 수정사항은 DX 및 BB lock을 캐시 할 필요가 없도록 동작하여 RAC 인스턴스에 전체 Shared pool 메모리 사용량을 줄이도록 한다, 결과적으로 ges resource dynamic" 및 "ges enqueues" 영역의 메모리 할당 및 사용률이 높아 발생

1.5.2     솔루션

1.5.2.1     Patch 21373473을 적용, 12.1.0.2를 사용하는 경우 Patch 21260431도 적용해야 한다.

1.5.2.2     _GES_DIRECT_FREE_RES_TYPE = "CTARAHDXBB"를 설정해서도 해결 가능

1.6      Size of State Objects (ktlbk) Keeps Increasing Till 4031 is Thrown (Doc ID 1638733.1)

1.6.1     원인
각각의 트랜잭션은 통계 객체를 생성할 수 있으며, 이런 동작들은 예상된 동작이고 정해진 매커니즘

1.6.2     솔루션
이런 동작을 통해 Shared pool이 부족하다면 증가 시켜줘야 하며, 생성된 통계 객체는 재사용 될 수 있기 때문에 트랜잭션이 활성화 될 때마다 지속적인 생성이 되는 것은 아닌 것 같습니다.

1.7      SHARED POOL 또는 다른 메모리 POOL 에서의 ORA-04031 에러 진단 및 해결 (Doc ID 2238073.1)

1.7.1     원인 (주요 원인 중 1개가 될 수 있음)
- ORACLE BUG
- 어떠한 세션이 처리를 위해서 연속적인 메모리 할당을 받아야 되는 경우, 이러한 상황에서 연속적인 공간을 할당 해주기 위해 쓰이는 예약 영역인 SHARED_POOL_RESERVED 영역의 SIZE 문제를 파악해보는 부분
- 작은 Shared pool 용량
- 충분히 큰 Shared pool 용량에서 단편화를 일으키는 요인으로 에러 발생
- Large Pool의 구성

1.7.2     솔루션
ORA-04031에 대한 가이드 되는 내용으로 해당 문서를 확인하는 것이 더욱 정리가 잘 되어 있습니다.

1.8      ORA-4031 에러에 대한 해결과 분석 [Video] (Doc ID 1532616.1)

1.8.1     솔루션
ORA-4031에 대한 구조적인 측면의 이해를 돕는 문서로 참고하시면 될 것 같습니다.

1.9      ppa Processes - Slow Query Performance in RAC (Doc ID 2304268.1)

1.9.1     원인
- Pnnn 병렬 쿼리 슬레이브 프로세스로, SQL문을 병렬 실행한 경우 해당 프로세스가 활성화 되고, 12.2 버전과 비교 해보았을 때 12.1버전에서 쿼리가 70% 더 시간이 소요되어 느린 부분이 발견
- 해당 문서를 참고한 이유는 유진투자선물 12.1 버전 사용 중이며 초기 ORA-4031가 발생했을 때 해당 ppa프로세스에 의해 내부적인 쿼리가 돌면서 발생하면서 순차적으로 업무 쿼리의 에러가 발생 한 점에 있다.

1.9.2     솔루션
- Patch 25036065 적용

1.10    Bug 27223075 - Wait for 'PX Deq Join Ack' when no active QC but PPA slaves show as busy (Doc ID 27223075.8)

1.10.1   원인
- 1.9 항목과 동일한 내용으로 검토한 내용이며 12.1버전에 PPA 프로세스에 대한 버그가 식별된 부분이 있음

1.11    High CPU Usage andor Frequent Occurrences of ORA-12850 or ORA-12751 For Monitor Queries by MMON From 12.1 (Doc ID 2102131.1)

1.11.1   원인
- 내부 자동화 기능으로 인한 첫 ORA-4031 발생으로 12.1에 대한 auto task 수행된 이력을 보았을 때 평일 장애 발생 이후 그 주 주말에 automatic report capture framework가 수행된 것이 확인되었다.
- 해당 문서를 참조해보면 12c 부터 위 기능이 출시 되었으며, 12.1 버전에서 해당 기능에 대한 cpu사용률이 올라감에 따라 Adaptive Optimization에 의해 다르게 동작하면서 문제가 발생.
- ORA-4031 기능 관련 히든파라미터 조정되는 내용을 보면 Adaptive Optimization기능을 비활성화 하는 부분이 있어 어느정도 일치되는 내용이 보임

1.11.2   솔루션

1.11.2.1   alter system set "_report_capture_cycle_time"=0; /* Default is 60 seconds */

1.11.2.2   또는 OS에서 MMON SLAVE를 강제 종료, 여러 개인 경우 모든 SLAVE 종료

2     ORA-4031 관련 조회 쿼리

2.1      Shared pool 단편화 관련 조회 1

x$ ksmsp 는 래치를 획득해야 하므로 래치경합을 일으킬 수 있습니다.

-- Fragmentation of Shared Pool.
set verify off
column PctTotSPMem for all
select ksmchcls "ChnkClass",
    sum(ksmchsiz) "SumChunkTypeMem",
    Max(ksmchsiz) "LargstChkofThisTyp",
    count(1) "NumOfChksThisType",
    round((sum(ksmchsiz)/tot_sp_mem.totspmem),2)*100||'%' “PctTotSPMem"
from x$ksmsp,
    (select sum(ksmchsiz) TotSPMem from x$ksmsp) tot_sp_mem
group by ksmchcls, tot_sp_mem.TotSPMem
order by sum(ksmchsiz);

SumChunkTypeMem: Shared Pool의 각 클래스별 할당 되어 있는 메모리 합
LargstChkofThisTyp: Shared Pool의 각 클래스별 연속적인 공간을 할당 받은 Chunk 중 가장 크기가 큰 메모리
NumOfChksThisType: Shared Pool의 각 클래스별 할당 되어 있는 Chunk 개수
PctTotSPMem: 사용률 현황

 

KSMCHCLS:  CHUNK의 CLASS
      (free : free, freeable : freeable, perm : permanent,
       recr : recreatable)
KSMCHSIZ:  CHUNK의 사이즈
KSMCHCOM:  CHUNK에 대한 속성을 나타내는 간단한 text comment
KSMCHPTR:  메모리 상에서 LOCATION에 대한 HEX value

참고 사이트
https://logicalread.com/2013/04/21/oracle-11g-shared-pool-fragmentation-mc02/#.XqkmM2gzZPY
https://m.blog.naver.com/PostView.nhn?blogId=koalagon&logNo=110120472676&proxyReferer=https:%2F%2Fwww.google.com%2F

2.2      Shared pool 단편화 관련 조회 2 (3.1 항목과 비슷한 내용)

x$ ksmsp 는 래치를 획득해야 하므로 래치경합을 일으킬 수 있습니다.
SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY ksmchcls;
  * 해당 쿼리 조회 시 아래 내용 검토
 - free memory(SIZ)가 5mb이하로 낮을 경우 Shared_pool_size, Shared_pool_reserved_size를 늘려야 할 수 있다.
 - perm이 지속적으로 늘어나면 시스템 메모리 누출로 볼 수 있다.
 - freebl 및 recr이 항상 큰 경우에는, 해제되지 않는 많은 커서 정보가 저장되어 있음을 나타낸다.
 - free가 크지만 여전히 ORA-4031가 발생 하는 경우 단편화를 의심해봐야 한다.

2.3      Shared pool 단편화 관련 조회 3

x$ ksmsp 는 래치를 획득해야 하므로 래치경합을 일으킬 수 있습니다.

select * from X$KSMLRU where KSMLRSIZ > 0;

해당 딕셔너리 뷰는 Shared pool에서 비 할당된 메모리Chunk에서 메모리 할당이 이루어진 마지막 쿼리를 보여주기 때문에 10줄 이상 조회되지 않으므로, 모니터링 또는 로깅 하기 위해선 spool을 이용해야 한다.
해당 쿼리는 때로 계속해서 메모리를 요청하는 세션을 찾는데 사용되기도 한다.
X$KSMLRU를 조회했을 때 KSMLRNUM 값이 큰 수치를 보이는 경우 해당 object 할당으로 인해 flush 된 object의 개수가 그 만큼 높다는 이야기이고 이와 관련하여 할당받은 연속적인 공간인 Chunk크기는 KSMLRSIZ를 참조하면 된다. 결국 이런 Procedure, function은 keep명령어를 통해 보관해두는 것이 좋다.

KSMLRSIZ:  allocate된 연속된 memory size.
               이 크기가 5K가 넘으면 문제될 소지가 있다고 보고,
               10K가 넘으면 심각한 문제가, 20K가 넘으면 매우 심각한
               문제를 야기할 수 있으므로 주의가 필요하다.
KSMLRNUM:  이 object의 할당으로 인하여 flush되었던 object의 갯수.
KSMLRHON:  load되고 있는 object의 이름.(PL/SQL or a cursor)
KSMLROHV:  load되고 있는 object의 hash value.
KSMLRSES:  이 object를 load한 session의 SADDR 값.

참고문서
VIEW: X$KSMLRU - LRU flushes from the shared pool - (7.3 - 8.1) (Doc ID 43600.1)
문제해결:공유 풀과 라이브러리 캐쉬 래치 경합 튜닝 (Doc ID 1540786.1)

2.4      Shared pool 단편화 관련 조회 4

SELECT '0 (<140)' bucket, ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10) "From",
       COUNT(*) "Count", MAX(ksmchsiz) "Biggest",
       TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz<140
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10)
UNION ALL
SELECT '1 (140-267)' bucket, ksmchcls, ksmchidx,20*TRUNC(ksmchsiz/20),
       COUNT(*), MAX(ksmchsiz),
       TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 140 AND 267
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 20*TRUNC(ksmchsiz/20)
UNION ALL
SELECT '2 (268-523)' bucket, ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50),
       COUNT(*), MAX(ksmchsiz),
       TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 268 AND 523
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50)
UNION ALL
SELECT '3-5 (524-4107)' bucket, ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500),
       COUNT(*), MAX(ksmchsiz) ,
       TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 524 AND 4107
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500)
UNION ALL
SELECT '6+ (4108+)' bucket, ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000),
       COUNT(*), MAX(ksmchsiz),
       TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz >= 4108
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000);

위 쿼리를 조회했을 때 대부분의 공간이 리스트의 최상위 부분에 위치해 있다는 것은 아주 작은 메모리 공간만 남아 있다는 의미이며, 이 경우 에러가 극심한 단편화 현상으로 인해 발생 한 경우다.
x$ ksmsp 는 래치를 획득해야 하므로 래치경합을 일으킬 수 있습니다.

 

2.5      Shared pool의 sub pool 조회
Shared pool의 Sub 영역에 대한 세부사항을 조회
-- sub pool1
select * from v$sgastat where pool='shared pool';

-- sub pool2
WITH X AS (
SELECT KSMDSIDX AS POOLIDX,
         'shared pool' AS POOL,
         KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
         SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
         SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
    FROM SYS.X$KSMSS
   WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
     , TRUNC(BYTES/1024/1024,2) AS MB
     , TRUNC(100*BYTES/SP_BYTES,2) AS PCT
  FROM X
 ORDER BY POOLIDX, PCT DESC;

2.6      리터럴 SQL 조회
v$sqlarea는 래치를 획득해야 하므로 래치경합을 일으킬 수 있습니다.
SELECT substr(sql_text,1,40) "SQL",
      count(*) ,
      sum(executions) "TotExecs"
 FROM v$sqlarea
 WHERE executions < 5
 GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
 ORDER BY 2 ;

 

2.7      동일 SQL이 여러 개 Version으로 메모리에 Load 된 경우 조회

SELECT address, hash_value,
                version_count ,
                users_opening ,
                users_executing,
                substr(sql_text,1,40) "SQL"
          FROM v$sqlarea
         WHERE version_count > 10;

SQL의 문자는 동일하지만, 기본 오브젝트 또는 바인드 등에 의하여 version count가 발생하고, 공유SQL 이랑은 다른 개념이다. 높은 version count는 progression monitoring(V$SESSION_LONGOPS)에 의해 발생할 수도 있으며 _SQLEXEC_PROGRESSION_COST 파라미터를 0으로 설정하여 해제하면 문제를 회피할 수 있다.

참조문서
Init.ora Parameter "_SQLEXEC_PROGRESSION_COST" [Hidden] Reference Note (Doc ID 68955.1)

2.8      Shared pool을 많이 사용하는 SQL 조회

SELECT substr(sql_text,1,40) "Stmt", count(*),
                sum(sharable_mem)    "Mem",
                sum(users_opening)   "Open",
                sum(executions)      "Exec"
          FROM v$sql
         GROUP BY substr(sql_text,1,40)
        HAVING sum(sharable_mem) > &MEMSIZE;

MEMSIZE는 byte 단위로 Shared pool 크기의 10%로 설정, 조회한되는 경우 그 이하로 설정하여 조회한다.
이 SQL은 유사한 리터럴 구문이 있거나 한 구문에 대해 여러 버전이 있어 Shared pool에 많은 부분의 메모리를 할당 받은 구문을 찾는 경우에 사용 가능하다.

 

2.9      Shared Pool 부적절한 사이즈 및 단편화 관련 조회

select count (*)
from  V$SHARED_POOL_RESERVED
where
REQUEST_FAILURES > 0
and
LAST_FAILURE_SIZE > <the setting for _shared_pool_reserved_min_alloc>;

ORA-4031와 일치되는 케이스가 없다면 단편화로 발생한 케이스인지를 판단해보아야 하고 위 쿼리를 조회 했을 때 아래 규칙을 적용하여 단편화발생 여부를 검토한다.
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

X$KSMLRU뷰를 확인하여LRU를 지속적으로 발생시키는 할당사이즈가 4100~4400byte 범위라면 _shared_pool_reserved_min_alloc 사이즈를 줄여주는 것이 도움 될 수 있다. 해당 파라미터 값을 4100으로 설정하면 Shared Pool의 메모리 할당 요구를 만족시킬 개연성(연관성)을 증가시킨다. 디폴트 값은 4400byte로 만약 4100byte로 변경한다면 Shared Pool Size와 Shared Pool내 reserved size 증가와 연계하여 이루어져야 하며 reserved size는 일반적으로 shared pool의 5%에서부터 증가되며 15%~20% 범위로 설정해야 한다.