[Oracle] PGA란?

SGA : 각 server process가 공유하는 메모리공간

PGA : 각 server process별로 개별적으로 할당되는 메모리공간

 

사용 목적

다른 server process 와 공유할 내용이 아니며 현재 세션에서만 쓰고 끝낼경우 

SQL Work Area

정렬, hashing 시 Disk IO를 줄이기 위해 사용 

- [Sort Area] SQL이 sort by , group by 처럼 정렬이 필요할 경우 

- [Hash Area] hash join 

Private SQL Area

- Cursor & Data Fetch

- SQL 바인드 변수 등 

 

PGA를 활용한 SQL 정렬 원리  

서론 : 정렬 hashing 할 데이터양이 매우 클 경우 , PGA(memory)를 많이 쓰면서, 다른 프로세스가 사용하지 못할 수 있다 

1. PGA 메모리를 점차 늘리면서 데이터를 정렬한다 (10M->20M)

 - 더이상 메모리 할당은 위험하다

2. PGA에서 1차적으로 정렬한 데이터를 -> Temorary Tablespace 에 내린다 (PGA는 비움)

3. 추가 데이터를 PGA에 올려서 정렬함  

 - 2-3을 반복한다 

4. Temp Tablespace에서 최종적으로 정렬한다 

 

인프런- 권철민 강사 강의자료 중 일부 발췌

Optimal : PGA만 사용

one pass : 데이터양이 많아 PGA 외의 temp도 썼을 경우

muti pass :  one pass가 여러번 발생하는것 

 

 

PGA 메모리 할당 방법 (Hidden Parameter)

history

8i까진 , DBA가 PGA 개별 메모리 구성요소 사이즈를 직접 설정 (ex.sort_area_size , sort_area_retained_size hash_area_size ...) 

OLTP에선 sorting 이 빈번하지 않고 작은 범위이니, sort area 작게 설정하고 그랬다 

단점으로는 고정 파라미터로 인해 쿼리나 업무에 따라 최적화된 PGA 구성이 어려움 (저녁에 배치돌릴땐 설정바꾸고 ~)

 

9i 부터, SQL Work Area size 정하는걸 동적으로 할당 , 주로 많이 사용한다(메모리 낭비도 예방)

WORKAREA_SIZE_POLICY=AUTO , PGA_AGGREATE_TARGET=4G <- soft limit이기에 최대 PGA size는 아니다 

*** PGA_AGGREATE_TARGET 은 online 중에 변경이 가능하다 

 

12c부터는 PGA_AGGREGATE_LIMIT 으로 PGA limit 정할 수 있음 <- hard limit 

 

 

SGA,PGA 설정을 할때는 _ADVICE 테이블에서 권고하는 사이즈로 하는게 가장 좋다 

 

 

Hidden Patameter

_smm_px_max_size	(DOP 5 이상) PQ (parallel query) 사용 시 한 개의 세션이 최대 사용 가능한 Workarea 메모리. PGA_AGGREGATE_TARGET의50%  (하나의 PQ가 사용할 수 있는 최대 )
_pga_max_size	한개의 서버프로세스가 최대 사용가능한 PGA  Workarea 메모리
_smm_max_size	한개의 서버프로세스가 단일 Operation에서 최대사용 가능한 Workarea메모리._pga_max_size의 50% ** 단일 operation이란? 하나의 쿼리에서 hash join, group by  ** PQ에서 쓰레드별로 돌아갈 경우 하나의 작업이 하나의 opertation (parallel T1,5 면 각 5개의 thread 별로 사용할 수 있는 크기  )

 

PGA_AGGREATE_TARGET 크기에 따른 size의 변화 

_smm_px_max_size :  PGA_AGGREATE_TARGET 의 50%

_pga_max_size : PGA_AGGREATE_TARGET  사이즈의 구간에 따라 퍼센테이지가 줄어듬  ( 1G이하까진 기본적으로 200M ~ PGA_AGGREATE_TARGET 의 20%.10%.5% )

_smm_max_size : _pga_max_size 의 50%

 

PGA – How Oracle allocate memory to the process – makeoracle (wordpress.com)

 

 

 

Parallel Query 사용 시 PGA 할당 하는 방법 

PQ는 주로 데이터가 큰 배치,DW 에서 많이 사용한다 . 그런 업무 특징이 PGA를 많이 사용하게 된다 (group by 로 summary 만들거나 등등)

 

QP 쿼리 하나는 _smm_px_max_size 값을 넘어갈 수 없으며, thread 당 _smm_max_size 만큼 사용할 수 없다 

_pga_max_size 사이즈는 자동으로 PGA_AGGREATE_TARGET  따라 설정이 되니 수동으로 설정할 수 있다  

 

잘 안쓰는 방식이긴 하나..

SORT_AREA_SIZE나 , HASH_AREA_SIZE 을 수동으로 바꾼 다음 parallel 로 수행 할 경우 , 하나의 쿼리(PQ)가 할당되는 PGA양이 상당해진다 Parallel degree 만큼 배로 사용하게 되니 조심하여 사용해야한다.

 

 

 

 

PGA_AGGREATE_TARGET  를 설정하는 방법

server process 들은 설정값 안에서 메모리 할당을 받고 사용함  

서비스 유형에 따라 설정가이드라인이 다르다 .

1. OLTP

SGA 공간확보가 중요하다. SGA는 주로 buffer cache 용으로 사용 ()

보통 PGA 초기설정인 2MB 정도를 넘지 않음 

session 1000개가 connect 만 하고 inactive 상태라고 해도 2G만 사용됨 

OLTP는 transaction이 빨리 끝내고 메모리 반환함 

- PGA_AGGREATE_TARGET   : ( 전체 mem size * 0.8) * 0.2

- SGA : ( 전체 mem size * 0.8) * 0.8

 

2. DSS (Decision Support System : DW,batchjob )

100G중 40G는 PGA , 40G는 SGA로 사용 

SGA는 buffer cache에서 가장 많은 양을 사용하고 , buffer cache는 single block( random access )할 때 올라오게 된다 

DSS 특성은 full scan이 많이 발생함으로 , direct IO가 많이 발생한다 (SGA에 안올라감 - 작은 테이블은 full scan 해도 올라가겠지만)

서비스 특성

보통 DSS 는 데이터가 많기 때문에 Group by로 보려는 데이터를 추린다 . 

join도 많이 사용 (hash join도 많이 사용하니 hash가 사용하는  PGA)

- PGA_AGGREATE_TARGET   : ( 전체 mem size * 0.8) * 0.5

- SGA : ( 전체 mem size * 0.8) * 0.5

 

여유공간을 위해 전체 메모리의  0.8을 사용하는데 , PGA를 위한 공간으로 봐도 무방하다  (그치만 실제 메모리가 클 경우 남는 공간이 많기 때문에 꼭 위의 수식을 따르진 않는다)

 

V$PGA_TARGET_ADVICE 뷰

PGA_AGGREATE_TARGET 의 사이즈를 줄이고 늘리면 성능이 얼마나 향상될지 보여준다

실시간으로 보여짐

컬럼 설명 

PGA_TARGET_FOR_ESTIMATE	예측PGA_AGGREGATE_TARGET크기
PGA_TARGET_FACTOR	현재설정된PGA_AGGREGATE_TARGET 대비예측 PGA_AGGREGATE_TARGET 크기비율 1이 현재 값이고 1.2면 1.2배 늘렸을 경우
ADVICE_STATUS	ADVICE 설정셋팅여
BYTES_PROCESSED	WORK AREA 내에서처리된총BYTES수
ESTD_TIME	총BYTES 처리시간
ESTD_EXTRA_BYTES_RW	추가적으로처리되어야할총BYTES 수(One-pass,Multi-pass 시) 추가적으로 temporary TBS에 내려가는 양 (PGA 모자르니 disk 사용하는 )
ESTD_PGA_CACHE_HIT_PERCENTAGE	PGACache Hit percent. BYTES_PROCESSED/(BYTES_PROCESSED+ESTD_ESTRA_BYTES_RW)
ESTD_OVERALLOC_COUNT	PGA Memory over-allocation 횟수 max 찍고 allocate 하는 횟수

 

 

 

DBA_HIST_PGA_TARGET_ADVICE

AWR에서 제공하는 것으로 , 실 업무에선 이걸 많이 본다 

하루에 한번씩 모니터링한다