ORA-04031

ORA-04031

(1) 에러 메시지

[ora11@localhost ~]$ oerr ora 4031 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")" // *Cause: More shared memory is needed than was allocated in the shared pool. // *Action: If the shared pool is out of memory, either use the // DBMS_SHARED_POOL package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // initialization parameters SHARED_POOL_RESERVED_SIZE and // SHARED_POOL_SIZE. // If the large pool is out of memory, increase the initialization // parameter LARGE_POOL_SIZE.

(2) 원인

새로운 SQL에 대해서 파스를 수행하려면 힙 영역에 새로운 빈 공간을 할당해야 하며 빈 공간을 할당하기 위해서는 프리 리스트로부터 필요한 크기의 프리 청크를 찾아야 한다. 그런데 프리 리스트를 검색하고도 프리 청크를 찾지 못한다면 Shared pool LRU 리스트로부터 사용 가능한 청크를 찾아서 사용하게 된다.

만약 LRU 리스트에서도 필요한 크기의 사용 가능한 청크를 찾지 못하면 ORA-04031 에러를 발생시키고 SQL 파스는 실패한다.

예) 새로운 SQL 파스를 위해서 256바이트의 공간이 필요하다고 가정

① Shared pool 래치를 획득하고 프리 리스트로부터 256바이트의 프리 청크를 검색한다. 이 과정에서 래치를 획득하지 못하면 latch: shared pool 대기 이벤트를 발생시키며 획득 가능할 때까지 대기한다.

② 256바이트의 프리 청크를 찾았다면 해당 청크를 익스텐트에 할당한다. 만약 프리 리스트에 256바이트 크기의 프리 청크가 없어서 찾지 못했다면 더 큰 크기의 프리 청크를 검색한다.

③ 만약 256바이트보다 더 큰 400바이트의 프리 청크를 찾았다면 400바이트 프리 청크를 필요한 크기의 256바이트와 나머지 144바이트 크기로 쪼갠다.

④ 필요한 크기로 쪼개진 256바이트 청크는 익스텐트에 할당하고 나머지 144바이트는 다시 프리 리스트에 등록되어서 관리된다.

⑤ 2번 단계에서 모든 프리 리스트를 검색하고도 256바이트보다 큰 프리 청크를 찾지 못했다면 Shared pool LRU 리스트로부터 핀이 해제된(재사용 가능한) 청크 중에서 256바이트 이상의 크기를 갖는 청크를 찾아서 프리 리스트로 등록하고 3번 단계부터 진행한다.

⑥ 5번 단계에서 Shared pool LRU 리스트를 검색하고도 필요한 크기의 청크를 찾지 못하면 "ORA-4031 unable to allocate %s bytes of shared memory" 에러를 발생시키고 SQL 파스는 실패한다.

프리 리스트로부터 프리 청크를 검색하고 할당받기까지의 모든 단계에서 shared pool 래치를 획득해야 한다. 그런데 청크 할당과 해제가 빈번하게 반복되면 청크는 더욱 더 작게 쪼개져서 관리되어야 할 청크 수가 증가한다. 이것은 프리 리스트를 검색하는 시간을 증가시키고 shared pool 래치에 대한 경합을 증가시켜서 성능 저하를 유발하거나 ORA-4031 에러를 발생시키게 된다.

(3) 임시 조치 방법

1) Shared pool 초기화 : 거의 해결 되는 경우가 없습니다!!!!!!

SQL> alter system flush shared_pool;

=> Shared pool 내의 연속된 메모리 조각들을 하나의 조각으로 합쳐주는 역할.

=> 다른 SQL 정보 또한 Shared pool에서 제거하므로 해당 명령어를 수행한 후에 모든 SQL이 하드파싱을 수행하게 되어 성능 저하가 발생할 수 있음.

 

2) 패치 등을 고려

ORA-4031 에러는 오라클 버그로 등록된 부분이 있으므로 해당 오라클 버전을 확인하여 오라클 패치 적용 및 업그레이드 등을 고려.

 

3) 파라미터 설정

SHARED_POOL_RESERVED_SIZE 파라미터 설정을 통해 에러 감소.

 

SQL> SELECT owner, name, type FROM v$db_object_cache
2 WHERE sharable_mem > 10000
3 AND (type=‘PACKAGE’ or type=‘PACKAGE BODY’ or
4 type=‘FUNCTION’ or type=‘PROCEDURE’)
5 AND KEPT=‘NO’;

 

OWNER NAME TYPE
----- -------- ------------
SYS STANDARD PACAKGE
SYS STANDARD PACAKGE BODY
SYS DBMS_UTILITY PACAKGE BODY

 

Keeping Large Objects

------------------------

 Pin large packages in the library cache:

SQL> EXECUTE dbms_shared_pool.keep(‘package_name’);

SQL> SELECT owner, name, type FROM v$db_object_cache
2 WHERE sharable_mem > 10000
3 AND (type=‘PACKAGE’ or type=‘PACKAGE BODY’ or
4 type=‘FUNCTION’ or type=‘PROCEDURE’)
5 AND KEPT=‘NO’;

OWNER NAME TYPE
----- -------- ------------
SYS DBMS_UTILITY PACAKGE BODY

 

4) Large pool 설정

만약 병렬 프로세싱을 사용한다면 Large pool 설정으로 에러를 감소.

 

5) 커서 공유

 .. OPEN_CURSORS

.. CURSOR_SPACE_FOR_TIME

.. SESSION_CACHED_CURSORS

과도한 설정시 4031 에러 발생하는 경우 발생

.. CURSOR_SHARING

 

CURSOR_SHARING 파라미터를 설정함으로써 커서를 공유하게 함.

- EXACT : 정확히 동일한 SQL문장인 경우만 커서를 공유. 기본값.

- FORCE : SQL 문장은 같으며 리터럴 변수 값만 틀린 SQL에 대해 커서를 공유.

- SIMILAR : 오라클이 실행계획을 판단해서 성능이 저하되지 않는다고 판단될 때 FORCE 설정과 동일하게 동작.

그러나 FORCE나 SIMILAR로 설정한 경우 원하지 않는 실행계획 변화로 인한 성능 저하를 유발시킬 수 있으므로 운영 단계에서는 적용이 어려움.

(4) 근본적인 조치 방법

1) 하드 파싱을 많이 발생시키는 원인이 되는 리터럴SQL을 찾아서 바인드 변수를 사용하도록 변경해야 함.

이미 개발이 완료되어 운영되고 있는 단계에서는 프로그램에서 수행되고 있는 SQL을 수정하기가 쉽지 않기 때문에, 미리 앞전에 개발 단계에서 하드 파스를 유발하는 SQL들을 찾아서 수정하는 것이 중요.

또한 개발자들에 대한 교육을 통해서 개발 시 바인드 변수를 사용하도록 해야 함.

 

2) Prepared Statement의 사용을 통해 JDBC 프로그램 내의 리터럴 SQL을 제거.

 

3) 실제 shared pool size 가 작아서 발생하는 경우도 있음

 

4) 운영중 발생시 db restart 하는게 대부분 임

2011.05.17 22:16:34 (*.172.37.20)

관리자

요약

-----

ORA-04031는 latch: shared pool와 연관성이 있습니다.
latch: shared pool은 Freelist에 동일한 LCO 검색에 실패했을 경우 새로운 LCO를 생성하기 위해
적절한 Free Chunk를 확보할때까지 shared pool latch를 획득하게 됩니다.
최적 크기의 프리 청크가 존재하지 않으면 조금 더 큰 크기의 프리 청크를 찾아서 이를 split하여
사용하며 남은 청크는 다시 프리 리스트로 등록시키며, 모든 프리 리스트를 탐색하고도
적절한 크기의 프리 청크를 찾지 못하면 LRU 리스트를 탐색합니다.
LRU 리스트의 청크들은 현재 핀(pin)되지 않은 재생성가능한 청크들이며,
LRU 리스트를 탐색하고도 적절한 크기의 청크를 확보하지 못하면
shared pool 내의 여유 메모리공간을 추가적으로 할당하고 이것마저도 실패한다면 ORA-4031 에러가 발생합니다

이처럼 ORA-04031는 빈번한 하드 파싱에 의한 메모리 단편화 문제이며 해결방법으로는

1. 사이즈가 큰 PL/SQL 블럭의 사용을 자제하며, 불가피하게 사이즈가 큰 PL/SQL 오브젝트는
   DBMS_SHARED_POOL.KEEP을 사용하여 Library Cache에 고정한다

2. 가급적 SQL을 공유할 수 있도록 Bind SQL를 사용하거나, CURSOR_SHARING 파라미터 사용

3. shared_pool_reserved_size의 사이즈를 증가시켜 Large Chunk을 위한 공간을 할당한다

그리고 임시 조치방법으로는 instance 재기동 or flash shared pool 생각해 볼 수 있습니다

2011.05.18 06:10:06 (*.172.37.20)

관리자

v$sql 내의 Literal SQL이 많은지 확인한다.
많은 경우 Literal SQL을 사용하는 SQL을    찾아서 공유 할수 있도록 Bind Variable을 사용토록 하면 됩니다.
=> Literal SQL을 찾는 방법.
select substr(sql_text, 1, 40) "SQL",
count(*) cnt,
sum(executions) "TotExecs",
sum(sharable_mem) mem,
min(first_load_time) start_time,
max(first_load_time) end_time,
max(hash_value) hash        
from v$sqlarea
where executions < 5    --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30    --> 비슷한 문장이 30개 이상.
order by 2 desc;

2011.05.18 06:11:26 (*.172.37.20)

관리자

혹시 알려진 제품 문제에 해당 되지 않는지 확인 한다.

* BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs
for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172,
901 )
* BUG 1640583: ORA-4031 due to leak / cache buffer chain contention
from AND-EQUAL access. (Fixed: 8171,901 )
* BUG 1318267: INSERT AS SELECT statements may not be shared when they
should be if TIMED_STATISTICS. It can lead to ORA-4031. (Workaround:
_SQLEXEC_PROGRESSION_COST=0, Fixed: 8171, 8200)
* BUG 1193003: Cursors may not be shared in 8.1 when they should be
(Fixed: 8162, 8170, 901)