[Oracle] SQL튜닝 - 5: SQL Workarea 최적화

 

[SQL Workarea]

 

  -  SGA 영역을 공유하는 가공 전 단계와 달리, 서버 프로세스가 각 세션별로 독립적으로 할당받는 개인 메모리 공간인 PGA 내에 존재

  -  SQL Workarea가 가동되는 대표적인 연산 종류

    ① Sort Area : ORDER BY, GROUP BY, DISTINCT, UNION 등 정렬이 요구되는 연산

    ② Hash Area : 대용량 테이블 간의 동등 조인을 처리하는 Hash Join 연산

    ③ Bitmap Area : 비트맵 인덱스 생성 및 병합 연산

 

[Workarea 크기에 따른 데이터 처리 3단계]

  - 가공해야 할 데이터의 총량과 프로세스에 할당된 SQL Workarea 메모리 크기의 대비에 따라 3가지 상태(Operation Mode)로 연산이 수행

1) Optimal (옵티멀 모드)   used_mem (0)

   - 연산에 필요한 데이터 전체 크기보다 SQL Workarea 메모리 공간이 더 큰 상태

   - 모든 가공 작업이 디스크를 전혀 거치지 않고 메모리 내에서만 수행

   - 디스크 I/O 오버헤드가 전혀 없으므로 가장 이상적이며 속도가 압도적으로 빠름

 

2) One-Pass (원패스 모드)  used_mem (1)

   - 가공해야 할 데이터 양이 Workarea 메모리 크기를 초과한 상태

   - 메모리가 부족하므로 정렬 대상 데이터의 일부를 메모리 안에서 1차 정렬(Sort Run)한 뒤, 디스크의 임시 공간인 Temporary Segment(임시 세그먼트)에 잠시 내려놓음

    - 이후 메모리와 디스크 간의 읽기/쓰기를 1회(One-Pass)만 교환하며 최종 결과를 병합(Merge)함

 

3) Multi-Pass (멀티패스 모드)  used_mem (n)

    - 가공할 데이터가 SQL Workarea 크기보다 극단적으로 많을 때 발생

    - 메모리 공간이 부족하여 임시 세그먼트에 데이터를 썼다가 읽고, 다시 정렬해서 또 다른 임시 세그먼트에 쓰는 작업을 여러 번(n번) 반복하는 상태

    - 디스크 병목(I/O Thrashing)이 극도로 발생하여 시스템 마비를 유발하므로, 튜닝을 통해 반드시 피하는 것이 좋음

 

 

[SQL Workarea 관리 기법]

 

1) 자동 관리 (Automatic Memory Management) 

• 오라클 메모리 관리자가 전체 시스템 상황을 감시하며, 정렬이 많이 필요한 프로세스에는 Workarea를 더 넓혀주고 단순 조회를 하는 세션에는 축소하는 등 동적으로 메모리를 배분
• 특별한 사유가 없다면 자동 관리를 사용하는 것을 추천함
• 설정 파라미터

-- 전체 세션이 사용할 총 PGA 메모리 목표 합계치 지정
ALTER SYSTEM SET pga_aggregate_target = 4G;

-- Workarea 관리 정책 자동 세팅
ALTER SYSTEM SET workarea_policy_auto = AUTO;

 

2) 수동 관리 (Manual Memory Management)

• 레거시 방식이거나, 특정 대용량 배치(Batch) 쿼리에 한하여 강제로 대규모 메모리를 주고 싶을 때 사용
• 고정 크기만 할당되므로 자원 낭비 및 One-Pass / Multi-Pass 위험 상존
• 설정 파라미터

-- 관리 정책 수동으로 전환
ALTER SESSION SET workarea_policy_auto = MANUAL;

-- 개별 연산 영역 크기 고정 (Ex: 100MB, 200MB)
ALTER SESSION SET sort_area_size = 104857600; 
ALTER SESSION SET hash_area_size = 209715200;

 

 

[V$SQL_WORKAREA_ACTIVE 활용법]

  - 현재 어떤 세션이 대량의 정렬/조인을 일으키며 Temporary Segment를 사용하고 있는지 모니터링할 때 사용

  - v$sql_workarea_active 뷰와 v$session 뷰를 조인하여 가동 중인 연산과 할당된 메모리 크기를 조회 가능

  - 조회 방법

SELECT 
    s.sid, 
    s.serial#, 
    wa.operation_type, 
    wa.policy,
    ROUND(wa.work_area_size / 1024 / 1024, 2) AS "Allocated_MB(현재)",
    ROUND(wa.max_mem_used / 1024 / 1024, 2)   AS "Max_Mem_MB(최대)"
FROM v$sql_workarea_active wa
JOIN v$session s ON wa.sid = s.sid
ORDER BY wa.work_area_size DESC;