ora 4031 에러 처리 방안

9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의 CPU를 효과적으로 사용하기 위해
하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있다.
이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도  이를 잘 활용하지 못해서 발생하는 경우가 발행할 수 있다.
이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 발생할 수 잇다.

현재 시스템이 Multi CPU인 경우에는 아마도 이 값이 1보다 큰 값으로 정의되어 있을 것이다..

그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서  _kghdsidx_count=1로 지정한다.

 

select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y
where x.indx = y.indx and x.ksppinm like '_kghdsidx_count%' escape ''
order by x.ksppinm;

 

 

SELECT * FROM v$sqltext;
SELECT substr(sql_text, 1, 40) AS “SQL”,
count(*) AS cnt,
sum(executions) AS “TotExecs”,
sum(sharable_mem) AS mem,
min(first_load_time) AS start_time,
max(first_load_time) AS end_time,
max(hash_value) AS hash
from v$sqlarea
where executions < 5 --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30 –> 비슷한 문장이 30개 이상.
order by 2 desc;
프로그램 시작 (세션 시작시)
alter session set cursor_sharing=force;
…
프로그램 작성
…
프로그램 종료 (세션 종료시)
alter session set cursor_sharing=exact;
강제로 메모리 정리
alter system flush shared_pool;
..

=======================================================================================
-----에러내용
ORA-04031: 4064 바이트의 공유 메모리를 할당할 수 없습니다 ("shared pool","select * from fm_org_chg_inf...","sga heap(2,0)","kglsim heap")

 

-----원인

Duplicate entries for SHARED_POOL_SIZE and JAVA_POOL_SIZE.

SGA_TARGET is set and also duplicate entries in parameter file found as below

"shared_pool_size" and "<SID>.__shared_pool_size"
    "java_pool_size" and "<SID>.__java_pool_size"
Upgrade fails with ORA-04031, When both SGA_TARGET and  Manual shared Memory Parameters

 

 

-----해결방안

1- Remove either of the entry of SGA_TARGET and Manual shared Memory Parameters from pfile
2- If SGA_TARGET is removed, then make sure <SID>.__parameters also removed.
3- Make sure SHARED_POOL_SIZE and JAVA_POOL_SIZE is more than 200 MB as mentioned in Note 376612.1 "ORA-04031: unable to allocate xxxx bytes of shared memory" during upgrade to 10gR2

 

---사견

SHARED_POOL_SIZE를 늘리라는 얘기를 하는데.

SGA_TARGET잡은 상태에서는 SHARED POOL, DATA CACHE는 바꿀수가 없습니다.

그렇게 할려면 SGA_TARGET라는 파라메터를 쓰지 말라는 말씀입니다..^^

다시말하면 오라클 버그라는 말이지요..망할 오라클..

 

아래 쿼리를 수행해서 DB_CACHE_SIZE,SHARED_POOL_SIZE가 SGA_TARGET 잡힌 상태에서

얼마나오는지 보고 수동으로 잡는게 좋을거 같습니다.

 

 

alter system set sga_target = 0 ;
ALTER SYSTEM SET DB_CACHE_SIZE=3791650816 ;
ALTER SYSTEM SET SHARED_POOL_SIZE=3892314112;

 

col NAME for a50
col VALUE for a50
set linesize 120
set pagesize 100
select nam.KSPPINM name,val.ksppstvl value
from x$ksppi nam, x$ksppsv val where
nam.indx=val.indx and nam.ksppinm like '%shared%' order by 1;

col NAME for a50
col VALUE for a50
set linesize 120
set pagesize 100
select nam.KSPPINM name,val.ksppstvl value
from x$ksppi nam, x$ksppsv val where
nam.indx=val.indx and nam.ksppinm like '%cache%' order by 1;

[출처] ORA-04031|작성자 5racle

이 에러의 원인은 크게 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>

[출처] ORA-04031|작성자 메치니