꿈꾸는 개발자, DBA 커뮤니티 구루비

PGA 관리

특정 App에 대한 최적의 값을 찾는 유일한 방법은 테스트를 실시하여 좋은 성능을 얻는데 얼마나 많은 PGA가 필요한지 측정하는 것이다.
실제로 메모리의 용량은 성능에만 영향을 미치고 오퍼레이션의 동작 방식에는 영향을 미치지 않는다.

WORKAREA_SIZE_POLICY

WORKAREA_SIZE_POLICY 초기화 파라미터는 작업 영역의 크기를 어떤 방식으로 구성할지 결정한다. 다음의 두 값 중 하나로 설정할 수 있다.
auto : 메모리관리자 가 단일 작업 영역의 크기를 결정한다. PGA_AGGREGATE_TARGET 초기화 파마미터를 통해 전체 시스템에서 사용할 PGA의 총량을 지정한다.
manual : HASH_AREA_SIZE, SORT_AREA_SIZE, SORT_AREA_RETAINED_SIZE, BITMAP_MERGE_AREA_SIZE 초기화 파라미터를 통해 작업 영역의 크기를 완전하게 제어한다.

PGA_AGGREGATE_TARGER

pga_aggregate_targer 초기화 파라미터를 통해서 하나의 데이터베이스 인스턴스가 할당할 전제 PGA 메모리의 크기를 바이트 단위로 지정할 수 있다.

메모리 관리자가 세션에 할당되는 PGA의 양을 자동으로 조절한다.

PGA_AGGREGATE_TARGER 초기화 파라미터를 통해 설정한 목표값이 너무 낮을 경우(여기서는 128MB), 메모리 관리자는 이를 준수하지 않는다.



SQL> SELECT name, value, unit
  2  FROM v$pgastat
  3  WHERE name IN ('aggregate PGA target parameter',
  4                 'maximum PGA allocated',
  5                 'over allocation count');

NAME                                VALUE UNIT
------------------------------ ---------- ----
aggregate PGA target parameter 134217728 bytes
maximum PGA allocated          418658304 bytes
over allocation count                 94



SQL> SELECT name, value, unit
  2  FROM v$pgastat
    3  WHERE name LIKE '% PGA allocated' OR name LIKE '% workareas';

NAME                                       VALUE UNIT
------------------------------------- ---------- ----
total PGA allocated                    999358464 bytes
maximum PGA allocated                 1015480320 bytes
total PGA used for auto workareas      372764672 bytes
maximum PGA used for auto workareas    614833152 bytes
total PGA used for manual workareas            0 bytes
maximum PGA used for manual workareas          0 bytes



SQL> SELECT name, value, unit
  2  FROM v$pgastat
  3  WHERE name LIKE 'aggregate PGA %';

NAME                                 VALUE UNIT
------------------------------ ----------- ----
aggregate PGA target parameter  1073741824 bytes
aggregate PGA auto target        910411776 bytes

SQL> execute pga_pkg.allocate(500000)

SQL> SELECT name, value, unit
  2  FROM v$pgastat
  3  WHERE name LIKE 'aggregate PGA %';

NAME                                 VALUE UNIT
------------------------------ ----------- ----
aggregate PGA target parameter  1073741824 bytes
aggregate PGA auto target        375754752 bytes

SQL> execute dbms_session.reset_package;

SQL> SELECT name, value, unit
  2  FROM v$pgastat
  3  WHERE name LIKE 'aggregate PGA %';

NAME                                 VALUE UNIT
------------------------------ ----------- ----
aggregate PGA target parameter  1073741824 bytes
aggregate PGA auto target        910411776 bytes

PGA_AGGREGATE_LIMIT

PGA_AGGREGATE_LIMIT 초기화 파라미터는 12.1 버전의 새로운 기능이다.
PGA_AGGREGATE_LIMIT 초기화 파라미터의 기본값은 다음 중에서 가장 큰 값으로 설정된다.
- 2GB
- PGA_AGGREGATE_TARGET 초기화 파라미터의 값의 두 배
- process 초기화 파라미터 값에 3MB를 곱한 값


SQL> ALTER SYSTEM SET pga_aggregate_limit = 1G;
ALTER SYSTEM SET pga_aggregate_limit = 1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

한계에 도달하면 데이터베이스 엔진은 호출을 중단하거나 심지어 세션을 죽인다. 호출이 중단되면 다음과 같은 오류가 발생한다.


ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT


ORA-03113: end-of-file on communication channel
Process ID: 5125
Session ID: 17 Serial number: 39


PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB
Immediate Kill Session#: 17, Serial#: 39
Immediate Kill Session: sess: 0x77eb7478  OS pid: 5125

SORT_AREA_SIZE

수동 PGA 메모리 관리를 사용하는 경우 sort_area_size 초기화 파라미터를 사용하여 머지 조인, 정렬, 집계에 사용되는 작업 영역의 크기를 바이트 단위로 지정한다.

SORT_AREA_RETAINED_SIZE

단지 정렬 Operation이 일어나는 동안 사용되는 메모리의 양을 지정할 뿐이다.

HASH_AREA_SIZE

하나의 작업 영역의 크기를 지정할 뿐이며, 하나의 세션은 여러 개의 작업 영역을 할당할 수 있다.

BITMAP_MERGE_AREA_SIZE

비트맵 인덱스와 관련된 비트맵을 병합하는데 사용되는 작업 영역의 크기를 바이트 단위로 지정한다.