본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++


출처 : 다음카페



PURPOSE
-------
이 문서는 SGA의 적절한 크기를 산정하는 방법을 기술하는데 목적이 있다.

Explanation
-----------
이 부분에서는 Oracle8, Oracle8i, Oracle9i에 대한 산정
방법을 먼저 기술한다. Oracle7에 대한 내용은 문서
후반에 별도로 기술한다.

1. SGA 크기를 살펴보는 방법

SGA 크기에 대한 정보는 데이터베이스 구동시 표시된다. 이 정보는
sqlplus 나 svrmgrl 에서도 살펴 볼 수 있다.

8.0.X
- svrmgrl
connect internal
show sga

8.1.X
- svrmgrl or sqlplus /nolog
connect internal
show sga

9.X
- sqlplus

SQL*Plus: Release 9.0.1.0.0 - Production on Thu Aug 23 15:40:29 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production

SQL> show sga
Total System Global Area 72123504 bytes
Fixed Size 279664 bytes
Variable Size 67108864 bytes
Database Buffers 4194304 bytes
Redo Buffers 540672 bytes


2 SGA를 구성하는 영역

다음은 svrmgrl SHOW SGA 실행 예이다:

Total System Global Area 23460696 bytes
Fixed Size 72536 bytes
Variable Size 22900736 bytes
Database Buffers 409600 bytes
Redo Buffers 77824 bytes

Total System Global Area
- SGA를 구성하는 영역 크기의 합계 (byte 단위)

Fixed Size
- 데이터베이스나 인스턴스의 상태를 저장하는 영역으로,
백그라운드 프로세스가 액세스 하는 영역
- 사용자 데이터는 저장되지 않음
- 일반적으로 100k 미만의 영역을 차지함

Variable Size
- 이 영역의 크기는 init.ora 파일의 다음 파라미터로 부터 영향을
받는다.
shared_pool_size
large_pool_size
java_pool_size
- 버젼별 내용에 대해서는 이 문서의 'SGA 크기 산정' 부문에 기술
되어 있다.

Database Buffers
- 데이터파일로 부터 읽어들인 데이터 블럭 내용을 저장하는 영역.
size = db_block_buffers * block size

Redo Buffers
- SGA 내의 circular buffer로서, 데이터베이스에 가해진 변경 사항에
대한 내역을 저장하는 영역.
- 최소 값은 데이터베이스 블럭 크기의 4배 이상으로 지정됨.

3. SGA 크기 산정

8.0.X

SGA 크기를 산정하기 위해서는 다음과 같은 공식을 사용한다.

((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + log_buffers) + 1MB


8.1.X

SGA 크기를 산정하기 위해서는 다음과 같은 공식을 사용한다.

((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

9.X
오라클 9i에서는, SGA가 이전 버젼처럼 정적으로 구성될 수도 있고,
동적으로 바뀔 수 있구 구성할 수 도 있다.

동적으로 SGA 크기를 변경할 수 있도록 지정한 경우, 다음과 같은
데이터베이스 초기화 파라미터에 의해 결정된다 : DB_BLOCK_SIZE,
DB_CACHE_SIZE, SHARED_POOL_SIZE, LOG_BUFFER.

오라클 9i부터, SGA의 크기는 동적으로 바꿀 수 있다. 이것은,
다음 파라미터를 사용하여 SGA 크기를 인스턴스 실행 중에도
바꿀 수 있다는 것을 의미한다.

Buffer cache (DB_CACHE_SIZE) -- standard block의 cache 크기 (byte 단위)

Shared pool (SHARED _POOL_SIZE) -- shared SQL과 PL/SQL 문장을 저장하기
위한 공간의 크기 (byte 단위)
Large pool (LARGE_POOL_SIZE) (기본값 : 0 bytes) -- large pool의 크기로
large pool은 shared server의 session memory,
message buffer에 대한 병렬 처리 등에 사용
되는 시스템 영역이며, disk I/O buffer의
backup 및 restore 처리에 의해 사용되기도
한다.

LOG_BUFFER 파라미터는, redo entry를 redo log 파일로 기록하는 과정에서
buffering하는데 사용된다. LOG_BUFFER 파라미터는 정적인 파라미터로
SGA의 적은 부문을 차지 하며, 데이터베이스를 SHUTDOWN 시킨 후 재 구동
시킬 때만 init.ora 파일로 부터 변경된 값을 읽어 들여 반영이 된다.

MAX_SGA_SIZE 파라미터를 동적으로 바꿀 수는 없으나, SGA를 구성하는
부문의 크기를 동적으로 바꿀 수 있다. DB_CACHE_SIZE, SHARED_POOL_SIZE,
LARGE_POOL_SIZE 크기를 데이터베이스 운영 중간에도, 바꿀 수 있다.

(참고: LARGE_POOL_SIZE 는 Oracle 9.0.1에서는 값을 동적으로 바꿀 수
없으며, 후속 버젼에서 적용될 예정이다)

최적의 cache 크기를 유지하기 위해서, DB_CACHE_ADVICE 파라미터 (동적
파라미터) 를 이용하여, cache 크기의 변화에 따른, 변동 사항을
V$DB_CACHE_ADVICE 뷰를 통해 조회할수 잇다.
DB_CACHE_ADVICE 파라미터는 ALTER SYSTEM...SET 절을 사용하여, 동작
하게 설정 ㅎㄹ 수 있다.
이 파라미터에 대한 자세한 설명은 Oracle 9i Database preformance
Guide에 자세히 기술되어 있다.


Oracle 9i부터, 테이블스페이스를 서로 다른 block size로 만들 수 있는
기능 및, 각 block size별 cache의 크기를 지정하느 기능이 추가 되었다.

SYSTEM 테이블스페이스는, 데이터베이스의 표준 block size를 사용하며,
다른 테이블스페이스는, 4종류 까지의 서로 다른 block size를 사용할

수 있다.

표준 block size는 DB_BLOCK_SIZE 파라미터에 의해 지정되며, 그 블럭에
대한 cache 크기는, DB_CACHE_SIZE 파라미터에 의해 지정된다. 표준 이외의
block size는 CREATE TABLESPACE 문장의 BLOCKSIZE 절에 의해 지정할 수
있다. 표준 이외 block size별 cache의 크기는, DB_nK_CACHE_SIZE 파라미터에
의해 지정되며, 여기서 n은 2, 4, 8, 16, 또는 32 Kbytes 가 될 수
있다.

표준 block size, 또는 기본 block size는 일반적으로 OS의 시스템 block

size와 동일한 크기 또는, 그 배수로 지정한다. DB_CACHE_SIZE 파리미터
- DEFAULT cache size - 는 표준 block size에 대한 cache의 크기를
지정한다 ( 기본은 48MB 으로 지정됨 ) 시스템 테이블스페이스는
표준 block size로 생성이 되며, DEFAULT cache size를 사용하게 된다.

표준 block size이거나, 표준 이외의 block size 이거나, 그리고 각 block
size별 cache는 동일한 크기의 block size를 사용하는 어떤 테이블스페이스에
의해서도 사용될 수 있다.
만약, 데이터베이스를 디자인 할 때, 다중 block size를 고려한다면,
DB_CACHE_SIZE 이외에도, 사용하고자 하는 block size에 대한
DB_nK_CACHE_SIZE 값을 지정하여야 한다.
사용하고자 하는 모든 block size에 대해, 각각의 cache의 크기를
지정해 주어야 한다. 이와 같이 block size 별 cache 크기 지정을 하는
방법은, 4개 까지의 표준 이외의 block size를 사용하는 테이블스페이스를
데이터베이스에서 액세스 할 수 있게 함으로써, block size별
목적에 맞는 cache 크기를 상세하게 지정할 수 있게 해 준다.

DB_BLOCK_SIZE 값은 데이터베이스를 생성할 때만 지정 가능하고, 한번
생성된 데이터베이스에 대해서는 그 값을 바꿀 수 없으므로, 초기에
값을 정할 때 신중하게 결정하여야 한다.

SGA 크기를 산정하기 위해서는 다음과 같은 공식을 사용한다.


DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE
+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB

참고: 사용중인 각각의 DB_nk_CACHE_SIZE 크기를 더해야 한다.
DB_nk_CACHE_SIZE는 (2, 4, 8, 16, 32k) 블럭에 대해 4개까지
지정 가능하다. 이중 하나는 기본 block size로 지정
될 것이고, 그 크기는 DB_CACHE_SIZE에 별도로 지정하게 된다.

4. 추가 정보

1) SHOW SGA 명령을 통해 나타나는 Redo Buffer의 크기는 init.ora 파일의
log_buffer 파라미터에 지정된 값과는 다른 값이 나타난다.

2) Redo Buffer의 크기는 데이터베이스가 실행되는 O/S의 block size의
최소한 4배 이상의 크기가 지정 되어야 한다. 자세한 자료는
<Note 30753.1> Init.ora Parameter "LOG_BUFFER" Reference Note 참조

3) Java_pool_size는 SHOW SGA 명령이나, V$SGA 뷰 조회시 감안되지
않는 문제는 Oracle 8.1.6 에서 해결되었다.

4) Oracle 8.1.5에서는 Java_pool_size와 관련된 제약사항이 존재한다.
기본 값은 20000K로 지정이 되어 있는데, 이 값을 최소한
1000K 이상으로 지정하지 않으면, 데이터베이스 구동시 ORA-01078
"failure in processing initialization parameters" 라는
에러가 발생하게 된다.

5) Oracle 8.1.6에서는 Java_pool_size와 관련된 제약사항이 존재한다.
기본 값은 20000K이며, init.ora 파일에 원하는 값을 지정할 수
있으나 최소 32768 이상으로 지정되어야 한다.


5. Oracle 7에서 SGA 크기를 산정하는 방법과, 사용중인 SGA 크기 확인

SGA의 개략적인 크기는 다음과 같은 공식을 사용하여 산정할 수
있다.

( (db_block_buffers * block size) + shared_pool_size + log_buffers) /.9

예 ( HP-UX 9000 장비 Oracle 7.0.16 ):

init<SID>.ora 에 다음 사항이 기술됨.
DB_BLOCK_BUFFERS = 200
LOG_BUFFERS = 8192
SHARED_POOL_SIZE = 3500000

default Block Size = 2048 byte 로 지정되어 있을 때

db_block_buffers * block size + shared_pool_size + log_buffers
( (200 * 2048) + 3500000 + 8192 ) / .9

409600 + 3500000 + 8192 = 3917792 bytes / 0.9

= 4,353,102 bytes (4M)

여기서 0.9로 나누는 이유는, SGA의 가변 부분을 고려해야 하기 때문이다.
이것은 정확한 값을 예상하는데 사용되는 값이다.

위 공식을 통해 예상된 값은 4353102 byte 였으나, 다음 예를 보면
실제 값은 4504072 byte 인 것을 알 수 있다.

SGA의 실제 크기를 확인하기 위해서는 다음과 같은 명령을
sqldba 또는 svrmgrl에서 수행 시켜야 한다.


7.0.X - 7.2.X

% sqldba lmode=y
SQLDBA> connect internal
SQLDBA> show sga

7.1.X - 7.3.X

% svrmgrl
SVRMGR> connect internal
SVRMGR> show sga

다음은 결과의 예이다.

Total System Global Area 4504072 bytes <-- 메모리 상의 전체 크기
Fixed Size 37704 bytes
Variable Size 4048576 bytes
Database Buffers 409600 bytes
Redo Buffers 8192 bytes ('log buffers')