starland

이 에러의 원인은 크게 3가지로 나뉘어 진다.

1. 다수의 사용자로 인한 SharedPoolSize부족문제

2. 구동중인 App에 비해 현저히 부족한 SharedPool사용으로 인한 문제

3. 덩치 큰 SQL 구동을 위한 연속된 SharedPool할당 불가로 인한 문제

이중 1,2는 같은 맥락에서 접근할 수 있으므로 크게 2가지라고 볼 수도 있다.

이 문제는 OTN 에서 꽤나 유명한 에러로서 아래와 TechBulletin에는 아래와 같이 언급되어 있다.

No. 10095

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: _db_handles_cached=0, Fixed: 8172,901 )
* BUG 1640583: ORA-4031 due to leak / cache buffer chain contentionfrom 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)


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. 

위의 내용과 관련하여 SharedPoolSize 조절 방법은 아래와 같다.

이미 공지의 사실이지만 서두로 언급하면 Oracle은 Background Process와 SGA영역으로 구분된다.

그중 SGA는 SharedPool과 RedoLogBuffer, BufferCache로 이루어져 있다. 

이중 SharedPool은 SQL Area와 Data Structure로 이루어져 있다.

SharedPool Size를 산정하는 방법은 아래와 같다.

계산 공식　　　　　　　　 

Session당 최대메모리사용량(Max Session Memory) * 동시 접속하는 User의 수　 
+　 Shared SQL 영역으로 사용되는 메모리양　　　　　　　　　 
+　 Shared PLSQL을 위해 사용하는 메모리 영역　　　　　　　　　　 
+　 최소 30%의 여유 공간　

계산 예제　　　　　　　　　 

　 (1) 적당한 user session에 대한 session id를 찾는다.　　　　　　　　 
　　　　　　　　　 
　　　　　　　 SQLDBA>　 select sid from v$process p, v$session s　　　　　　　　　　 
　　　　　　　　　　　　 where p.addr=s.paddr and s.username='SCOTT';　　　　　　　　　 

　　　　　　　　　　　　　 SID　　　　　　　　　 
　　　　　　　　　　 ----------　　　　　　　　　 
　　　　　　　　　　　　　　 29　　　　　　　　　 
　　　　　　　 1 rows selected.　　　　　　　　　 

　 (2) 이 session id에 대한 maximum session memory를 찾는다.　　　　　　　　 
　　　　　　　　　 
　　　　　　　 SQLDBA> select value from v$sesstat s, v$statname n　　　　　　　　　　 
　　　　　　　　　　　 where s.statistic# = n.statistic#　　　　　　　　　　 
　　　　　　　　　　　 and n.name = 'session uga memory max' and sid=29;　　　　　　　　　 

　　　　　　　　　　 VALUE　　　　　　　　　　　　　　 
　　　　　　　　　　 -----------　　　　　　　　　 
　　　　　　　　　　　 273877　　　　　　　　　　　　　　　　　 
　　　　　　　 1 rows selected.　　　　　　　　　 
　　　　　　　　　 
　 (3) Total shared SQL area를 구한다.　　　　　　　　 
　　　　　　　　　 
　　　　　　　 SQLDBA>　 select sum(sharable_mem) from v$sqlarea;　　　　　　　　　 

　　　　　　　 SUM(SHARAB　　　　　　　　　 
　　　　　　　 ---------------------　　　　　　　　　 
　　　　　　　　　　　　　　 8936625　　　　　　　　　 
　　　　　　　 1 row selected.　　　　　　　　　 
　　　　　　　　　 
　 (4) PLSQL sharable memory area를 구한다.　　　　　　　　　 
　　　　　　　　　 
　　　　　　　 SQLDBA>　 select sum(sharable_mem) from v$db_object_cache;　　　　　　　　　 

　　　　　　　 SUM(SHARAB　　　　　　　　　 
　　　　　　　 ------------------　　　　　　　　　 
　　　　　　　　　　　 4823537　　　　　　　　　 
　　　　　　　 1 row selected.　　　　　　　　　 
　　　　　　　　　 
　　　　　　　　　 
　 (5) Shared pool size를 계산한다.　　　　　　　　　 
　　　　　　　　　　　　　　　　　 
　　　　　　　　　　　　 274K shared memory　 *　 400 users　　　　　　　　　 
　　　　　　　 +　　　　　 9M Shared SQL Area　　　　　　　　　　　　　　 
　　　　　　　 +　　　　　 5M PLSQL Sharable Memory　　　　　　　　　　 
　　　　　　　 +　　　　　 60M Free Space (30%)　　　　　　　　　　　　　　 

　　　　　　　 =　　　 184M Shared Pool　　　　　　　　　　　　 
　　　　　　　　　　　　　　　　　 
　　　　　　　　　　 
　　 이 예제에서는 Shared pool의 size는 184M가 적당하다고 할 수 있다. 이때 Free Space(60M) 계산 방법은 전제 184 M 에 대한 30 % 정도의 추정치 이다.　　　　　　　　 
　　　　　　　　　 
Shared Memory부족 (ORA-4031)에 대한 대처

　다음과 같은 방법으로 에러를 피해 갈 수 있다.- "Sys.dbms_shared_pool.keep" procedure사용.　　　　　　　　 

[참고] 위 package를 사용하려면 ?/rdbms/admin/dbmspool.sql,prvtpool.sql를 수행시켜 package를 create시킨 후 사용한다.　　　　　　　　 
　　　 (자세한 사항은 bulletin 10095,Oracle7 Server Tuning 4장12를 참조한다.)　　　　　　　　　 
　　　　　　　　　 

ORACLE_HOME/dbs/initSID.ora에 보시면 Processes=???값과 sessions=?????라는 
값에 좌우가 되는데 시스템에서 허락되는 
User별 process값이 있고 이 범위내에서 허용이 된다. 
SQL> show parameter process; 

NAME TYPE VALUE 
------------------------------------ ------- ------------------------------ 
aq_tm_processes integer 0 
db_writer_processes integer 1 
job_queue_processes integer 0 
log_archive_max_processes integer 1 
processes integer 300 
SQL> show parameter session; 

NAME TYPE VALUE 
------------------------------------ ------- ------------------------------ 
java_max_sessionspace_size integer 0 
java_soft_sessionspace_limit integer 0 
license_max_sessions integer 0 
license_sessions_warning integer 0 
mts_sessions integer 330 
session_cached_cursors integer 0 
session_max_open_files integer 10 
sessions integer 335 
SQL>