우리가 작성한 SQL 쿼리는 데이터베이스 내부에서 어떤 과정을 거쳐 결과를 반환할까요? 똑같은 결과를 만드는 쿼리라도, 데이터베이스가 어떤 길을 선택하느냐에 따라 그 실행 속도는 천차만별일 수 있습니다. 이는 마치 서울에서 부산까지 가는 방법이 KTX, 버스, 비행기 등 다양하고 각각의 소요 시간이 다른 것과 같습니다. 데이터베이스의 ‘옵티마이저(Optimizer)’는 우리가 던진 SQL을 실행하기 위한 가장 효율적인 경로, 즉 ‘실행 계획(Execution Plan)’을 수립하는 똑똑한 네비게이션과 같습니다. 하지만 때로는 이 옵티마이저가 최적이 아닌 경로를 선택하여 쿼리 성능을 저하시키기도 합니다.
이때 개발자와 데이터베이스 관리자(DBA)에게 필요한 것이 바로 옵티마이저의 속마음을 들여다보는 도구입니다. 오라클(Oracle) 데이터베이스 환경에서 그 역할을 하는 가장 대표적인 두 가지 도구가 바로 ‘EXPLAIN PLAN’과 ‘Autotrace’입니다. 이 두 도구는 우리가 작성한 쿼리가 어떤 실행 계획을 통해 수행될 것인지, 그리고 실제 실행되었을 때 얼마나 많은 시스템 자원을 사용했는지를 분석하여 성능 병목의 원인을 찾아내고 개선할 수 있는 결정적인 단서를 제공합니다. 이 글에서는 쿼리 튜닝의 첫걸음인 EXPLAIN PLAN과 Autotrace의 사용법과 차이점을 명확히 이해하고, 이를 통해 어떻게 SQL 성능을 측정하고 최적화할 수 있는지 알아보겠습니다.
쿼리 실행의 청사진: EXPLAIN PLAN
EXPLAIN PLAN은 이름 그대로, 특정 SQL 문에 대한 ‘실행 계획’을 ‘설명(Explain)’해달라고 옵티마이저에게 요청하는 명령어입니다. 이 명령어의 가장 큰 특징은 SQL을 ‘실제로 실행하지 않고’, 옵티마이저가 현재의 통계 정보 등을 바탕으로 ‘어떻게 실행할 것인지’에 대한 계획만을 예측하여 보여준다는 것입니다. 이는 건축가가 건물을 짓기 전에 설계도(청사진)를 먼저 보여주는 것과 같습니다. 실제 공사를 시작하기 전에 설계도를 보며 비효율적인 구조나 문제점을 미리 파악하고 수정할 수 있는 것처럼, EXPLAIN PLAN을 통해 우리는 데이터에 아무런 영향을 주지 않고 안전하게 쿼리의 잠재적인 성능 문제를 진단할 수 있습니다.
EXPLAIN PLAN 사용법과 결과 해석
EXPLAIN PLAN은 보통 두 단계로 사용됩니다. 먼저 EXPLAIN PLAN FOR 명령으로 분석하고 싶은 SQL 문의 실행 계획을 생성하여 PLAN_TABLE이라는 특별한 테이블에 저장합니다. 그 다음, DBMS_XPLAN.DISPLAY 함수를 사용하여 PLAN_TABLE에 저장된 실행 계획을 사람이 보기 좋은 형태로 조회합니다.
[EXPLAIN PLAN 사용 예시]
SQL
-- 1단계: 실행 계획 생성
EXPLAIN PLAN FOR
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.sal > 2000;
-- 2단계: 생성된 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
결과로 출력되는 실행 계획 테이블은 쿼리가 어떤 순서로, 어떤 방식으로 처리되는지를 보여주는 상세한 정보의 집합입니다. 여기서 가장 중요하게 봐야 할 항목은 다음과 같습니다.
- Operation: 수행되는 작업의 종류를 나타냅니다.
TABLE ACCESS FULL(테이블 전체를 스캔),INDEX UNIQUE SCAN(인덱스를 통해 한 건의 데이터를 찾음),HASH JOIN(두 테이블을 조인하는 방식 중 하나) 등 다양한 작업이 있습니다. 이 작업의 종류를 통해 쿼리가 효율적으로 인덱스를 사용하고 있는지, 불필요한 전체 스캔을 하고 있지는 않은지 파악할 수 있습니다. - Name: 작업의 대상이 되는 객체(테이블, 인덱스)의 이름입니다.
- Cost: 옵티마이저가 예측한 해당 작업의 상대적인 비용입니다. 이 비용이 높을수록 성능이 오래 걸릴 가능성이 높다고 해석할 수 있으며, 전체 실행 계획에서 어느 부분이 가장 비용이 많이 드는 병목 구간인지 식별하는 데 도움을 줍니다.
- Rows (Cardinality): 해당 작업을 통해 반환될 것으로 예측되는 행(Row)의 개수입니다. 옵티마이저는 이 예측치를 기반으로 다음 조인 방식이나 접근 경로를 결정하므로, 통계 정보가 부정확하여 이 예측치가 실제와 크게 다를 경우 비효율적인 실행 계획이 수립될 수 있습니다.
실행 계획은 들여쓰기 된 트리 구조로 표시되는데, 가장 안쪽에 있는 작업부터 순서대로 실행되어 바깥쪽으로 결과를 전달하는 방식으로 해석하면 됩니다.
실제 주행 기록 분석: Autotrace Mode
Autotrace는 EXPLAIN PLAN과 달리 SQL 문을 ‘실제로 실행’한 후에, 해당 쿼리를 위해 사용된 ‘실행 계획’과 함께 ‘실제 실행 통계(Execution Statistics)’ 정보를 함께 보여주는 강력한 기능입니다. SQL*Plus나 SQL Developer와 같은 클라이언트 도구에서 SET AUTOTRACE ON 명령으로 활성화할 수 있습니다. 이는 마치 자동차의 네비게이션이 안내한 경로(실행 계획)로 실제 주행을 마친 뒤, 총 주행 시간, 평균 연비, RPM 사용량 등의 상세한 주행 기록(실행 통계)을 함께 분석하는 것과 같습니다.
Autotrace의 실행 통계 정보
Autotrace가 제공하는 실행 통계 정보는 쿼리가 실행되는 동안 내부적으로 어떤 일이 일어났는지를 구체적인 수치로 보여주기 때문에, 성능 문제의 원인을 훨씬 더 깊이 있게 분석할 수 있게 해줍니다.
[Autotrace 활성화 및 사용 예시]
SQL
-- Autotrace 기능 활성화
SET AUTOTRACE ON;
-- 분석할 쿼리 실행 (쿼리 결과와 함께 실행 계획, 통계 정보가 출력됨)
SELECT * FROM emp WHERE deptno = 20;
-- Autotrace 기능 비활성화
SET AUTOTRACE OFF;
주요 실행 통계 항목은 다음과 같습니다.
- Consistent Gets / DB Block Gets: 메모리(버퍼 캐시)에서 읽은 데이터 블록의 수입니다. 이 수치가 높을수록 논리적인 I/O가 많았음을 의미하며, 불필요하게 많은 데이터를 읽고 있음을 시사합니다.
- Physical Reads: 디스크에서 직접 읽어온 데이터 블록의 수입니다. 메모리에 원하는 데이터가 없어 디스크까지 접근했음을 의미하며, 이 수치가 높을수록 물리적인 I/O가 많아 성능이 심각하게 저하됩니다. 튜닝의 핵심 목표 중 하나는 이 Physical Reads를 최소화하는 것입니다.
- Redo Size: 데이터 변경(DML) 작업 시 발생한 리두 로그의 양입니다.
- Sorts (memory/disk): 정렬(ORDER BY, GROUP BY 등) 작업을 위해 메모리 또는 디스크를 사용한 횟수입니다. 디스크 정렬이 발생하면 성능이 크게 저하됩니다.
이처럼 Autotrace는 “이 쿼리는 왜 느릴까?”라는 질문에 대해 “예상보다 훨씬 많은 데이터 블록을 읽고 있고(Consistent Gets 증가), 디스크 접근도 빈번하게 발생하고 있습니다(Physical Reads 증가)” 와 같이 구체적인 수치로 대답해 줄 수 있습니다.
EXPLAIN PLAN vs Autotrace: 무엇을 언제 써야 할까?
EXPLAIN PLAN과 Autotrace는 상호 보완적인 관계에 있으며, 목적에 따라 적절하게 사용해야 합니다.
| 구분 | EXPLAIN PLAN | Autotrace |
| 쿼리 실행 여부 | 실행 안 함 (예측) | 실제 실행 (실행 후 분석) |
| 제공 정보 | 예측된 실행 계획 | 실제 사용된 실행 계획 + 실제 실행 통계 |
| 장점 | 데이터 변경 없이 안전하게 분석 가능, DML 쿼리도 부담 없이 분석 | 실제 리소스 사용량을 정확히 파악 가능, 예측과 실제의 차이 분석 |
| 단점 | 실제 실행 계획과 다를 수 있음, 실제 리소스 사용량은 알 수 없음 | 쿼리를 직접 실행하므로 시간이 오래 걸리거나 시스템에 부하를 줄 수 있음 |
| 주 사용 시점 | 개발 단계, 쿼리 작성 중, DML 튜닝 | 개발 완료 후, 통합 테스트, 운영 환경 성능 분석 |
EXPLAIN PLAN은 개발 단계에서 쿼리를 작성하면서 수시로 실행 계획을 확인하고, 인덱스 사용 여부나 조인 순서의 타당성을 검토하는 데 매우 유용합니다. 특히 INSERT, UPDATE, DELETE와 같이 데이터를 변경하는 DML 쿼리는 실제로 실행하기 부담스러우므로, EXPLAIN PLAN을 통해 실행 계획만 미리 확인하는 것이 안전합니다.
반면, Autotrace는 개발이 어느 정도 완료된 후나 실제 운영 환경에서 성능 문제가 발생했을 때, 그 원인을 정밀하게 분석하는 데 사용됩니다. EXPLAIN PLAN이 보여준 예측 계획과 실제 실행 계획이 다른 경우(바인드 변수 문제나 통계 정보 변경 등으로 인해)가 종종 있는데, Autotrace는 실제 사용된 계획을 보여주므로 더 정확한 분석이 가능합니다. 무엇보다 ‘실행 통계’라는 강력한 무기를 통해, 비효율적인 실행 계획이 구체적으로 어떤 자원을 얼마나 낭비하고 있는지를 객관적인 수치로 증명하고 튜닝의 방향을 설정할 수 있게 해줍니다.
결론: 예측과 실측으로 완성하는 쿼리 튜닝
성능 좋은 SQL을 작성하는 것은 단순히 원하는 결과를 정확하게 가져오는 것을 넘어, 그 결과를 ‘가장 효율적인 방법’으로 가져오는 것을 의미합니다. EXPLAIN PLAN과 Autotrace는 이 ‘가장 효율적인 방법’을 찾기 위한 여정에서 개발자에게 길을 안내하는 지도와 계기판의 역할을 합니다.
EXPLAIN PLAN이라는 지도를 통해 목적지까지의 여러 경로(실행 계획)를 미리 살펴보고 최적의 경로를 설계하고, Autotrace라는 계기판을 통해 실제 주행(쿼리 실행) 시의 연비와 속도(리소스 사용량)를 측정하여 설계가 옳았는지를 검증하고 미세 조정을 가하는 것입니다. 이 두 가지 도구를 능숙하게 사용하여 옵티마이저의 생각을 읽고 그와 소통할 수 있을 때, 비로소 우리는 데이터베이스의 성능을 완전히 통제하고 사용자에게 쾌적한 서비스 속도를 제공하는 전문가로 거듭날 수 있을 것입니다.