🖥️ 들어가며
📌 소트 발생 최소화 전략
- 소트 미발생 SQL 작성 최적화: UNION은 UNION ALL로, DISTINCT는 EXISTS 서브쿼리로, 불필요한 COUNT 제거
- 인덱스로 소트 연산 대체: SORT ORDER BY 및 GROUP BY 대체
- 소트 영역 사용 최소화: 소트 완료 후 데이터 가공, TOP-N 쿼리 형태로 작성(ROWNUM, STOPKEY)
📌 PGA 메모리 관리
- 자동 및 수동 관리: Oracle 9i부터 자동 기능 도입 (workarea_size_policy = auto 또는 manual)
- 수동 PGA 관리 시 주의사항: 수동 PGA + 병렬 쿼리 함께 사용 시, 과도한 메모리 사용 및 시스템 리소스 고갈 가능성 높음
✏️ 1. 소트가 발생하지 않도록 SQL 작성
- ⭐⭐⭐
1. Union → Union All로 대체
2. Distinct → Exists 서브쿼리로 대체
3. 불필요한 Count 연산 제거
- 소트 미발생 SQL 1: Union → Union All로 대체
- PK 컬럼: empno
- empno를 Select List에 포함하므로 두 집합간 중복 가능성이 전혀 없습니다.
=> 따라서 Union을 Union All로 대체해도 아무런 문제가 없습니다.
-- Union: SORT UNIQUE -> 소트 오퍼레이션 발생
select empno, job, mgr from emp where deptno = 10
union
select empno, job, mgr from emp where deptno = 20;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 152 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 8 | 152 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 57 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_IDX | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_IDX | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
-- Union All: 소트 오퍼레이션 미발생(empno가 PK 컬럼이므로 결과 데이터는 위와 동일)
select empno, job, mgr from emp where deptno = 10
union all
select empno, job, mgr from emp where deptno = 20;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 152 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 57 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_IDX | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_IDX | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
- 소트 미발생 SQL 2: Distinct → Exists 서브쿼리로 대체
- Exists
- 서브쿼리의 결과 반환 여부만 확인합니다. (어떤 값을 반환하는지는 중요 X)
SELECT 1: 어떤 행이 존재하는지만 확인할 수 있음 - 한 건이라도 존재하면 Ture를 리턴 (없으면 False를 리턴 / ↔ Not Exists)
- 서브쿼리의 결과 반환 여부만 확인합니다. (어떤 값을 반환하는지는 중요 X)
- Distinct 사용한 SQL
select distinct 과금연월 from 과금 where 과금연월 <= :yyyymm and 지역 like :reg || '%'- 입력한 과금연월(yyyymm) 이전에 발생한 과금데이터를 모두 스캔 → Distinct로 중복값 제거
- 각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 됩니다.
- Exists 서브쿼리로 대체한 SQL
select 연월 from 연월테이블 a where 연월 <= :yyyymm and exists (select 'x' from 과금 where 과금연월 = a.연월 and 지역 like :reg || '%' )
- 연월테이블을 먼저 드라이빙 → where절 조건: 특정 날짜 이전의 연월 선택 → 과금테이블을 Exists 서브쿼리로 필터링하는 방식입니다.
- 서브쿼리 내 조건
- 과금연월 = a.연월: 메인쿼리 연월과 일치하는지 확인
- 지역 like :reg || '%': 특정 지역 패턴과 일치하는지 확인
- 서브쿼리 내 조건
- 인덱스: 과금연월 + 연월로 구성하면 최적으로 수행할 수 있습니다. (서브쿼리에서 필요로 하는 모든 데이터를 인덱스만으로 얻을 수 있음)
- 연월테이블을 먼저 드라이빙 → where절 조건: 특정 날짜 이전의 연월 선택 → 과금테이블을 Exists 서브쿼리로 필터링하는 방식입니다.
- Exists
-- Distinct
SELECT DISTINCT M.M_CODE, M.M_NM
FROM T_ORDER O, T_PRODUCT P, T_MANUF M
WHERE O.ORDER_DT >= '20090101'
AND O.PROD_ID = P.PROD_ID
AND M.M_CODE = P.M_CODE;
-- 실행계획: NESTED LOOPS 3번, HASH UNIQUE 2번
-- Exists 서브쿼리로 대체
SELECT M.M_CODE, M.M_NM
FROM T_MANUF M
WHERE EXISTS (
SELECT 1
FROM T_PRODUCT P
WHERE M_CODE = M.M_CODE
AND EXISTS (
SELECT 1
FROM T_ORDER O
WHERE ORDER_DT >= '20090101'
AND PROD_ID = P.PROD_ID));
-- 실행계획: NESTED LOOPS SEMI 2번 (Sort 연산 없음)
- 소트 미발생 SQL 3: 불필요한 Count 연산 제거
- 데이터 존재 여부만 확인하면 되는데, 불필요하게 전체 건수를 Count하는 경우 이를 제거합니다.
- 1건 이상 존재한다면 더 이상 읽지 않도록 rownum <= 1을 줍니다.
-- 기존: 전체 Count
declare
l_cnt number;
begin
select count(*) into l_cnt
from member
where memb_cls = '1'
and birth_yyyy <= '1950';
if l_cnt > 0 then
dbms_output.put_line('exists');
else
dbms_output.put_line('not exists');
end if;
end;
-- 변경: rownum <=1 -> select 1
declare
l_cnt number;
begin
select 1 into l_cnt
from member
where memb_cls = '1'
and birth_yyyy <= '1950'
and rownum <= 1;
dbms_output.put_line('exists');
exception
when no_data_found then
dbms_output.put_line('not exists');
end;
✏️ 2. 인덱스를 이용한 소트 연산 대체
- ⭐⭐⭐
1. Sort Order By 대체
2. Sort Group By 대체 - 인덱스로 소트 연산 대체 1: Sort Order By 대체
- 인덱스: region + custid
=> Sort Order By 연산을 대체할 수 있습니다.- where region = 'A' 조건으로 인해 인덱스에서 'A' region의 데이터만 선택됩니다.
- 선택된 데이터는 이미 custid로 정렬되어 있기 때문에, Sort 연산을 생략할 수 있습니다.
- Sort Order By 대체가 유용한 상황
- 대량 데이터: 정렬해야 할 데이터가 매우 많을 때
- 부분 조회: 전체 결과 중 일부만 필요할 때 (페이징 처리)
- 예를 들어 첫 10개의 결과만 필요하다면
- 인덱스를 사용하면 10개의 레코드만 읽고 멈출 수 있지만
- 일반적인 정렬은 모든 데이터를 정렬한 후 → 10개를 선택해야 합니다.
- 인덱스: region + custid
select custid, name, resno, status, tel1
from customer
where region = 'A'
order by custid
- 인덱스로 소트 연산 대체 2: Sort Group By 대체
- 인덱스 구성
- region을 선두 컬럼으로 하는 결합 인덱스 (인덱스: region + 다른컬럼)
- 또는 region만의 단일 컬럼 인덱스
- 인덱스가 이미 region별로 정렬되어 있기 때문에, 추가적인 정렬 없이 그룹화를 수행할 수 있습니다.
- 인덱스 구성
-- 인덱스: region
select region, avg(age), count(*)
from customer
group by region
-- 실행계획
SORT GROUP BY NOSROT -- 정렬없이 그룹화가 수행됨
✏️ 3. 소트 영역을 적게 사용하도록 SQL 작성
- 소트 연산이 불가피하다면 메모리 내에서 처리되도록 합니다.
- ⭐⭐⭐
1. 소트 완료 후 데이터 가공
2. Top-N 쿼리 - 소트 영역 적게 SQL 작성 1: 소트 완료 후 데이터 가공
- 데이터 크기: 원본 데이터가 가공된 데이터보다 일반적으로 크기가 작습니다.
- 메모리 사용: 소트 영역에 작은 크기의 데이터를 저장하므로 메모리 사용량이 줄어듭니다.
-- 1번 쿼리: 가공한 후 -> 소트 영역에 담음
select lpad(empno, 10) || lpad(ename, 30) || lpad(job, 30)
from emp
where sal <= 2000
order by job
-- 2번 쿼리: 가공되지 않은 상태 -> 소트 영역에 담음 -> 정렬 후 가공
select lpad(empno, 10) || lpad(ename, 30) || lpad(job, 30)
from (select empno
, ename
, job
from emp
where sal <= 2000
order by job)
- 소트 영역 적게 SQL 작성 2: Top-N 쿼리
- Top-N 쿼리 형태로 작성하면 소트 연산(=값 비교) 횟수, 소트 영역 사용량을 최소화할 수 있습니다.
- 만약 인덱스: 종목코드 + 거래일시 -> 존재했다면 Order By 연산을 대체할 수 있습니다.
- rownum 조건: rownum으로 지정한 N건에서 멈춥니다.
- 실행계획의 COUNT STOPKEY가 이를 의미합니다.
-- Top-N 쿼리 형태: rownum <= 10 -> 10건에서 멈춤 (실행계획: COUNT STOPKEY)
select *
from (select 거래일시, 체결건수, 체결수량, 거래대금
from 시간별종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20080304'
order by 거래일시
)
where rownum <= 10- Top-N 쿼리의 소트 부하 경감 원리
- 인덱스: 종목코드 + 거래일시 가 없다면
종목코드를 선두로 갖는 다른 인덱스를 사용하거나, Full Table Scan 방식으로 처리됩니다. (이때 정렬 작업 불가피) - Top 10 즉, rownum <= 10이면
- 소트 영역 사용량 줄임: 10개의 레코드를 담을 배열만 할당한 뒤, 처음 10개 레코드를 정렬된 상태로 담습니다.
- 소트 연산 횟수 줄임: 이후 읽는 레코드에 대해서 맨 우측의 가장 큰 값과 비교해서, 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도합니다.
- 인덱스: 종목코드 + 거래일시 가 없다면
- Top_N 쿼리 알고리즘이 작동하지 못하는 경우
- ⭐⭐⭐ rownum
- 조건절에 rownum이 있어야 합니다.
- Pseudo Column은 DBMS가 인지하지 못합니다. (r_num, rnum 등은 불가)
- ⭐⭐⭐ rownum
-- 조건절: ROWNUM
SELECT *
FROM (SELECT ROWNUM NO
, 거래일시
, 체결건수
, 체결수량
, 거래대금
FROM (SELECT 거래일시
, 체결건수
, 체결수량
, 거래대금
FROM 시간별종목거래
WHERE 종목코드 = :isu_cd
AND 거래일시 >= :trd_time
ORDER BY 거래일시
)
WHERE ROWNUM <= 100 ---------------- 빼면 Top-N 쿼리 미작동
)
WHERE NO BETWEEN 91 AND 100
-- PSEUDO COLUMN 사용불가
SELECT M_CODE, PROD_ID, CUST_ID, ORDER_DT
FROM (SELECT X.*, ROWNUM R_NUM ------------ Pseudo Column
FROM (SELECT M_CODE, PROD_ID, CUST_ID, ORDER_DT
FROM T_ORDER
ORDER BY ORDER_DT) X
)
WHERE R_NUM <= 10; -------------------------- DBMS가 Pseudo Column을 인지 X, STOPKEY 작동 X- 윈도우 함수에서 Top-N 쿼리 처리
- rank(), row_number()를 쓰면 Top-N 쿼리 알고리즘이 작동해 max() 함수를 쓸 때보다 소트 부하를 줄여줍니다.
- rank(), row_number()
- 내부적으로 Top-N 알고리즘을 사용합니다.
- 전체 데이터셋을 정렬하지 않고도 상위 N개의 결과를 찾아낼 수 있습니다.
- max()
- 전체 데이터셋에 대해 window sort 연산을 수행해야 합니다.
=> 모든 데이터를 메모리에 로드하고 정렬해야 하므로 리소스를 많이 사용합니다.
- 전체 데이터셋에 대해 window sort 연산을 수행해야 합니다.
✏️ 4. 소트 영역 크기 조정
- Work Area Size 관리 파라미터
- sort_area_size
- hash_area_size
- bitmap_merge_area_size
- create_bitmap_area_size
- Work Area Size 관리 방식
- SQL Server: 소트 영역을 수동으로 조정하지 않습니다.
- Oracle 8i: sort_area_size 파라미터로 조정합니다.
-- Oracle 8i
alter session set sort_area_size = 1048576;- Oracle 9i
- 자동 PGA 메모리 관리 기능이 도입되었습니다. (Automatic PGA Memory Management)
- workarea_size_policy = auto => DBMS 내 자동 계산
- workarea_size_policy = manual => Work Area Size 수동 전환 가능
- 대량 배치 수행 시 sort 및 hash area size를 필요한 만큼 크게 조정할 수 있습니다.
- 최대: 2,147,783,487 Byte
-- 대량 배치 작업일 경우, hash area 및 sort area size 조정
alter session set workarea_size_policy = manual;
alter session set hash_area_size = 2,147,483,647; ------ 대량 Hash Join 작업 필요 시
alter session set sort_area_size = 2,147,483,647; ------ 대량 정렬 작업 필요 시- ⚠️ 수동 PGA 메모리 관리 방식 변경 시 주의사항 (workarea_size_policy = manual)
- Parallel Query 수행 시 병렬 슬레이브별로 sort_area_size나 hash_area_size를 설정합니다.
- Parallel Degree의 2배만큼 슬레이브 프로세스가 작동합니다.
- 각 슬레이브 프로세스는 개별적으로 설정된 sort_area_size나 hash_area_size만큼 메모리를 할당 받습니다.
- 아래 쿼리는 2GB * 8 (Parallel Degree) * 2
=> 32GB의 sort area가 잡힙니다. - 수동 PGA와 병렬 쿼리를 함께 사용할 때 문제점
- 과도한 메모리 사용
- sort_area_size를 2GB로 설정하고, Parallel Degree를 8로 지정했습니다.
- 결과적으로 2GB * 8 * 2 = 32GB의 메모리가 할당됩니다.
- 시스템 리소스 고갈
- 이렇게 큰 메모리 할당은 시스템 리소스를 급격히 소모할 수 있습니다.
즉, 다른 프로세스나 쿼리의 실행에 악영향을 미칠 수 있습니다.
- 이렇게 큰 메모리 할당은 시스템 리소스를 급격히 소모할 수 있습니다.
- 예측 불가능한 성능
- 수동 PGA 설정과 병렬 쿼리를 함께 사용하면 메모리 사용량을 예측하기 어렵습니다.
=> 성능 튜닝과 리소스 관리를 복잡하게 만듭니다.
- 수동 PGA 설정과 병렬 쿼리를 함께 사용하면 메모리 사용량을 예측하기 어렵습니다.
- 정리하면
- 수동 GPA와 병렬 쿼리를 함께 사용하면 예상하지 못한 대량 메모리 사용으로 시스템 안전성, 성능에 심각한 영향을 줄 수 있으므로 권장하지 않습니다.
- 과도한 메모리 사용
-- 수동 PGA + Parallel 예시
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 2147483647;
select /*+ full(a) parallel(a 8)*/ *
from 대형테이블 a
order by 배송일자;
📒 정리하면
- 소트 발생 최소화 전략
- 소트 미발생 SQL 작성 최적화
- UNION → UNION ALL로 대체
- DISTINCT → EXISTS 서브쿼리 사용
- 불필요한 COUNT 연산 제거 (ROWNUM <= 1) - 인덱스로 소트 연산 대체
- SORT ORDER BY 연산 대체
- SORT GROUP BY 연산 대체 - 소트 영역 사용 최소화
- 소트 완료 후 데이터 가공
- Top-N 쿼리 형태로 SQL 작성 (반드시 ROWNUM / Pseudo Column은 DBMS 인지 XXX)
- 소트 미발생 SQL 작성 최적화
- PGA 메모리 관리
- 자동 vs. 수동 관리
- Oracle 9i부터 자동 PGA 메모리 관리 기능 도입
- workarea_size_policy 파라미터로 관리 방식 설정 (auto: 자동 / manual: 수동) - 수동 PGA 관리 시 주의사항
- Parallel Query와 함께 사용 시 과도한 메모리 할당 가능성
- 예시: 2GB * 8(Parallel Degree) + 2(Parallel 슬레이브 프로세스 수) = 32GB
=> 과도한 메모리 사용, 시스템 리소스 고갈 가능성이 높기 때문에 수동 PGA와 병렬 쿼리를 함께 사용하는 것은 권장하지 않습니다.
- 자동 vs. 수동 관리
반응형
