행위

SGA 와 PGA

DB CAFE

1 SGA 확인/변경

1.1 ASMM(Automatic Shared Memory Management)

  1. 10g부터 SGA구성 요소 중 Shared pool, DB buffer cache, Large pool, Java pool, Streams Pool 메모리 자동 관리.
  2. MMAN 프로세스가 5분 마다 주기적으로 수집한 작업부하(Workload) 정보를 바탕으로 동적으로 구성, 메모리는 필요한 곳으로 동적으로 할당.

1.1.1 ASMM 사용 조건

  1. STATISTICS_LEVEL Parameter 값이 typical 또는 ALL 로 설정
  2. SGA_TARGET Parameter 값을 0 보다 큰 값으로 설정(0은 사용하지 않음을 의미)
  3. SGA_MAX_SIZE : ASMM에서 사용할 최대값 지정.
  4. SGA_MAX_SIZE와 SGA_TARGET은 동일한 값으로 설정 권장.
  5. DB_CACHE_SIZE =0
  6. JAVA_POOL_SIZE =0
  7. LARGE_POOL_SIZE=0
  8. SHARED_POOL_SIZE = 0
  9. STREAMS_POOL_SIZE = 0
  10. REDO LOG BUFFER 를 제외하고 대부분 자동 조절함.

주의 :sga_target 설정 시 REDO LOG BUUFER SIZE 는 항상 빼고 셋팅 할 것.

  • 예시
SGA_TARGET = 8G

DB_CACHE_SIZE =0
JAVA_POOL_SIZE =0
LARGE_POOL_SIZE=0
SHARED_POOL_SIZE = 0
STREAMS_POOL_SIZE = 0

==> 위와 같이 설정 시 최소 값은 기본 값으로 구성되며 최대 8G

DB_CACHE_SIZE = 1G 로 설정시 최소 값은 1G 유지하고, 최대 8G


- SGA_MAX_SIZE

: SGA 전체의 최대 크기, SGA관련 파라미터들은 SGA_MAX_SIZE 한도 내에서 파라미터의 크기를 변경

- Oracle 9i 이후부터는 DBA가 ALTER SYSTEM 명령을 이용해서 SGA 파라미터의 크기를 동적으로 변경

 'Dynamic SGA'라고 함.

- 데이터베이스를 shutdown/startup 없이 작업이 가능

- Oracle 10g에서 Dynamic SGA를 사용할 수 있는 SGA 관련 파라미터는

 DB_CACHE_SIZE, LOG_BUFFER, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE

- SGA 파라미터의 크기를 늘려주기 위해서는 필요한 만큼의 Free Granule이 존재해야만 하며,

 현재 사용하고 있는 SGA의 크기가 SGA_MAX_SIZE보다 작아야 합니다.
※ Granule 
  - Granule은 가상 메모리 상의 연속된 공간으로, dynamic SGA 모델에서 할당할 수 있는 최소 단위.
  - 이 Granule의 크기는 SGA 전체의 추정 값 (SGA_MAX_SIZE)에 따라 구분.
   if estimated SGA size is < 128M   4MB  else  16MB
  - Dynamic SGA를 사용할 수 있는 SGA관련 파라미터는 이 Granule 단위로 늘어나거나 줄어들 수 있습니다.

1.2 SGA 정보

1.2.1 SQL*Plus에서 SHOW SGA 명령어

SQL> SHOW SGA;
 Total System Global Area  171966464 bytes
 Fixed Size                   787988 bytes
 Variable Size             145750508 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 262144 bytes

◈ Total System Global Area

SGA_MAX_SIZE =[ Fixed Size + Variable Size +Database Buffers + Redo Buffers ]
- SGA를 구성하는 영역 크기의 합계로 SGA_MAX_SIZE 파라미터로부터 영향 받음.

◈ Fixed Size

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

◈ Variable Size

[ SHARED_POOL_SIZE +JAVA_POOL_SIZE +LARGE_POOL_SIZE + ?? ]
- 이 영역의 크기는 SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 파라미터로부터 영향을 받음

◈ Database Buffers

[ DB_CACHE_SIZE + DB_nK_CACHE_SIZE +DB_KEEP_CACHE_SIZE +DB_RECYCLE_CACHE_SIZE ]
- 데이터파일로 부터 읽어 들인 데이터 블록 내용을 저장하는 영역으로 DB_CACHE_SIZE 파라미터로부터 영향

◈ Redo Buffers

- 데이터베이스에 가해진 모든 변경 사항에 대한 내역을 저장하는 Redo Log Buffer의 크기로 LOG_BUFFER 파라미터로부터 영향

1.2.1.1 V$SGAINFO 뷰 조회

SQL> SELECT * FROM V$SGAINFO;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2057528 No
Redo Buffers                       14716928 No
Buffer Cache Size                1593835520 Yes
Shared Pool Size                  469762048 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                  33554432 Yes
Granule Size                       16777216 No
Maximum SGA Size                 2147483648 No
Startup overhead in Shared Pool   335544320 No
Free SGA Memory Available                 0

Internal SGA overhead 크기가 10G부터 shared pool size에 포함되었으며 자동 memory 관리 기능 사용하지 않고 수동 크기 설정 시 최소한 overhead 크기보다는 커야 합니다.

그렇지 않으면 startup 시 ORA-00371에러가 발생하며 startup 실패 합니다. 첨부파일 Metalink Doc ID 351018.1 : “Minimum for SHARED POOL SIZE.docx” 참조.

[ORA-00371] 00371, 00000, "not enough shared pool memory, should be atleast %s bytes" Cause: Init.ora parameter shared_pool_size is too small Action: Increase the parameter value

1.2.2 SGA 변경 이력 정보 조회

1.2.2.1 V$SGA_RESIZE_OPS

SGA 메모리 변경이력을 확인하여 최대로 증가한 값을 기준으로 수동 세팅 설정
- SHRINK : 감소
- GROW   : 증가

SELECT COMPONENT
     , OPER_TYPE
     , FINAL_SIZE/1024/1024/1024 FINAL_SIZE_GB
     , TO_CHAR(START_TIME,'YYYYMMDD HH24:MI:SS') STARTED
  FROM V$SGA_RESIZE_OPS
 WHERE STATUS='COMPLETE'
 ORDER BY STARTED DESC, COMPONENT;

1.2.2.2 V$MEMORY_RESIZE_OPS

SGA TARGET,PGA TARGET 증가/감소 이력 조회
SELECT COMPONENT
     , OPER_TYPE
     , OPER_MODE
     , PARAMETER
     , ROUND(INITIAL_SIZE/1024/1024/1024,2) INITIAL_SIZE_GB
     , ROUND(TARGET_SIZE/1024/1024/1024 ,2) TARGET_SIZE_GB
     , ROUND(FINAL_SIZE/1024/1024/1024,2) FINAL_SIZE_GB
     , START_TIME
     , END_TIME
     , CON_ID 
  FROM V$MEMORY_RESIZE_OPS
ORDER BY start_time , component;

1.2.3 SGA 변경

◈ 10g 의 경우 SGA_TARGET 파라미터를 설정하면 나머지 값들을 적절하게 자동으로 설정해줌 (ASMM)

SQL> alter database set sga_target=6000M scope=both;

◈ ASMM 미사용시

[SGA Size 산정] 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_BUFFER + 1MB

※ 대상 초기화파라미터는 SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, STREAMS_POOL_SIZE 가 있다.

sga_target = 0 => ASMM 를 사용하지 않겠다는 의미

SQL> alter system set LARGE_POOL_SIZE='32M' SCOPE=spfile;
SQL> alter system set JAVA_POOL_SIZE='50M' SCOPE=spfile;
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2000M;
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=500M;


1.2.4 현재 SGA SIZE 확인

select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size               744168 ==> 백그라운드 프로세스 액세스 하는 영역
Variable Size         520093696 ==> SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE
Database Buffers     2147483648 ==> DB_CACHE_SIZE
Redo Buffers            1323008 ==> Redo Log Buffer
select * from v$sgainfo;
SGA 영역 확인 (parameter)
SELECT NAME
     , DISPLAY_VALUE
  FROM V$PARAMETER
 WHERE NAME IN (
              'pga_aggregate_target'
            , 'db_cache_size'
            , 'shared_pool_size'
            , 'shared_pool_reserved_size'
            , 'log_buffer'
            , 'java_pool_size'
            , 'large_pool_size'
            , 'sga_max_size'
 );

2 PGA 확인/변경

PGA ==> User 의 개인 사물함 (메모리 영역) 
Sorting(정렬), 커서상태, Hash Join에 이용
- Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions
- Hash-join
- Bitmap merge
- Bitmap create
- Write buffers used by bulk load operations

2.1 PGA 정보

WORKAREA_SIZE_POLICY = AUTO/MANUAL; -- 관리방식

2.1.1 MANUAL 방식

- SORT_AREA_SIZE : Sort 작업을 위해 Oracle 프로세스가 최대로 사용할 수 있는 메모리 영역 - SORT_AREA_RETAINED_SIZE : Sort 작업이 완료된 후 결과 값을 유저 프로세스로 전달하는 패치 작업이 완료될 때까지 유지할 메모리 영역 - HASH_AREA_SIZE : Hash-Join 작업을 위해 Oracle 프로세스가 최대로 사용할 수 있는 메모리 영역 - BITMAP_MERGE_AREA_SIZE : 비트맵 인덱스에 대한 Range Scan을 통해 추출된 비트맵들의 병합(Merge)을 위해 Oracle 프로세스가 최대로 사용할 수 있는 메모리 영역 - CREATE_BITMAP_AREA_SIZE : 비트맵 인덱스 생성 시 Oracle 프로세스가 최대로 사용할 수 있는 메모리 영역

2.1.2 AUTO 방식

- PGA_AGGREGATE_TARGET : DB에 접속된 모든 Oracle 서버 프로세스들의 가용 PGA Target 크기이며, 값이 0 이상 할당될 경우 WORKAREA_SIZE_POLICY는 AUTO로, 메모리 사용 후 즉시 OS로 반납시키는 _USE_REALFREE_HEAP는 TRUE로 자동 변경된다. - _PGA_MAX_SIZE : 하나의 Oracle 프로세스당 최대로 사용할 수 있는 PGA 메모리 크기(Default 200MB) - _SMM_MAX_SIZE : Serial 오퍼레이션을 위한 최대 Work Area 크기이며, PGA_AGGREGATE_TARGET과 _PGA_MAX_SIZE에 의해 자동으로 계산된다. - _SMM_PX_MAX_SIZE : Parallel 오퍼레이션을 위한 개별 Slave 프로세스들의 최대 Work Area 크기이며, PGA_AGGREGATE_TARGET과 _PGA_MAX_SIZE에 의해 자동으로 계산된다. 6 DOP(Degree Of Parallelism)보다 큰 경우에만 적용되며, 6 DOP 이하일 경우 모든 Slave Process는 _SMM_MAX_SIZE 이내로 적용된다. - _SMM_MAX_SIZE와 _SMM_PX_MAX_SIZE의 계산 방식 : Oracle 11g R2 자체 TEST 결과 _SMM_MAX_SIZE의 경우 _PGA_MAX_SIZE 값의 50%가 할당됐고, _SMM_PX_MAX_SIZE는 PGA_AGGREGATE _TARGET 값의 50%가 할당됐다. Oracle 내부의 계산방식이라 확신할 수는 없으나 자체 Test 결과 일정한 비율은 확인되고 있어 파라미터 값에 대한 예측이 어느 정도 가능하다.

2.2 PGA관련 WAIT EVENT

  1. 정렬작업을 위해 임시 영역을 읽고 쓰는 경우에는 direct path read temp, direct path write temp 이벤트를 대기.
  2. Direct Path I/O는 정렬해야 할 데이터가 정렬을 위해 할당된 PGA 메모리 영역보다 큰 경우에 발생.
  3. 정렬작업 시 direct path read temp, direct path write temp 대기가 광범위하게 나타난다면 PGA 메모리 영역의 추가 할당
PGA_AGGREGATE_TARGET
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=10G SCOPE=BOTH;
_PGA_MAX_SIZE
ALTER SYSTEM SET "_PGA_MAX_SIZE"=1G SCOPE=BOTH;

2.3 SQL 메모리 정렬방식

- Optimal Pass Sort 
모든 작업이 메모리상에서 이루어지는 경우
- One Pass Sort 
프로세스에 할당된 작업공간의 크기 내에서 디스크상으로 한 번에 정렬이 이루어지는 경우
- Multi Pass Sort 
정렬작업 중간에 정렬결과를 TEMP, TBS 내 정렬 세그먼트(Sort Segment) 영역에 읽고 쓰는 작업이 여러 번 발생하는 경우
  • PGA 관련 대기 이벤트에서 Multi Pass Sort 작업 중 direct path read temp, direct path write temp 대기 이벤트가 광범위하게 발생하는 경우 PGA_AGGREGATE_TARGET의 값을 증가 해야함.

2.4 PGA SIZE 권장 사항 및 설정 방법

2.4.1 SQL*PLUS =

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1073741824

2.4.1.1 파라미터(V$PARAMETER)

SELECT NAME
     , DISPLAY_VALUE
  FROM V$PARAMETER
 WHERE NAME IN (
              'pga_aggregate_target'
 );

2.4.2 PGA_AGGREGATE_TARGET 설정 샘플

- For OLTP
PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
- For DSS
PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=655M SCOPE=BOTH;