PGA와 UGA

PGA에 담기는 두 영역, 전용 서버와 공유 서버에서 UGA가 놓이는 위치, 세션의 PGA 사용량을 확인하는 SQL

PGAUGA전용 서버공유 서버V$PGASTAT

#핵심 요약

PGA에는 개인 SQL 영역(private SQL area)과 작업 영역(work area)이 있음

UGA의 위치는 서버 방식에 따라 다름
전용 서버에서는 PGA 안, 공유 서버에서는 SGA 안(Large Pool 또는 공유 풀)임

작업 영역은 두 방식 모두 PGA 안에 있음

세션의 PGA 사용량은 V$PROCESS와 V$SESSION, 또는 V$SESSTAT으로 확인함

#개념과 동작 원리

#PGA에 담기는 두 영역

개인 SQL 영역(private SQL area): 지금 실행 중인 커서의 상태, 바인드 변수 값, 질의 진행 위치 같은 정보임
실행 계획 자체는 공유 풀에 하나만 있지만, 그 계획을 어디까지 실행했는가는 세션마다 다르므로 따로 가져야 함
작업 영역(work area): 정렬, 해시 조인, 비트맵 병합처럼 중간 결과를 펼쳐 놓고 작업해야 하는 연산이 쓰는 공간임

Oracle Database 19c는 개인 SQL 영역과 SQL 작업 영역을 PGA의 구성으로 설명함
이 구분은 모든 세션 상태가 항상 PGA에 있다는 뜻은 아님
UGA는 전용 서버 접속에서 PGA에, 공유 서버 접속에서는 SGA에 위치함

#전용 서버와 공유 서버에서 UGA의 위치

구분	전용 서버	공유 서버
UGA 위치	PGA 안	SGA 안(Large Pool 또는 공유 풀)
작업 영역 위치	PGA 안	PGA 안
세션과 프로세스	1 대 1	요청마다 달라질 수 있음

공유 서버에서는 세션 하나를 여러 서버 프로세스가 번갈아 처리할 수 있으므로, 세션별 정보(UGA)는 어느 프로세스에서나 볼 수 있는 SGA에 있어야 함
이 이유는 인스턴스와 SGA 구성 요소의 Large pool 항목에 있는 설명과 같은 맥락임

#주요 용어와 구성 요소

용어	설명
PGA	서버 프로세스가 갖는 메모리 영역
UGA	세션 정보를 담는 영역 서버 방식에 따라 PGA 또는 SGA 안에 있음
private SQL area	실행 중인 커서 상태, 바인드 변수 값, 질의 진행 위치
work area	정렬, 해시 조인, 비트맵 병합의 중간 결과 영역
`PGA_AGGREGATE_TARGET`	PGA 전체 목표 크기 파라미터

#필수 명령어 또는 SQL

#PGA 파라미터

SQL*Plus조회만 함

SHOW PARAMETER pga_aggregate

결과 해석: 개별 실행 출력은 기재 없음

#내 세션에 붙어 있는 서버 프로세스와 PGA 크기

SQL조회만 함

SELECT p.spid,
       ROUND(p.pga_used_mem/1024/1024, 2)  used_mb,
       ROUND(p.pga_alloc_mem/1024/1024, 2) alloc_mb,
       ROUND(p.pga_max_mem/1024/1024, 2)   max_mb
  FROM v$process p, v$session s
 WHERE p.addr = s.paddr
   AND s.sid = SYS_CONTEXT('USERENV','SID');

결과 해석: 개별 실행 출력은 기재 없음

#통계 뷰로 세션의 PGA와 UGA 크기 확인

SQL조회만 함

SELECT n.name, ROUND(st.value/1024, 1) KB
  FROM v$sesstat st, v$statname n
 WHERE st.statistic# = n.statistic#
   AND n.name IN ('session pga memory', 'session uga memory')
   AND st.sid = SYS_CONTEXT('USERENV','SID');

결과 해석: 개별 실행 출력은 기재 없음

#인스턴스 전체의 PGA 지표

SQL조회만 함

SELECT name, value FROM v$pgastat
 WHERE name IN ('aggregate PGA target parameter', 'total PGA allocated',
                'over allocation count', 'cache hit percentage');

결과 해석:

V$PGASTAT의 실습 출력 예시

실습 기록의 출력이며 환경은 확인 불가

출력 예시SQL*Plus 출력 기록조회만 함

NAME                                    VALUE
------------------------------ --------------
aggregate PGA target parameter      802160640
total PGA allocated                 297836544
over allocation count                       0
cache hit percentage                      100

#현재 세션의 SID, SERIAL#, OS PID

SQL조회만 함

SELECT s.sid, s.serial#, s.username, p.spid os_pid, s.logon_time
  FROM v$session s, v$process p
 WHERE s.paddr = p.addr
   AND s.sid = SYS_CONTEXT('USERENV','SID');

결과 해석:

실습에서는 이 SQL에 "현재 접속 DB 세션의 SID, SERIAL# 및 서버 프로세스 SPID 조회(접속 계정 확인 필요)"라고 적었음

#구문과 옵션 설명

구문	의미	바꿀 입력값	결과 해석
`p.addr = s.paddr`	세션(`V$SESSION`)과 그 세션의 서버 프로세스(`V$PROCESS`)를 연결	없음	세션 1개에 프로세스 1개가 대응하는 행을 돌려줌
`s.sid = SYS_CONTEXT('USERENV','SID')`	조회 대상을 현재 세션으로 제한	다른 세션을 보려면 SID 값을 지정	결과는 자기 세션의 값
`p.pga_used_mem`, `pga_alloc_mem`, `pga_max_mem`	프로세스의 PGA 사용량, 할당량, 최대치(MB로 환산)	없음	환산식은 `/1024/1024`, 소수 둘째 자리 반올림
`n.name IN ('session pga memory', 'session uga memory')`	세션 PGA와 UGA 크기 통계	통계 이름	값은 KB로 표시(`/1024`, 소수 첫째 자리)
`V$PGASTAT`의 `over allocation count`, `cache hit percentage`	PGA 목표 초과 횟수, 캐시 적중률	확인할 통계 이름	실습 예시에서 over allocation count 0이고 cache hit 100이면 지금은 정렬이 디스크로 넘어가지 않는다고 해석함

#어떤 뷰를 볼 것인가

세션 메모리 점검 항목	조회 뷰
정렬이 디스크로 넘어가는가	`V$PGASTAT`의 `cache hit percentage`
내 세션의 서버 프로세스와 PGA 크기	`V$PROCESS` + `V$SESSION`
세션이 쓰는 PGA, UGA 크기	`V$SESSTAT` + `V$STATNAME`

#사용 조건과 주의사항

UGA의 위치는 서버 방식에 달려 있음
전용 서버와 공유 서버를 판별하는 방법은 기재 없어 확인 불가

V$SESSION 기반 SQL은 모두 SYS_CONTEXT('USERENV','SID')로 현재 세션만 대상으로 함
다른 세션을 보려면 조건을 바꿔야 함