PL/SQL 성능 최적화 전략

테이블 조인 및 파싱 순서 최적화

효율적인 테이블 순서 선택

오라클의 규칙 기반 옵티마이저(RBO)는 FROM 절을 오른쪽에서 왼쪽으로 처리하므로, 가장 작은 테이블을 마지막에 배치하는 것이 유리하다. 현재는 대부분 비용 기반 옵티마이저(CBO)를 사용하지만, 좋은 습관은 유지하는 것이 바람직하다.

-- 효율적인 접근: 소형 테이블 먼저 처리
SELECT COUNT(*) FROM ab01, ac01;  

-- 비효율적인 접근: 대형 테이블 먼저 스캔
SELECT COUNT(*) FROM ac01, ab01;

WHERE 절 조건 순서 최적화

WHERE 절은 아래에서 위로 평가되므로, 조인 조건을 필터링보다 앞에 두어 조기에 결과 집합을 줄이는 것이 좋다.

-- 권장 방식
SELECT ab01.aab001, ab02.aab051
FROM ab01, ab02
WHERE ab01.aab001 = ab02.aab001  
  AND ab02.aae140 = '31';         

-- 비효율적인 방식
SELECT ab01.aab001, ab02.aab051
FROM ab01, ab02
WHERE ab02.aae140 = '31'          
  AND ab01.aab001 = ab02.aab001;

서브쿼리 및 집합 연산 최적화

SELECT * 사용 피하기

와일드카드(*) 사용은 데이터 사전 조회를 유발하여 불필요한 I/O를 발생시킨다.

-- 명시적 컬럼 지정
SELECT aac001, aac002, aab001 
FROM ac01 
WHERE aab001 = '1001';

-- 비효율적인 전체 컬럼 로드
SELECT * FROM ac01 WHERE aab001 = '1001';

EXISTS로 IN 대체

EXISTS는 첫 번째 매칭 후 즉시 종료되지만, IN은 전체 결과 집합을 생성해야 한다.

-- 추천 방식
SELECT *
FROM ac01
WHERE EXISTS (
    SELECT 1 FROM ac02
    WHERE ac02.aac001 = ac01.aac001
      AND aab001 = str_aab001
      AND aae140 = '31'
);

-- 비효율적인 IN 사용
SELECT *
FROM ac01
WHERE aac001 IN (
    SELECT aac001 FROM ac02
    WHERE aab001 = str_aab001 AND aae140 = '31'
);

NOT EXISTS로 NOT IN 대체

NOT IN은 NULL 값 존재 시 결과가 없어지고, 전체 테이블 스캔을 수행한다.

-- 권장 구문
SELECT *
FROM ac01
WHERE NOT EXISTS (
    SELECT 1 FROM ab01
    WHERE ab01.aab001 = ac01.aab001
      AND aab020 = '100'
);

-- 문제 있는 NOT IN 사용
SELECT * FROM ac01
WHERE aab001 NOT IN (
    SELECT aab001 FROM ab01 WHERE aab020 = '100'
);

집합 연산 및 조건 필터링 최적화

UNION ALL로 UNION 대체

UNION은 중복 제거 및 정렬 작업으로 인해 리소스를 많이 소모한다.

-- 최적화된 UNION ALL
SELECT col FROM tab1
UNION ALL
SELECT col FROM tab2;

-- 비효율적인 UNION
SELECT col FROM tab1
UNION
SELECT col FROM tab2;

DECODE 함수 활용

DECODE를 통해 단일 패스로 다중 조건 통계를 계산할 수 있다.

-- 효율적인 집계
SELECT 
    COUNT(DECODE(aac008, '1', '1', NULL)) AS zz,
    COUNT(DECODE(aac008, '2', '1', NULL)) AS tx
FROM ac01
WHERE aab001 = '100001';

-- 반복 쿼리 방식
SELECT COUNT(1) FROM ac01 WHERE aab001='100001' AND aac008='1';
SELECT COUNT(1) FROM ac01 WHERE aab001='100001' AND aac008='2';

HAVING 대신 WHERE 사용

WHERE은 그룹핑 전에 필터링하여 처리량을 줄인다.

-- 그룹 전 필터링
SELECT aac008, COUNT(*)
FROM ac01
WHERE aac008 IN ('1', '2')
GROUP BY aac008;

-- 그룹 후 필터링
SELECT aac008, COUNT(*)
FROM ac01
GROUP BY aac008
HAVING aac008 IN ('1', '2');

PL/SQL 프로그램 구조 최적화

중복 테이블 조회 방지

유사 쿼리를 통합하여 한 번의 조회로 여러 로직을 처리한다.

-- 최적화된 커서 사용
CURSOR cur_kc24 IS
    SELECT akc260, aka130
    FROM kc24
    WHERE akb020 = str_akb020
      AND aka130 IN ('11', '21');

FOR rec IN cur_kc24 LOOP
    IF rec.aka130 = '11' THEN
        -- 외래진료 처리
    ELSIF rec.aka130 = '21' THEN
        -- 입원 처리
    END IF;
END LOOP;

-- 비효율적인 별도 조회
FOR rec_mz IN (SELECT akc260 FROM kc24 WHERE aka130='11') LOOP ... END LOOP;
FOR rec_zy IN (SELECT akc260 FROM kc24 WHERE aka130='21') LOOP ... END LOOP;

N+1 문제 해결

커서 내부에서 쿼리를 실행하면 N번의 데이터베이스 왕복이 발생한다.

-- 조인으로 해결
CURSOR cur_ac04 IS
    SELECT ac04.aac001, ac04.akc010, ac01.aac008
    FROM ac04, ac01
    WHERE ac04.aac001 = ac01.aac001
      AND ac04.aab001 = prm_aab001;

FOR rec_ac04 IN cur_ac04 LOOP
    IF rec_ac04.aac008 = '1' THEN
        n_jfje := rec_ac04.akc010 * 0.08;
    END IF;
END LOOP;

-- N+1 문제 코드
FOR rec_ac04 IN (SELECT aac001, akc010 FROM ac04) LOOP
    SELECT aac008 INTO str_aac008 FROM ac01 WHERE aac001 = rec_ac04.aac001;
END LOOP;

BULK COLLECT 및 FORALL 활용

배치 처리를 통해 컨텍스트 스위칭을 최소화한다.

-- 최적화된 배치 처리
DECLARE
    TYPE t_ac04 IS TABLE OF ac04%ROWTYPE;
    l_data t_ac04;
BEGIN
    SELECT * BULK COLLECT INTO l_data FROM ac04 WHERE aab001 = '1001';

    FORALL i IN 1..l_data.COUNT
        INSERT INTO ac04_bak VALUES l_data(i);

    COMMIT;
END;

-- 행 단위 삽입
FOR rec IN (SELECT * FROM ac04) LOOP
    INSERT INTO ac04_bak VALUES rec;
END LOOP;

트랜잭션 제어 및 보조 기법

COMMIT 주기 조절

n_count := 0;
FOR rec IN cur_data LOOP
    INSERT INTO target VALUES (...);
    n_count := n_count + 1;

    IF n_count >= 10000 THEN
        COMMIT;
        n_count := 0;
    END IF;
END LOOP;
COMMIT;

ROWID를 이용한 중복 제거

DELETE FROM ac01 a
WHERE a.rowid > (
    SELECT MIN(b.rowid)
    FROM ac01 b
    WHERE a.aac002 = b.aac002
      AND a.aac003 = b.aac003
);

테이블 별칭 사용

-- 명확한 별칭 사용
SELECT a.aac001, b.akc010
FROM ac01 a, ac04 b
WHERE a.aac001 = b.aac001;

-- 긴 테이블명 반복
SELECT ac01.aac001, ac04.akc010
FROM ac01, ac04
WHERE ac01.aac001 = ac04.aac001;

인덱스 및 정렬 최적화

인덱스 컬럼 함수 사용 금지

-- 올바른 인덱스 활용
SELECT * FROM ac01 WHERE aac001 = '1001';

-- 인덱스 무효화
SELECT * FROM ac01 WHERE UPPER(aac001) = '1001';

정렬 최적화

-- 인덱스 생성
CREATE INDEX idx_ac01_aac008 ON ac01(aac008);
SELECT * FROM ac01 ORDER BY aac008;

-- 인덱스 없는 정렬
SELECT * FROM ac01 ORDER BY aac008;

락 및 연결 문제 처리

락 대기 모니터링

SELECT * FROM v$session s, v$lock l
WHERE s.sid = l.sid AND l.block = 1;

명명 규칙 준수

-- 패키지: CUX_PAYROLL_PUBLIC_PKG
-- 함수: GET_EMPLOYEE_SALARY
-- 프로시저: UPDATE_EMPLOYEE_DEPT_INFO
-- 파라미터: P_EMPLOYEE_ID

태그: PL/SQL Oracle 성능최적화 데이터베이스 SQL

7월 7일 01:33에 게시됨

인기 태그

©2026 괴물 클럽