SHARED POOL SIZE의 계산방법

SHARED POOL SIZE의 계산방법
==============================
PURPOSE
---------
다음은 ORACLE DATABASE 의 SHARED POOL SIZE를 계산하는 방법에 대하여 알아보기로 한다.

Explanation
------------
오라클 instance를 background process + SGA라고 말할 때 처음 instance가 기동되면 SGA가 할당된다. 이 때 이 SGA를 구성하는 메모리 영역은 크게 3부분으로 구성된다고 할 수 있다.
- Shared pool, Redo log buffer, DB buffer cache.

여기서는 SGA를 구성하는 shared pool의 size를 시스템에 맞게 산출하는 방법에 대해서 알아본다.
Shared pool 영역 구성은 MTS 방식으로 접속된 session의 PGA, Shared SQL area 그리고 dynamic하게 할당되는 data structure로 구성된다.

1. Shared Pool
Shared pool은 ?/dbs/initSID.ora(parameter file)의 SHARED_POOL_SIZE라는 parameter로 그 크기를 지정하는데 default값은 3.5M를 갖게 된다.
일반적으로 shared pool이 얼마나 사용되는가 하는 문제는 application dependent하므로 각

application을 조사하는 게 필요하다.
시스템에서 필요로 하는 크기를 검사하기 위해 아주 큰 크기로 parameter file에 지정하여 dynamic하게 할당되는 SGA가 충분히 큰 값을 갖게 한 후, 검사가 끝난 다음 아래에서 계산된 size로
변경 해 주도록 한다.

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

3. 계산 예제
(1) 적당한 user session에 대한 session id를 찾는다.

SVRMGR> 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를 찾는다.

SVRMGR> 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를 구한다.

SVRMGR> select sum(sharable_mem) from v$sqlarea;
SUM(SHARAB
------------------
8936625
1 row selected.

(4) PLSQL sharable memory area를 구한다.

SVRMGR> 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가 적당하다고 할 수 있다.

4. Shared Memory 부족 (ORA-4031)에 대한 대처 Ora-4031이 발생하는 원인은 2가지이다. 첫째 절대적으로 shared pool size가 작아서 나는 경우와, 둘째로 memory는 있으나 적재하려 하는

PL/SQL package가 너무커서 연속된 shared pool영역을 점유하지 못하는 경우가 있다.
만일 첫번의 경우라면 적당한 계산 과정으로 계산하여 parameter file에서 SHARED_POOL_SIZE를 늘려주고 ,두 번째 경우라면 다음과 같은 방법으로 에러를 피해 갈 수 있다.
- "Sys.dbms_shared_pool.keep" procedure사용.

[참고] 위 package를 사용하려면 ?/rdbms/admin/dbmspool.sql,
prvtpool.plb를 수행시켜 package를 create시킨 후 사용한다.

1. sga_target

이 parameter는 사용자가 원하는 크기만큼 sga를 지정하면 oracle kernel이 알아서(process수 etc)
shared_pool, db_cache_size등을 동적으로 할당하게 됩니다.
그냥 단순히 원하는 size만 지정하면 됩니다.
사용 예)
init.ora 에
sga_target = 300m
변경은 alter system set sga_target = 200m; 이런식으로 하면 됩니다만..
sga_target < sga_max_size입니다. sga_max_size는 oracle 10g에서도 변경 불가 입니다.

2. OLTP 속성에 관련된 parameter(shared_pool_size , db_cache_size)
sga_target , db_cache_size, shared_pool_size는 동적으로 크기를 변경할 수 있습니다.
단,db_cache_size + shared_pool_size <= sga_target 여야 합니다.
또한, sga_target < sga_max_size(변경불가) 입니다.
만약 sga_target = 300m로 지정하고

alter system set shared_pool_size = 100m;
alter system set db_cahce_size = 150m;

이렇게 하면 원하는 크기만큼 설정이 됩니다.

물론, shared_pool_size와 db_cache_size를 지정하지 않으면 오라클이 알아서(?)
내부적으로 shared_pool_size와 db_cache_size를 할당합니다.

가) sga_target만 설정한 경우
. sqlplus 에서 "show parameter sga_target" --- 지정된 값
. sqlplus 에서 "show parameter shared_pool_size" -- 0
. sqlplus 에서 "show parameter db_cache_size" -- 0

. alter system set sga_target = 200m; 으로 변경하면
. sqlplus 에서 "show parameter sga_target" --- 200m
. sqlplus 에서 "show parameter shared_pool_size" -- 0
. sqlplus 에서 "show parameter db_cache_size" -- 0

나) sga_targe=300m, shared_pool_size, db_cache_size를 지정한 경우
. sqlplus 에서 "show parameter sga_target" --- 지정된 값
alter system set shared_pool_size = 100m;
alter system set db_cahce_size = 150m;

. sqlplus 에서 "show parameter shared_pool_size" -- 100m
. sqlplus 에서 "show parameter db_cache_size" -- 150m

3. Batch 속성에 관련된 parameter(pga_aggregate_target)
Batch 속성을 갖는 DB는 주로 sorting 작업을 많이 하기 때문에 pga 영역의 확보가 절대적으로 필요합니다.
따라서 alter system set pga_aggregate_target = 1000m; 등등으로 설정하면 됩니다.

4. 그러면 얼만큼 메모리를 할당하면 될까요?
가) 일반적으로 OLTP인경우
SGA (65%) + PGA (15%) + O/S Overhead (20%)
예를 들어 전체 메모리가 10G 인경우
SGA ( 6G) , PGA( 2G ), O/S (2G)

나) 일반적으로 BATCH인경우
SGA (30%) + PGA (50%) + O/S Overhead (20%)
예를 들어 전체 메모리가 10G 인경우
SGA ( 3G) , PGA( 5G ), O/S (2G)

5. 그러면 4.에서 권고한 만큼 메모리를 할당/재할당 해야 하나요?
- 그건 그때 그때 달라요.. 경험이 필요하겠죠
원칙은 오라클에 가급적 많음 메모리를 할당하되, page나 swap이 빈번하게 발생하면 않되겠쬬!!!!!

[출처] SHARED POOL SIZE의 계산방법|작성자 검은괭이