PGA 관리

1 PGA 관리[편집]

정렬 명령과 해쉬조인은 개인메모리 영역인 PGA에서 실행됨
일반적으로 작업영역이 클수록 더 좋은 성능을 제공

1.1 주요 파라미터[편집]

1.1.1 workarea_size_policy[편집]

작업영역의 크기를 어떤 방식으로 구성할지 결정
1. :auto
  1. 메모리 관리자가 단일 작업 영역의 크기 결정
  2. pga_aggregate_target 초기화 파라미터를 통해,전체 시스템에서 사용할 PGA 총량 지정
  3. 기본값은 auto
2. :manual
  1. hash_area_size,sort_area_size,sort_area_retained_size,bitmap_merge_area_size 초기화 파라미터를 통해 작업영역의 크기 결정
동적파라미터 이며 인스턴스 레벨과 세션 레벨에서 변경가능

메모리 관리를 auto할것을 강력하게 권고함(manual로 좋은 결과를 내는 경우는 드물기 때문)

1.1.2 pga_aggregate_target[편집]

유효값은 10MB ~ 4TB 까지 지정 가능
기본값은 SGA크기의 20%
memory_target 과 memory_max_target 초기화 파라미터로 필요에 따라 SGA와 PGA간의 자동으로 메모리 재분배
1. 이러한 구성을 하는 경우 pga_aggregate_target은 PGA의 최소크기를 지정하는 역활만 한다.
물리적 제한값이 아닌 목표값임을 명심할것. 물리적 한계는 pga_aggregate_limit 파라미터로 가능(12.1버전부터가능)
PGA과 초과할당되는지 V$PGASTAT뷰로 모니터링 가능
1. maximum PGA allocated 값이 aggregate PGA target parameter 값보다 훨씬 크면 pga_aggregate_target 초기화 파라미터에서 설정한 값이 적절하지 않은것임
2. over allocation count는 인스턴스가 기동한 이후 pga_aggregate_target 초기화 파라미터에서 지정한 값을 초과하여 PGA를 할당받은 횟수임. 최적값은 0이 되어야함.

select name,value,unit
  from v$pagstat
 where name in ('aggregate PGA target parameter','maximum PGA allocated','over allocation count');

1.2 V$PGASTAT[편집]

This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager. For example:

SELECT * FROM V$PGASTAT;
The output of this query might look like the following:

NAME                                                          VALUE UNIT
-------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                             41156608 bytes
aggregate PGA auto target                                  21823488 bytes
global memory bound                                         2057216 bytes
total PGA inuse                                            16899072 bytes
total PGA allocated                                        35014656 bytes
maximum PGA allocated                                     136795136 bytes
total freeable PGA memory                                    524288 bytes
PGA memory freed back to OS                              1713242112 bytes
total PGA used for auto workareas                                 0 bytes
maximum PGA used for auto workareas                         2383872 bytes
total PGA used for manual workareas                               0 bytes
maximum PGA used for manual workareas                       8470528 bytes
over allocation count                                           291
bytes processed                                          2124600320 bytes
extra bytes read/written                                   39949312 bytes
cache hit percentage                                          98.15 percent

1.2.1 aggregate PGA target parameter[편집]

기본값은 SGA 크기의 20% 입니다.
0으로 설정하면 PGA 메모리의 자동 관리가 비활성화됨.

1.2.2 PGA 자동 대상 집계(aggregate PGA auto target)[편집]

auto 모드에서 실행중인 작업 영역에 사용할 수있는 PGA 메모리 양
이 총계는 PGA_AGGREGATE_TARGET 파라메터 값과 현재 work area workload 에서 동적 변경고 Oracle에 의해 지속적으로 조정됩니다.
이 값이 PGA_AGGREGATE_TARGET의 값과 비교하여 작으면 시스템의 다른 구성 요소 (예 : PL/SQL 또는 Java 메모리)에서 많은 PGA 메모리가 사용되며 SORT 작업 영역에는 거의 남지 않습니다.
auto 모드로 실행중인 작업 영역에 충분한 PGA 메모리가 남아 있는지 확인 해야합니다.

1.2.3 글로벌 메모리 바운드(global memory bound)[편집]

AUTO 모드에서 실행되는 작업 영역의 최대 크기를 제공
작업 영역 워크로드의 현재 상태를 반영하도록 Oracle Database에 의해 지속적으로 조정됩니다.
시스템에서 활성 작업 영역 수가 증가하면 일반적으로 글로벌 메모리 바운드가 줄어 듭니다.
일반적으로 글로벌 바운드의 값은 1MB 미만으로 줄지 않아야합니다.
그렇다면 PGA_AGGREGATE_TARGET의 값을 늘려야합니다.

1.2.4 할당 된 총 PGA(total PGA allocated)[편집]

인스턴스가 할당 한 현재 PGA 메모리 양을 제공합니다.
Oracle은 이 숫자를 PGA_AGGREGATE_TARGET 값보다 작게 유지하려고합니다.
그러나 작업 영역 워크로드가 매우 빠르게 증가하거나 초기화 매개 변수 PGA_AGGREGATE_TARGET이 너무 작은 값으로 설정된 경우 할당 된 PGA가 해당 값을 작은 백분율로 짧은 시간 동안 초과 할 수 있습니다.

1.2.5 사용 가능한 총 PGA 메모리(total freeable PGA memory)[편집]

해제 할 수있는 할당 된 PGA 메모리 양을 나타냅니다.

1.2.6 자동 작업 영역에 사용 된 총 PGA(total PGA used for auto workareas)[편집]

자동 메모리 관리 모드에서 실행중인 작업 영역에서 현재 사용중인 PGA 메모리의 양
이 숫자는 PGA 메모리의 다른 소비자 (예 : PL/SQL 또는 Java)가 소비하는 메모리 양을 결정하는 데 사용할 수 있습니다.
PGA other = total PGA allocated - total PGA used for auto workareas

1.2.7 초과 할당 수(over allocation count)[편집]

이 통계는 인스턴스 시작시 누적됩니다.
PGA_AGGREGATE_TARGET의 값이 너무 작아서 이전 방정식의 PGA 다른 구성 요소를 수용하기에 너무 작으면 작업 영역 워크로드를 실행하는데 필요한 최소 메모리를 초과 할 경우 초과 할당 PGA 메모리가 발생할 수 있습니다.
이 경우 Oracle은 초기화 매개 변수 PGA_AGGREGATE_TARGET을 준수 할 수 없으며 추가 PGA 메모리를 할당해야합니다.
초과 할당이 발생하면 V$PGA_TARGET_ADVICE에서 제공 한 정보를 사용하여 PGA_AGGREGATE_TARGET의 값을 늘려야합니다.

1.2.8 처리 된 총 바이트 수(total bytes processed)[편집]

인스턴스 시작 이후 메모리를 많이 사용하는 SQL 연산자가 처리 한 바이트 수입니다.
예를 들어, 처리 된 바이트 수는 정렬 작업의 입력 크기입니다. 이 숫자는 캐시 적중률 메트릭을 계산하는 데 사용됩니다.

1.2.9 추가 바이트 읽기/쓰기(extra bytes read/written)[편집]

작업 영역을 최적으로 실행할 수없는 경우 입력 데이터에 대해 하나 이상의 추가 패스가 수행됩니다.
추가 바이트 읽기 / 쓰기는 인스턴스 시작 이후 이러한 추가 패스 중에 처리된 바이트 수를 나타냅니다.
이 숫자는 캐시 적중률을 계산하는 데에도 사용됩니다.
이상적으로는 처리된 총 바이트에 비해 작아야 합니다.

1.2.10 캐시 적중률(cache hit percentage)[편집]

이 메트릭은 PGA 메모리 구성 요소의 성능을 반영하기 위해 Oracle Database에서 계산됩니다.
인스턴스 시작부터 누적됩니다. 100 % 값은 인스턴스 시작 이후 시스템에서 실행 된 모든 작업 영역이 최적의 PGA 메모리를 사용했음을 의미합니다.
물론 이것은 이상적이지만 순수한 OLTP 시스템을 제외하고는 거의 발생하지 않습니다.
실제로 일부 작업 영역은 PGA 메모리의 전체 크기에 따라 one-pass 또는 multi-pass를 실행합니다.
작업 영역이 최적으로 실행될 수없는 경우 입력 데이터에 대해 하나 이상의 추가 패스가 수행됩니다.
이는 입력 데이터의 크기와 수행 된 추가 패스 수에 비례하여 캐시 적중률을 줄입니다.
1. 다음 예는 캐시 적중률이 추가 패스의 영향을 받는 방법을 보여줍니다.
2. 4개의 정렬 작업이 실행되었으며 3개는 작았으며 (1MB의 입력 데이터) 1개는 더 컸습니다 (100MB의 입력 데이터).

네 가지 작업으로 처리 된 총 바이트 수 (BP)는 103MB입니다. 작은 정렬 중 하나가 하나의 패스를 실행하면 1MB 이상의 입력 데이터에 대한 추가 패스가 수행됩니다. 이 1MB 값은 여분의 바이트 읽기 / 쓰기 또는 EBP 수입니다. 캐시 적중률은 다음 공식으로 계산됩니다.

BP x 100 / (BP + EBP)

이 경우 캐시 적중률은 99.03 %로 거의 100 %입니다. 이 값은 작은 정렬 중 하나만 추가 패스를 수행해야하는 반면 다른 모든 정렬은 최적으로 실행할 수 있다는 사실을 반영합니다. 따라서 캐시 적중률은 거의 100 %입니다. 1MB를 초과하는이 추가 패스는 작은 오버 헤드를 나타 내기 때문입니다. 반면에 큰 정렬이 단일 패스를 실행하는 정렬 인 경우 EBP는 1MB 대신 100MB이며 추가 패스는 훨씬 큰 영향을 미치기 때문에 캐시 적중률이 50.73 %로 떨어집니다.

2 sort area 크기 조정[편집]

- 세션 레벨 혹은 시스템 레벨에서 각 세션에 할달될 수 있는 총 크기 조정 가능 - 크기 조정 1차 목표 : 디스크 소트가 발생하지 않게 함 - 크기 조정 2차 목표 : onepass 소트 처리

2.1 PGA 메모리 관리 방식의 선택[편집]

About Work Area
1. 데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용되는 메모리 공간

1. sort_area_size, hash_area_size, bitmap_merge_area_size,create_bitmap_area_size 파라미터를 통해 조정
2. 9i부터 "Automatic PGA Memory Management" 기능 도입으로 사용자가 일일이 그 크기 조정 하지 않아도 됨
3. pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용가능한 PGA 메모리 총량을 지정
  1. 오라클이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리 할당
  2. 이 파라미터의 설정 값은 인스턴스 기동 중에 자유롭게 늘리거나 줄일 수 있음
  3. 이 기능을 활성화하려면 "workarea_size_policy=auto"로 해야 함
4. 9i부터 default "workarea_size_policy=auto" : *_area_size 파라미터는 모두 무시되며 오라클이 내부적으로 계산한 값 사용
5. 수동 PGA 메모리 관리 : 주로 트랜잭션이 거의 없는 야간에 대량의 배치 job 수행 시 효과적
  1. 해당 경우에 "workarea_size_policy=auto"로 사용하면 프로세스 당 사용할 수 있는 최대 크기제한되므로 work area를 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸 수 없게 되고, 결국 수 GB의 여유 메모리가 있어도 충분히 메모리를 활용하지 못해 작업 시간이 오래 걸릴 수 있음

"workarea_size_policy=manual"로 변경

2.2 자동 PGA 메모리 관리 방식 하에서 크기 결정 공식[편집]

workarea_size_policy = auto 모드에서 WORK AREA 크기
1. 단일 프로세스가 사용할 수 있는 최대 work area 크기는 인스턴스 기동 시 오라클에 의해 내부적으로 결정
2. _smm_max_size 파라미터 통해 확인 가능(value 단위 : KB)
3. Work Area 크기 조회

SELECT a.ksppinm name, b.ksppstvl VALUE 
  FROM sys.x$ksppi a, sys.x$ksppcv b 
 WHERE a.indx = b.indx AND a.ksppinm = '_smm_max_size' ; 

NAME VALUE(KB) 
-------------- -------------------- 
_smm_max_size 15974

1. 이 파라미터의 값을 결정하는 내부 계산식
  1. 9i ~ 10gR1 :
```
_smm_max_size=least((pga_aggregate_target * 0.5), (_pga_max_size * 0.5))
```
    - DB관리자가 지정한 pga_aggrate_target의 5%와 _pga_max_size 파라미터(maximum size of the PGA memory for a single process. 단위는 byte)의 50% 중 작은 값으로 설정
  2. 10gR2
    - pga_aggregate_target <= 500MB :
```
_smm_max_size = pga_aggregate_target * 0.2
```
    - 500MB < pga_aggregate_target <= 1000MB :
```
_smm_max_size = 100MB
```
    - pga_aggregate_target > 1000MB : _smm_max_size =
```
pga_aggregate_target * 0.1
```
    - ```
    _pga_max_size = _smm_max_size * 2
```
2. 병렬 쿼리의 각 슬레이브 프로세스가 사용할 수 있는 work area 총량은 _smm_px_max_size 파라미터(KB)에 의해 제한 됨
3. SGA : sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당
4. PGA : 자동 PGA 메모리 관리 기능을 사용하더라도 pga_aggregate_target 크기 만큼의 메모리를 미리 할당하지 않음
5. pga_aggregate_target 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당 받을 수 있는 work area의 총량을 제한하는 용도임

2.3 수동 PGA 메모리 관리 방식으로 변경 시 주의사항[편집]

workarea_size_policy=manual모드로 설정한 프로세스는 pga_aggregate_target 파라미터 제약 받지 않음
sort area와 hash area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼 페이징(paging)이 발생하면서 시스템 전체 성능 저하(페이징이 심하면 시스템 마비까지 가능)
*_area_size : 0 ~ 2147483647(2G - 1byte)
workarea_size_policy=manual
1. 병렬 쿼리를 사용하면 각 병렬 슬레이블 별로 sort_area_size크기 만큼의 sort area 사용 가능
2. sort order by나 해시 조인 등을 수행할 때는 사용자가 지정한 DOP(the drgree of parallelism)의 2배수만큼의 병렬 슬레이브가 떠서 작업 수행
3. paralle1(t 64)의 경우 128개의 프로세스가 각각 최대 2GB의 sort area 사용
4. manual 모드에서 병렬 degree를 크게 설정할 때는 sort_area_size와 hash_area_size를 반드시 확인

(sort order by를 수행할 때 한쪽 서버 집합은 데이터 블록을 읽어 반대편 서버 집합에 분배하는 역학만 하므로 위 쿼리만으론 최대 64*2GB의 sort area가 필요)

1. 병렬 쿼리 테스트
  1. 결과 : 시스템의 상태에 따라 작업을 수행하는 병렬 슬레이브의 수가 다를 수 있다.

||parallel Degree||parallel Slave|| |8|9| |16|5| |32|5| |64|5|

1. 1. 쿼리

alter session set workarea_size_policy = manual; 
alter session set sort_area_size = 2147483647; 

SELECT /*+ full(t) parallel(t 64) */ 
* 
from t 
ORDER BY object_name;

2.4 PGA_AGGREGATE_TARGET 의 적정 크기[편집]

오라클 권고
1. OLTP : (Total Physical Memory * 80%) * 20%
2. DSS : (Total Physical Memory * 80%) * 50%
애플리케이션 특성에 따라 모니터링 결과를 바탕으로 세밀한 조정 필요
1. 일반적인 목표 : Optimal 소트 방식으로 수행, 나머지(10%미만)만 onepass 소트 방식으로 수행
2. 시스템에 multipass 소트가 종종 발생하는 것으로 측정되면 크기를 늘리거나 튜닝이 필요한 상태임

2.5 sort area 할당 및 해제[편집]

sort_area_size
1. 8.0 이전 : 소트가 수행되는 시점에 sort_area_size 크기만큼의 메모리 미리 할당
2. 8.0 이후 : db_block_size 크기에 해당하는 chunk단위로 필요한 만큼 조금씩 할당

sort_area_size는 할당할 수 있는 최대 크기를 지정하는 파라미터로 바뀜

PGA
1. 8i 이전 : PGA공간은 프로세스가 해제될 때까지 OS에 반환하지 않음
2. 9i 이후 : 자동PGA 메모리 관리 방식 도입으로 프로세스가 더 이상 사용하지 않는 공간을 즉시 반환함으로써 다른 프로세스가 사용 가능

(버그로 인해 PGA메모리가 반환되지 않는 경우가 종종 있음)

실제 Sort Area 가 할당되고 해제 되는 과정 측정
1. 최초 : 쿼리 수행 직전
2. 수행도중 : 쿼리가 수행 중이지만 아직 결과가 출력되지 않은 상태(--> 값이 계속 변함)
3. 완료 후 : 결과를 출력하기 시작했지만 데이터를 모두 fetch하지 않은 상태
4. 커서를 닫은 후 : 정렬된 결과집합을 끝까지 fetch하거나 다른 쿼리를 수행함으로써 기존 커서를 닫은 직후
5. 결과 : 수행도중과 완료 후에 UGA, PGA 크기가 max 값을 밑도는 이유 : 소트해야 할 총량이 할당받을 수 있는 sort area 최대치를 초과하기 때문.

그 때마다 중간 결과집합(sort run)을 디스크에 저장하고 메모리에 반환했다가 필요한 만큼 다시 할당받음

1. AUTO 모드로 설정한 프로세스는 이 파라미터의 제약을 받음
  1. PGA 및 UGA 크기 조회 쿼리

SELECT ROUND( MIN( decode( n.name , 'session pga memory' , s.value ) ) /1024 ) "PGA(KB)" , 
ROUND( MIN( decode( n.name , 'session pga memory max' , s.value ) ) /1024 ) "PGA_MAX(KB)" , 
ROUND( MIN( decode( n.name , 'session uga memory' , s.value ) ) /1024 ) "UGA(KB)" , 
ROUND( MIN( decode( n.name , 'session uga memory max' , s.value ) ) /1024 ) "UGA_MAX(KB)" 
FROM v$statname n 
   , v$sesstat s 
WHERE ( name LIKE '%uga%' 
OR name LIKE '%pga%' ) 
AND n.statistic# = s.statistic# 
AND s.sid = :sid

1. 1. 자동 PGA 메모리 관리 방식으로 시스템 레벨에서 사용할 수 있는 총량 제한 - pga_aggregate_target = 24M

alter system set pga_aggregate_target = 24M; 
System altered. 
CREATE TABLE t_emp AS 
SELECT * 
FROM emp , 
( 
SELECT ROWNUM no 
FROM dual 
CONNECT BY LEVEL <= 100000 
) ; 
Table created.

1. 1. 정렬이 필요한 쿼리 수행

SELECT * 
FROM t_emp 
ORDER BY empno ;

1. 1. 결과

||단계||PGA(KB)||PGA_MAX(KB)||UGA(KB)||UGA_MAX(KB)|| ||최초|572|3004|280|657| ||수행 도중|764|3004|344|657| ||완료 후|636|3004|344|657| ||커서를 닫은 후|572|3004|280|657|

1. manual 모드로 설정한 프로세스는 이 파라미터의 제약을 받지 않음

alter session set workarea_size_policy = MANUAL; 
alter session set sort_area_size = 52428800; 
alter session set sort_area_retained_size = 52428800; 

SELECT * 
FROM t_emp 
ORDER BY empno ; 


### 결과 
||단계||PGA(KB)||PGA_MAX(KB)||UGA(KB)||UGA_MAX(KB)|| 
||최초|636|3004|280|657| 
||수행 도중|44796|44796|44264|44264| 
||완료 후|2812|52988|2393|47205| 
||커서를 닫은 후|572|52988|280|47205|

DB CAFE

(PGA SIZE 조정에서 넘어옴)

목차