오라클 SQL 실행 계획 최적화 - 성능 향상을 위한 실전 가이드

1. 실행 계획(Execution Plan)이란?

오라클에서 SQL을 실행하면 옵티마이저(Optimizer)가 가장 효율적인 실행 방법을 선택합니다. 실행 계획(Execution Plan)은 SQL이 실행될 때 어떤 경로로 데이터를 조회하는지를 보여주는 정보입니다. 실행 계획이 비효율적이라면 SQL 실행 속도가 느려지며, 이는 성능 저하로 이어집니다.

SQL 실행이 느려졌다면 먼저 EXPLAIN PLAN이나 AUTOTRACE를 사용하여 실행 계획을 분석해야 합니다. 실행 계획을 해석하는 방법과 성능을 개선하는 최적화 기법을 상세히 알아보겠습니다.

---

2. 실행 계획 확인 방법

2.1 EXPLAIN PLAN 사용

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

이 명령어를 실행하면 실행 계획이 테이블 형태로 출력됩니다.

2.2 AUTOTRACE 사용

SET AUTOTRACE ON;
SELECT * FROM employees WHERE department_id = 10;

AUTOTRACE는 실행 계획뿐만 아니라 쿼리의 실행 통계를 함께 제공하여 성능 분석에 유용합니다.

---

3. 실행 계획을 해석하는 방법

실행 계획에는 다양한 연산자가 포함됩니다. 주요 연산자는 다음과 같습니다.

• TABLE ACCESS FULL: 테이블의 모든 데이터를 스캔하는 방식으로 가장 느립니다.
• INDEX SCAN: 인덱스를 이용하여 데이터를 검색하는 방식으로 속도가 빠릅니다.
• HASH JOIN: 대량의 데이터를 조인할 때 사용되며, 조인 키에 적절한 인덱스가 없으면 성능이 저하될 수 있습니다.
• NESTED LOOPS JOIN: 작은 데이터셋을 조인할 때 효율적이지만, 인덱스가 없으면 비효율적입니다.
• SORT: ORDER BY, GROUP BY와 같은 정렬 작업이 포함된 경우 발생하며, TEMP 테이블을 사용하면 성능이 저하됩니다.

실행 계획에서 TABLE ACCESS FULL이 자주 발생한다면, 인덱스를 활용하는 방식으로 최적화해야 합니다.

---

4. 오라클 SQL 속도 최적화 방법

4.1 적절한 인덱스 생성

SQL이 테이블 전체를 스캔하는 대신 인덱스를 이용하도록 유도하면 성능이 향상됩니다.

• B-TREE 인덱스: 검색 조건이 = 또는 BETWEEN일 때 유용
• BITMAP 인덱스: 값의 종류가 적은 컬럼에 적합
• FUNCTION-BASED 인덱스: UPPER(name) = 'JOHN' 같은 조건에 유용

예제:

CREATE INDEX idx_employees_dept ON employees(department_id);

4.2 ANALYZE를 사용하여 통계 정보 갱신

옵티마이저가 최신 데이터를 기준으로 실행 계획을 수립하도록 ANALYZE나 DBMS_STATS를 실행해야 합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

4.3 HINT 사용하여 실행 계획 유도

오라클 힌트(Hint)를 사용하면 실행 계획을 강제로 변경할 수 있습니다.

SELECT /*+ INDEX(employees idx_employees_dept) */ * FROM employees WHERE department_id = 10;

4.4 서브쿼리 대신 JOIN 사용

서브쿼리는 실행될 때마다 별도의 SQL이 수행되므로, 가능하면 조인을 활용하는 것이 좋습니다.

-- 서브쿼리
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

-- JOIN으로 변경
SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 100;

4.5 EXISTS를 활용한 최적화

IN 대신 EXISTS를 사용하면 불필요한 검색을 방지할 수 있습니다.

-- IN 사용
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

-- EXISTS 사용
SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 100);

4.6 불필요한 SELECT 제거

컬럼 전체를 조회하는 SELECT *는 불필요한 데이터를 포함할 수 있습니다.

SELECT name, salary FROM employees WHERE department_id = 10;

4.7 UNION 대신 UNION ALL 사용

UNION은 중복을 제거하기 위해 정렬 작업이 필요하지만, UNION ALL은 이를 생략하여 성능이 향상됩니다.

SELECT name FROM employees WHERE department_id = 10
UNION ALL
SELECT name FROM employees WHERE department_id = 20;

4.8 WHERE 조건 최적화

컬럼에 함수(UPPER, TO_CHAR)를 사용하면 인덱스가 무효화될 수 있습니다.

-- 인덱스를 사용할 수 없음
SELECT * FROM employees WHERE UPPER(name) = 'JOHN';

-- 인덱스를 사용할 수 있음
SELECT * FROM employees WHERE name = 'John';

4.9 PARTITIONING 활용

대량의 데이터를 다룰 때 파티셔닝을 적용하면 특정 데이터만 조회할 수 있어 성능이 향상됩니다.

CREATE TABLE employees_part
PARTITION BY RANGE (hire_date)
(
    PARTITION p1 VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

4.10 MATERIALIZED VIEW 활용

자주 조회하는 데이터를 미리 저장해 두면 성능이 향상됩니다.

CREATE MATERIALIZED VIEW mv_employees AS
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

---

5. 실행 계획 최적화 적용 전후 비교

최적화를 적용하기 전과 후의 실행 계획을 비교하여 성능이 개선되었는지 확인합니다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---

6. 결론

SQL 실행 계획이 비효율적이라면 실행 속도가 느려지고 시스템 리소스를 낭비하게 됩니다. 실행 계획을 분석하고 인덱스 최적화, 조인 방식 변경, HINT 활용, WHERE 조건 최적화, 파티셔닝 적용 등의 방법을 사용하면 성능을 개선할 수 있습니다.

SQL 최적화는 단순히 쿼리를 수정하는 것이 아니라, 데이터 구조와 옵티마이저의 동작 방식까지 고려해야 하는 중요한 작업입니다. 지속적인 모니터링과 튜닝을 통해 최적의 성능을 유지하는 것이 중요합니다.