[Oracle] SQL 실행 계획(Explain Plan) 해석하기: 성능 최적화를 위한 첫걸음

[Oracle] 내 SQL의 성능 진단서: 실행 계획(Execution Plan) 분석법

SQL을 작성하고 결과가 나오기까지, 데이터베이스 내부에서는 수많은 연산이 일어납니다. 하지만 데이터 양이 수백만 건을 넘어가는 순간, 우리가 짠 쿼리는 예상치 못한 속도 저하를 일으키곤 하죠. 이때 필요한 것이 바로 실행 계획입니다. 내 SQL이 최적의 길을 가고 있는지 진단하는 법을 상세히 알아보겠습니다.

---

🚀 오라클 실행 계획 확인 '치트키' (5초 완성)

이론보다 실습이 먼저입니다. 당장 내 쿼리의 설계도를 확인하고 싶다면, 아래의 EXPLAIN PLAN FOR와 DBMS_XPLAN.DISPLAY 조합을 사용하세요. 매번 구글링하지 않도록 이 코드 블록을 메모해 두시는 것을 추천합니다.

SQL

 

-- 1단계: 분석하려는 쿼리 맨 앞에 'EXPLAIN PLAN FOR'를 붙여 실행합니다.
-- (쿼리가 실제 수행되지는 않으며, 내부적으로 설계도만 생성됩니다.)
EXPLAIN PLAN FOR
SELECT * FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > 5000;

-- 2단계: 생성된 실행 계획을 읽기 쉬운 표 형태로 출력합니다.
-- (오라클 표준 패키지인 DBMS_XPLAN을 활용하는 가장 정확한 방법입니다.)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

💡 핵심 포인트

• EXPLAIN PLAN FOR: 실제 데이터를 조회하지 않고 '계획'만 세우므로 운영 DB에서도 안전합니다.
• DBMS_XPLAN.DISPLAY: 복잡한 로우 데이터를 트리 형태의 깔끔한 표로 변환해 줍니다.
• 우선순위: 결과창에서 Operation(어떤 방식인지), Cost(부하량), Rows(결과 행 수) 세 가지만 먼저 확인해도 튜닝의 절반은 성공입니다.

---

1. 실행 계획이란?

실행 계획은 SQL이 실행되기 위해 오라클 옵티마이저(Optimizer)가 세운 상세 설계도입니다. 어떤 테이블부터 읽을지, 인덱스를 탈지 말지, 어떤 방식으로 조인을 할지를 결정한 결과물입니다.

2. 실행 계획 확인하기: 실전 명령어

가장 많이 쓰이는 방법은 EXPLAIN PLAN과 DBMS_XPLAN 패키지를 활용하는 것입니다.

SQL

-- 1단계: 실행 계획 생성
EXPLAIN PLAN FOR
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.empno = 7934;

-- 2단계: 결과 출력
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3. 실행 계획의 핵심 컬럼 읽는 법

실행 계획 결과 창에는 여러 컬럼이 나타나는데, 다음 5가지는 반드시 체크해야 합니다.

1. Id: 각 단계의 식별 번호입니다.
2. Operation: 수행된 작업의 종류입니다. (예: INDEX RANGE SCAN, HASH JOIN)
3. Name: 작업 대상인 테이블이나 인덱스 명입니다.
4. Rows (E-Rows): 해당 단계에서 나올 것으로 예상되는 레코드 수입니다. 통계 정보가 최신일수록 정확합니다.
5. Cost (%CPU): 이 작업을 수행하는 데 드는 상대적인 비용입니다. 낮을수록 효율적입니다.

---

4. 가장 중요한 '읽는 순서'의 원칙

실행 계획은 단순히 위에서 아래로 읽으면 안 됩니다. 두 가지만 기억하세요.

• 원칙 1 (들여쓰기): 가장 안쪽(오른쪽)으로 들여쓰기 된 것이 먼저 실행됩니다.
• 원칙 2 (상하 관계): 들여쓰기 깊이가 같다면 위에 있는 것이 먼저 실행됩니다.

---

5. 좋은 예시 vs 나쁜 예시 (성능 분석)

실행 계획에서 나타나는 대표적인 케이스를 통해 성능을 진단해 보겠습니다.

✅ 좋은 예시: INDEX UNIQUE/RANGE SCAN

• 상황: WHERE empno = 7934 처럼 인덱스가 걸린 컬럼으로 조회할 때.
• 결과: INDEX UNIQUE SCAN 또는 INDEX RANGE SCAN이 나타납니다.
• 분석: 수만 건의 데이터 중 필요한 데이터가 있는 위치만 정확히 찾아가므로 매우 빠릅니다.

❌ 나쁜 예시: TABLE ACCESS FULL

• 상황: 인덱스가 없는 컬럼을 조회하거나, 인덱스가 있어도 조건절에서 컬럼을 가공(WHERE SUBSTR(id, 1, 3) = '123')했을 때.
• 결과: TABLE ACCESS FULL이 나타납니다.
• 분석: 책의 목차를 안 보고 처음부터 끝까지 다 읽는 것과 같습니다. 데이터가 많을수록 성능이 기하급수적으로 떨어집니다.

⚠️ 주의할 예시: CARTESIAN PRODUCT

• 상황: 조인 조건을 빠뜨렸을 때 발생합니다.
• 결과: MERGE JOIN CARTESIAN이 나타납니다.
• 분석: 두 테이블의 모든 데이터를 곱하기 때문에(1,000건 x 1,000건 = 100만 건), 순식간에 시스템 부하를 일으킵니다.

---

6. 실무 팁: 실행 계획을 최적화하는 3단계 전략

1. 통계 정보 최신화: 옵티마이저는 통계 정보를 바탕으로 길을 찾습니다. DBMS_STATS를 통해 통계 정보를 최신으로 유지해야 엉뚱한 길(실행 계획)을 잡지 않습니다.
2. 적절한 인덱스 설계: TABLE ACCESS FULL이 빈번하다면 자주 쓰이는 조건절 컬럼에 인덱스를 생성하세요.
3. 힌트(Hint) 활용: 오라클이 자꾸 나쁜 길을 선택한다면 /*+ INDEX(e emp_pk) */와 같은 힌트를 주어 개발자가 강제로 길을 지정해 줄 수 있습니다.

---

🏁 마무리하며: 실행 계획은 개발자의 '지도'입니다

실행 계획을 읽는 능력은 단순 코딩을 넘어 시스템 전체의 효율을 결정짓는 엔지니어의 핵심 역량입니다.

• 내가 짠 SQL이 왜 느린지 모르겠다면? 실행 계획을 여세요.
• 인덱스를 만들었는데 효과가 있는지 궁금하다면? 실행 계획을 확인하세요.

실행 계획을 분석하는 것은 투자에서 기업의 재무제표를 읽는 것과 같습니다. 겉으로 보이는 주가(쿼리 결과) 뒤에 숨겨진 비용 구조와 자산 활용 효율(실행 경로)을 파악해야 성공적인 튜닝(수익)이 가능합니다. 설계도를 읽는 개발자가 시스템과 자산의 미래를 바꿉니다.

오늘 정리한 들여쓰기 원칙과 주요 Operation의 의미만 잘 파악해도, 여러분은 이미 '성능을 고민하는 개발자'로 한 단계 성장한 것입니다.