from OTN http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#20461
from Oracle 9i SQL Tuning Workshop R2 2-8
==================================================================================================
PGA Memory Management for Dedicated Mode
Dedicated Mode는 PGA를 가진다.
9i 이전에는 이 영역을 TUNE 하기 위해 아래와 같은 파라미터를 조절하였다.
SORT_AREA_SIZE (정렬영역)
HASH_AREA_SIZE (해시영역)
BITMAP_MERGE_AREA_SIZE (비트맵병합영역)
CREATE_BETMAP_AREA_SIZE (비트맵생성영역)
PGA는 세션정보, 커서정보, SQL 실행 작업 영역, 정렬 영역, 해시 조인 영역,
비트맵 병합 영역, 비트맵 생성 영역으로 나뉘며
이를 간단히 나누면 CURSOR 정보, SESSION 정보, WORK AREA 정보로 나뉘어진다.
WORK AREA는 정렬과 INDEX 생성시 사용되는 공간이다.
위의 파라미터를 일일히 수정하는 것은 어렵다.
이는 데이터 입력사이즈과 작업영역의 수와 연관되어있는데 이는 매우 다양하며
서로 연관되어 얽혀있기 때문이다.
PGA_AGGREGATE_TARGET 의 설정은 *_AREA_SIZE 파라미터값을 무시한다.
(PGA_ ~~ 의 설정은 0이 아닌값을 할당한다는 뜻이고 우측의 파라미터가 무시된다는 것은 자동관리된다는 뜻)
SQL> alter system set pga_aggregate_target=32m scope=both;
System altered.
pga_aggregate_target 의 value를 0 이 아닌 값을 할당한다.
SQL> alter system set workarea_size_policy='auto' scope=both;
workarea_size_policy 를 manual에서 auto로 설정하면 자동관리가 실행된다.
자동관리가 된다는 의미는 위에서 보았던 네가지 파라미터 (*_AREA_SIZE ) 를 ORACLE이
관리한다는 뜻이다.
pga_aggregate_target, workarea_size_policy 둘다 dynamic 파라미터이다.
System altered.
SQL> show parameter aggr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 33554432
SQL> show parameter size_pol
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string auto
위에서 할당한 사이즈는 예로써 할당한 것이고 OTN에서의 권고사항은 다음과 같다.
- For OLTP: PGA_AGGREGATE_TARGET
= (total_mem * 80%) * 20%
- For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
전체메모리에서 80%를 ORACLE이 사용한다 할 때 OLTP 환경에는 그 사이즈의 20%를 PGA에 할당하고 다른경우 50%를 할당한다란 이야기다.
쉽게말해 OLTP에서는 한 SESSION이 하는 일의 양이 작고 단발적으로 일어나고 종료된다. 하지만 DW환경과 같은 경우 한 세션이 막대한 데이터를 가지고 JOIN이나 정렬작업을 일으킨다. 이경우 PGA의 SORT AREA 등을 사용하게 되는걸 생각했을 때 PGA에 큰 공간이 할애 되어야 할 것이다.
PGA 영역이 지나치게 작을 때 일어날수 있는 상황으로는 정렬작업시 메모리상에서 다 처리할수가 없어 물리적저장장치를 이용하게 되는데 이를 다중패스가 일어난다고 한다. 이 경우 물리적 I/O가 발생하기 때문에 작업의 수행속도가 늦어질 수 밖에 없다.
/*+ ONE-PASS 와 다중패스 */