[Oracle] PGA 영역

이론1. pga 메모리 영역의 역할은?

💡 정렬작업과 해쉬조인

이론2. 정렬을 일으키는 SQL은?

💡
1. ORDER BY
2. SORT MERGE JOIN
3. CREATE INDEX 생성문 실행시
4. 데이터 분석함수 사용시

이론3. PGA 영역을 DBA가 알아야하는 이유

💡 개발자들이 과도한 정렬을 일으키는 악성 SQL을 수행하게 되면
PGA 영역이 꽉차면서 SQL의 성능이 느려집니다.

---

실습1. PGA 영역의 현재 사이즈를 확인하시오

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 625M

---

실습2. PGA 영역의 사이즈를 700M로 늘리시오

scope=both
: 현재 메모리에도 반영하면서
700m를 컴퓨터를 끄고 올리면서도 자동반영하겠다

SQL> alter system set pga_aggregate_target=700m scope=both;

시스템이 변경되었습니다.

SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL>
SQL> startup
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 1979709008 bytes
Fixed Size                  8898128 bytes
Variable Size             520093696 bytes
Database Buffers         1426063360 bytes
Redo Buffers               24653824 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
SQL>
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 700M

💡 해쉬조인이 많이 일어나고 과도한 정렬작업이 많이 일어나는 dw같은 db는 pga 사이즈를 늘려줘야함

---

이론4. SGA 영역과 PGA 영역의 사이즈를 자동 조절하는 대표 오라클 파라미터가 무엇인가요?

💡 memory_target

---

실습3. 현재 memory_target의 사이즈를 확인하시오

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 0
SQL>

💡 위의 파라미터가 0으로 되어있으면 sga_target과 pga_aggregate_target을 직접 적용해서 설정해줘야함.
만약 memory_target에 크기를 설정해놓으면 오라클이 알아서 sga_target과 pga_aggregate_target을 자동조절합니다.

dba가 memory_target 이라는 파라미터 하나만 설정해 놓으면 나머지는 오라클이 다 알아서 합니다.

낮시간: sga > pga
밤시간: sga < pga

memory_target를 물리적 메모리의 40~50%로 잡아주기를 권장하니까 잡아놓으면 오라클이 알아서 자동조절합니다.

---

실습4. 현재 pga 영역의 사용 현황을 확인하시오

ed pga.sql
@pga

col current clear

SELECT b.value "Current", a.value "Max", (a.value - b.value) "Diff"
       FROM V$PGASTAT a, V$PGASTAT b
       WHERE a.name = 'aggregate PGA target parameter' AND b.name = 'total PGA inuse';

---

실습5. pga_aggregate_target을 800m 로 늘리시오

SQL> alter system set pga_aggregate_target=800m scope=both;

시스템이 변경되었습니다.

SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

SQL> startup
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 1979709008 bytes
Fixed Size                  8898128 bytes
Variable Size             520093696 bytes
Database Buffers         1426063360 bytes
Redo Buffers               24653824 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

---

문제. 아래의 과도한 정렬작업을 scott 유져에서 수행하면 pga 영역의 사용율이 올라가는지 확인하시오

SELECT   e1.sal
   from  emp  e1, emp  e2, emp  e3, emp  e4, emp  e5, emp  e6, emp  e7
   order by e3.sal  desc;

ㄴ sql developer에서 위의 코드를 돌리고

SQL> @pga
SQL> /

---

🔎 퀴즈

1. 서버 또는 백그라운드 프로세스의 데이터 및 제어 정보를 포함하는 메모리 영역은?

1) Shared Pool
2) PGA
3) 버퍼 캐시
4) 유저 세션 데이터

2. 데이터 파일에서 데이터베이스 버퍼 캐시로 읽혀지는 것은?

1) 행
2) 변경 사항
3) 블록
4) SQL

답:
1. 2번 PGA
2. 3번 블록