[ORACLE] EXPLAIN PLAN()으로 SQL 실행 계획 정확히 읽는 법

[Oracle] EXPLAIN PLAN

 

데이터베이스의 성능은 곧 서비스의 품질과 직결됩니다. 특히 Oracle Database를 운용하는 환경에서 쿼리의 성능을 정확하게 진단하고 최적화하는 과정은 필수입니다. 이 글에서는 EXPLAIN PLAN을 활용해 SQL 실행 계획을 분석하고, 성능을 향상시키는 구체적인 방법을 전문가의 시각에서 상세히 설명합니다.

1. EXPLAIN PLAN이란?

EXPLAIN PLAN은 Oracle SQL 쿼리를 실행하기 전, 옵티마이저가 어떤 방식으로 쿼리를 실행할지 예측한 실행 계획을 보여주는 기능입니다. 실제 실행 결과가 아니라, 실행될 계획을 확인함으로써 쿼리의 병목현상이나 불필요한 Full Table Scan 등을 사전에 감지할 수 있습니다.

2. EXPLAIN PLAN의 기본 사용법

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

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

위 예제는 간단한 SELECT 문에 대해 실행 계획을 분석하는 방법입니다. DBMS_XPLAN.DISPLAY를 사용하면 PLAN_TABLE에 저장된 결과를 사람 읽기 쉬운 형식으로 출력해 줍니다.

3. 실행 계획의 주요 구성 요소

• Operation: 실행될 작업 유형 (예: TABLE ACCESS FULL, INDEX RANGE SCAN)
• Options: 작업에 대한 추가 옵션 정보
• Object Name: 사용된 테이블 또는 인덱스 이름
• Cost: 비용 추정치로, 상대적인 실행 비용을 나타냅니다
• Cardinality: 예상 출력 행 수

4. EXPLAIN PLAN vs AUTOTRACE vs AWR

SQL 성능을 분석하는 데 사용되는 도구들을 비교하면 다음과 같습니다:

기능	EXPLAIN PLAN	AUTOTRACE	AWR Report
실행 전 분석	가능	불가능 (실행 후)	불가능 (실행 후)
실행 결과 포함 여부	미포함	포함	포함
리소스 사용량 분석	제한적	가능	매우 정밀
실무 활용도	높음	중간	높음 (문제 추적용)

5. EXPLAIN PLAN 활용 팁

• 인덱스 사용 여부 확인: 예상과 달리 TABLE ACCESS FULL이 발생하면 인덱스가 비효율적이거나 누락되었을 가능성이 높습니다.
• 조인 순서 분석: 조인 순서가 비효율적일 경우, 옵티마이저 힌트 사용을 고려할 수 있습니다.
• FILTER 및 VIEW 작업 확인: 불필요한 중간 뷰나 필터 조건이 있는지 점검하여 불필요한 연산 제거 가능

6. DBMS_XPLAN 패키지의 활용

DBMS_XPLAN.DISPLAY_CURSOR를 사용하면 실제 실행된 SQL의 커서 정보를 바탕으로 더욱 정확한 실행 계획을 확인할 수 있습니다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

이 방식은 EXPLAIN PLAN이 보여주지 못하는 실행 중 통계 정보까지 함께 제공하므로, 실시간 튜닝에 효과적입니다.

7. 실행 계획 분석 시 주의사항

• PLAN_TABLE 업데이트 여부: 여러 사용자가 PLAN_TABLE을 공유하면 충돌이나 오염된 데이터가 출력될 수 있으므로 사용자 별로 PLAN_TABLE을 따로 두는 것이 안전합니다.
• 실제 실행과 차이점 인지: EXPLAIN PLAN은 통계 기반의 예측이며, 실행 계획은 SQL의 바인드 변수 값, 통계정보의 신선도, 병렬 실행 여부 등에 따라 달라질 수 있습니다.

8. 옵티마이저 힌트와 실행 계획의 제어

필요에 따라 옵티마이저가 선택하는 실행 계획을 힌트(Hint)로 제어할 수 있습니다. 예:

SELECT /*+ INDEX(emp emp_idx_dept) */ * FROM emp WHERE deptno = 10;

하지만 힌트는 옵티마이저의 판단을 억제하는 도구이므로 신중하게 사용해야 하며, 데이터 양이나 통계가 변할 경우 성능 저하를 일으킬 수 있습니다.

9. 성능 최적화를 위한 실전 전략

• 정기적인 통계 수집(DBMS_STATS)으로 옵티마이저 정확도 향상
• 불필요한 컬럼 조회 제거로 I/O 감소
• WHERE절 조건 순서, 자료형 일치 등 기본적인 쿼리 최적화 습관
• 인덱스 리빌드와 파티셔닝 고려

10. 결론

Oracle의 EXPLAIN PLAN은 단순한 분석 도구가 아닌, 개발자와 DBA가 시스템 성능을 최적화하는 데 있어 필수적인 진단 도구입니다. 이를 통해 시스템 병목을 사전에 감지하고, 정확한 쿼리 튜닝 전략을 수립할 수 있습니다. EXPLAIN PLAN을 능숙하게 활용하는 능력은 데이터베이스 시스템의 안정성과 성능을 결정짓는 핵심 역량입니다.

출처

• Oracle® Database SQL Tuning Guide, Oracle Corporation
• Tom Kyte, "Expert Oracle Database Architecture", Apress
• Oracle Live SQL Documentation: https://livesql.oracle.com