<Memory Parameters>

* The smallest chunk on memory: 4M bytes (must be a multiple of four)

Parameter

Example

MEMORY_MAX_TARGET *

 

1G

 

MEMORY_TARGET

If > 0, AMM(Automatic Memory Management)

 

 

SGA_MAX_SIZE *

 

800M

 

SGA_TARGET

If > 0, ASMM(Automatic Shared Memory Management)

600M

Try to use this amount

SHARED_POOL_SIZE

Mandatory If SGA_TARGET is not set

200M

The sum of these parameters must be less than SGA_TARGET

DB_CACHE_SIZE

200M

LOG_BUFFER *

16M

LARGE_POOL_SIZE

Optional

16M

JAVA_POOL_SIZE

Optional

64M

PGA_AGGREGATE_TARGET

PGA memory allocated to all server processes

200M

 

 * Static parameter: It is needed to restart database to reset the parameter.

 ** Dynamic parameter값변경시설정된SGA_TARGET을넘어서는값으로설정하려할경우에러는발생하나,

     최대한으로변경할수있는값으로Oracle이값을변경시킴. Decrease가능함.

--SGA_MAX_SIZE is 400M
SQL> SHOW parameter pool
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big INTEGER 64M
large_pool_size                      big INTEGER 16M
olap_page_pool_size                  big INTEGER 0
shared_pool_reserved_size            big INTEGER 6710886
shared_pool_size                     big INTEGER 128M
streams_pool_size                    big INTEGER 0
 
SQL> ALTER system SET large_pool_size=48m;
System altered.
 
SQL> ALTER system SET shared_pool_size=160m; 
ALTER system SET shared_pool_size=160m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified VALUE IS invalid
ORA-04033: Insufficient memory TO grow pool
 
SQL> SHOW parameter pool
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big INTEGER 64M
large_pool_size                      big INTEGER 48M -- increased by 48M
olap_page_pool_size                  big INTEGER 0
shared_pool_reserved_size            big INTEGER 6710886
shared_pool_size                     big INTEGER 152M -- increased by 152M 
streams_pool_size                    big INTEGER 0
 

 

<Errors related to memory>

ORA-4031 unable to allocate %s bytes of shared memory(\"%s\",\"%s\",\"%s\",\"%s\")

- Parameter 정보: 발생영역, 오브젝트명, 컴포넌트, 내부적으로수행한함수정보(콜트리)

- 발생원인: Shared Pool 내에많은단편화(Fragmentation)가발생하거나, Free Memory가부족하여SQL이로드될공간을확보하지못했을때발생

- 해결방법

1. Literal SQL 조사

 일반적으로 ORA-4031을 가장 많 발생시키는 경우로, 입력값을사용하는SQL 중변수를 사용하여 작성하지 않고 하드코딩 된 값을 이용하는 SQL의 경우

 입력값을 제외한 다른 부분은 동일하더라도 개별 SQL로 인식하여 Hard Parsing 및 Shared Pool 단편화 현상을 일으킴. (아래script 참조)

2. Version Count 높은SQL 조사

 동일한 쿼리이나 변수값의 길이에 따라 다른 version으로 인식하여 별도의 SQL로 다시 Parsing 하여 Shared Pool 공간을 과다하게 사용하는 경우.

 V$SQL_SHARED_CURSOR 이용해 cursor를 공유하지 못하는 이유 파악해 해결해야함. (아래script 참조)

3. Shared Pool Size 증가(SGA_MAX_SIZE로SGA관리및추가할당여유공간있는경우)

 SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = nM;

4. Shared Pool Flush (DB 사용불가능상태인경우에만수행. Flush된SQL들이Hard Parsing 되며CPU 사용률증가와관련대기이벤트증가로또다른성능문제발생가능)

 SQL> ALTER SYSTEM FLUSH SHARED_POOL;

 

<Scripts>

1. 메모리(pool) 할당/사용조회

SELECT pool, SUM(bytes)/1024/1024 
FROM v$sgastat 
GROUP BY pool;
 
SELECT S.pool,S.name, S.bytes "Free Bytes",
       round((S.bytes/P.VALUE)*100,2) Perc_Free, 
       P.VALUE / (1024*1024) SB_Pool_Mg 
 FROM v$parameter P, v$sgastat S 
WHERE S.name = 'free memory'
  AND P.name = 'shared_pool_size' ;
 

 

2. Literal SQL

SELECT substr(sql_text,1,50),
       COUNT(*),
       SUM(executions),
       SUM(sharable_mem)
 FROM v$sqlarea
 GROUP BY substr(sql_text,1,50)
 HAVING COUNT(*) > 30
ORDER BY COUNT(*) DESC;
 
SELECT sql_text FROM v$sql WHERE sql_id='sql id';
 

 

3. Version Count

SELECT address,version_count
FROM v$sqlarea
WHERE version_count > 100 
ORDER BY version_count DESC;
 
SELECT * FROM V$SQL_SHARED_CURSOR
WHERE address = 'address from above query';
 

 

[References]

Oracle Database 11g: Administration Workshop I Chapter 4

ORA-4031 Error 발생시 점검항목 정리 by 박정재