SORT와 PGA_AGGREGATE_TARGET

 http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams157.htm 

PGA는 각각의 프로세스에서 개별적으로 사용되는 메모리 영역을 말한다. 이 PGA는 크게 아래와 같이 4개로 나누어져 있다. 

정렬 공간 : 명시적 또는 묵시적으로 정렬작업이 발생할 때 사용된다. 이 공간에서 정렬작업이 완료된다면 메모리 정렬이라고 하고 작업량이 이 메모리 영역을 넘어설 경우 disk 를 사용하게 된다.
세션 정보 : 유저 프로세스의 세션정보를 저장한다.
커서 상태 정보 : SQL 파싱 정보가 저장되어 있는 주소를 저장한다.
변수 저장 공간 : SQL 문장에서 사용했던 BIND 변수를 저장한다.

9i부터 PGA 메모리 영역의 크기를 auto와 manual 두가지 방법으로 관리한다.
AUTO으로 관리한다면 PGA_AGGREGATE_TARGET 파라미터를 설정해야 하고 manual 으로 한다면  *_AREA_SIZE  파라미터 값을 설정해야 한다.

WORKAREA_SIZE_POLICY=AUTO|MANUAL

AUTO ( PGA_AGGREGATE_TARGET  )

PGA_AGGREGATE_TARGET는 PGA 메모리 관리를 Auto 로 했을 때 사용되는 파라미터이다. 

주의 

이 값을 0으로 설정하면 자동으로 WORKAREA_SIZE_POLICY=MANUAL 으로 된다.

Property	Description
Parameter type	Big integer
Syntax	PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value	10 MB or 20% of the size of the SGA, whichever is greater
Modifiable	ALTER SYSTEM
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

<출처 : http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams177.htm  >

 
AUTO 로 관리될 경우 PGA_AGGREGATE_TARGET 에 설정 크기내의 가용 메모리 내에서 PGA 크기가 자동으로 조정된다.
말이 좀 어려운데  PGA_AGGREGATE_TARGET는 생성되는 모든 PGA가 사용 할 수 있는 총 메모리 크기이다. 그러므로 예를 들면 PGA_AGGREGATE_TARGET =10M 일때 현재 1mb를 사용중인 Process 가 2개가 있다면 현재 가용 메모리 양은 8MB 가 된다.

만약 크기를 정하지 않으면 기본값은 10Mb 와 SGA 크기의 20%중 큰 값으로 설정된다.

 예를들어 만약  PGA_AGGREGATE_TARGET 이 50MB 이고 현재 PGA크기가 5MB 인 서버프로세스가 10MB 작업공간이 필요한 정렬작업을 수행시 자동으로 10MB의 메모리를 할당받게 되어 메모리 정렬이 일어나게 된다.


MANUAL ( SORT_AREA_SIZE ) 

 이전 버전에서는 SORT_AREA_SIZE 를 설정하여 각 서버 프로세스별로 동일한 크기의 PGA 를 할당하여 사용했었다. 이 방법은 관리가 간단한 반면 메모리 사용이 비효율적일 수 있다. 
 각 PGA는 SORT_AREA_SIZE 크기 만큼의 정렬공간을 할당받는다. 예를 들어 SORT_AREA_SIZE가 5M일 때 10MB 의 공간이 필요한 정렬 작업이라면 디스크I/O가 발생하게 된다. 


주의 사항
 분명 WORKAREA_SIZE_POLICY=AUTO 방법으로 사용하는 것이 융통성이나 효율면에서 이점이 있는것이 분명하다. 하지만 주의 할 점은  PGA_AGGREGATE_TARGET=100M 인데 어떤 서버 프로세스의 PGA에서 100MB 할당 받아버리면 다른 사용자가 접속할 수 없게 된다. 그러므로 이를 위해 잘 파악하고 사용해야 한다.




현재 PGA 메모리 사용량과 최대 메모리 사용량 조회

-

   select PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM 
   from v$process
   order by 1;
   
 -- 결과
PROGRAM                                          PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM 
------------------------------------------------ ------------ ------------- -----------
PSEUDO                                                      0             0           0
oracle@server1 (ARC0)                                10755245      22907169    22907169
oracle@server1 (ARC1)                                10767957      22907169    22907169
oracle@server1 (CJQ0)                                  465021       1480333     1480333
oracle@server1 (CKPT)                                  301197        609233      609233
oracle@server1 (D000)                                  555241       1218189     1218189
oracle@server1 (DBW0)                                  274557       1933613     1933613
oracle@server1 (J000)                                  257157       1087117     1087117
oracle@server1 (LGWR)                                10769773      22910077    22910077
oracle@server1 (MMAN)                                  214137        366221      366221
oracle@server1 (MMNL)                                  218217        431757      431757
oracle@server1 (MMON)                                 1248501       2725517     2856589
oracle@server1 (PMON)                                  213713        366221      366221
oracle@server1 (PSP0)                                  212937        366221      366221
oracle@server1 (QMNC)                                  215281        366221      366221
oracle@server1 (RECO)                                  352437        497293      497293
oracle@server1 (RVWR)                                  211073       3118733    17929869
oracle@server1 (S000)                                  122889        300685      300685
oracle@server1 (SMON)                                  465737       1283725     1349261
oracle@server1 (TNS V1-V3)                             326625        645417      645417
oracle@server1 (TNS V1-V3)                             309549        579881      579881
oracle@server1 (TNS V1-V3)                             326625        645417      645417
oracle@server1 (TNS V1-V3)                             326625        645417      645417
oracle@server1 (q000)                                  314445        562829      562829
oracle@server1 (q001)                                  231081        366221      366221

-

위의 결과에서 highlight 된 부분이 Server Process 이다. 

각 컬럼에 대해 설명하자면 
PGA_USED_MEM : 프로세스가 현재 사용하는 PGA 크기.
PGA_ALLOC_MEM : 프로세스에 할당된 PGA 크기
- 다른 프로세스에서 필요로 할 경우  PGA_USED_MEM  를 제외한 나머지 공간을 반환한다. 즉  PGA_ALLOC_MEM  만큼 할당되어 있지만 현재는  PGA_USED_MEM  만 사용중이고 남은 메모리는 반환되지 않았음을 의미)
PGA_MAX_MEM : 프로세스가 가장 많이 사용했을 때의 크기




&