ORA-4031 조치 방법 과 DBMS_SHARED_POOL STORED PROCEDURE 사용법
==============================================================

Purpose
-------

다음과 같은 작업 수행 시 Oracle 이 Shared pool 에서 연속적인
메모리 부분을 찾지 못해 ORA-4031 에러를 발생시키는 것을 볼 수 있다.

. PL/SQL Routine
. Procedure 수행 시
. Compile 시
. Forms Generate 또는 Running 시
. Object 생성하기 위해 Installer 사용 시

본 자료에서는 이러한 에러에 대한 대처 방안을 설명 하고자 한다.

Problem Description
-------------------

Error 발생의 주된 원인은 Shared Pool의 사용 가능한 Memory 가 시간이
흐름에 따라 작은 조각으로 분할되어 진다는 것이다. 그래서 큰 부분의
Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가
발생한다. 즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도
충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.

1. Shared Pool과 관련된 인스턴스 파라미터
다음 3가지 파라미터는 본 자료를 이해 하는데 매우 중요하다.

* SHARED_POOL_SIZE - Shared Pool 의 크기를 지정 한다. 정수를 사용하며
"K" 나 "M" 을 덧붙일 수 있다.

* SHARED_POOL_RESERVED_SIZE - 공유 풀 메모리에 대한 대량의 연속 공간
요청에 대비해서 예약하는 영역의 크기를 지정한다. 이 영역을 사용하기
위해서는 SHARED_POOL_RESERVED_MIN_ALLOC 보다 큰 영역 할당 요청이어야
한다. 일반적으로 SHARED_POOL_SIZE 의 10% 정도를 지정한다.

* SHARED_POOL_RESERVED_MIN_ALLOC - 예약 메모리 영역의 할당을 통제한다.
이 값보다 큰 메모리 값이 할당 요청되었을 때 공유 풀의 free list 에
적합한 메모리 공간이 없으면 예약된 메모리 공간의 리스트에서 메모리를
할당해 준다. 이 값은 8i부터는 내부적으로만 사용된다.

Workaround
-----------
Re-start the instance

Solution Description:
---------------------
이 Error 해결방안을 살펴 보면 다음과 같다.

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

* BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs
for buffer handles. (Workaround: dbhandles_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:
SQLEXECPROGRESSION_COST=0, Fixed: 8171, 8200)
* BUG 1193003: Cursors may not be shared in 8.1 when they should be
(Fixed: 8162, 8170, 901)


2. Object를 Shared Pool에 맞추어 Fragmentation을 줄인다.
(Dbms_Shared_Pool Procedure 이용)

다음은 크기가 크고 빈번히 access되는 package들임.

standard packages
dbms_standard
diutil
diana
dbms_sys_sql
dbms_sql
dbms_utility
dbms_describe
pidl
dbms_output
dbms_job


3. Shared Pool 을 효율적으로 사용하도록 Application Program을 조절한다.


4. 메모리 할당을 조정한다.

우선 다음 쿼리로 library cache 문제인지 shared pool reserved space
문제인지 진단한다.

SELECT free_space, avg_free_size, used_space,
avg_used_size, request_failures, last_failure_size
FROM v$shared_pool_reserved;

만일 REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
SHARED_POOL_RESERVED_MIN_ALLOC
이면 ORA-4031 은 Shared Pool 의 연속 공간 부족의 결과이다.

해결책: SHARED_POOL_RESERVED_MIN_ALLOC 값을 증가 시켜서 shared pool
reserved space 에 올라가는 오브젝트의 수를 줄인다. 그리고
SHARED_POOL_RESERVED_SIZE 와 SHARED_POOL_SIZE 를 충분히 확보
해 준다.

만일 REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
SHARED_POOL_RESERVED_MIN_ALLOC
이거나
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is <
SHARED_POOL_RESERVED_MIN_ALLOC
이면 ORA-4031 은 library cache 내의 연속된 공간 부족의 결과 이다.

해결책: SHARED_POOL_RESERVED_MIN_ALLOC 을 줄여서 shared pool reserved
space 를 보다 쉽게 사용할 수 있도록 해준다. 그리고 가능하면
SHARED_POOL_SIZE 를 증가시킨다.


5. DBMS_SHARED_POOL STORED PROCEDURE 사용법

이 stored package는 dbmspool.sql을 포함하며 7.0.13 이상 version에서 사용
가능하다. 이는 다음과 같이 3가지 부분으로 나누어 진다.

Procedure sizes(minsize number):
-> Shared_Pool_size 안에서 정해진 Size 보다 큰 Object를 보여준다.

Procedure keep(name varchar2, flag char Default 'P'):
-> Object (Only Package)를 Shared Pool 에 유지한다. 또한 일단 Keep한
Object는 LRU Algorithm에 영향을 받지 않으며
"Alter System Flush Shared_Pool" Command 에 의해 Package 의 Compiled
Version 이 Shared Pool에서 Clear되지 않는다.

Procedure unkeep(name varchar2):
-> keep() 의 반대 기능이다

이 Procedure들과 사용법에 대해 보다 더 자세한 정보를 위해서는
$ORACLE_HOME/rdbms/admin/dbmspool.sql script 또는 오라클 레퍼런스
매뉴얼을 참조하기 바람.


Reference Documents
-------------------
Diagnosing and Resolving Error ORA-04031.