[ oracle ] SQL 실행 계획 확인 방법

오라클에서 SQL 의 실행계획을 확인하는 방법 알아보자

 

---

1. EXPLAIN PLAN  

 - 실행 전 예상 실행계획(통계 없이)을 PLAN_TABLE에 저장하여 조회.  

EXPLAIN PLAN FOR <SQL문>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

 

---

2. V$SQL_PLAN 조회  

   - 이미 실행된 SQL의 실제 실행계획(바인딩 변수 값 포함)을 실시간 확인 가능.  
     
SELECT * FROM V$SQL_PLAN WHERE SQL_ID = '<SQL_ID>';
 
 

---

3. DBMS_XPLAN.DISPLAY_CURSOR  

   - V$SQL_PLAN과 비슷하게 실제 실행된 계획을 보기 좋게 출력.  
   - 실행 통계, 실제 처리 행 수 등 상세정보 포함 가능.  
   
# sql 에 주석 삽입, 실행 
select /*+ gather_plan_statistics cost_based */ ;

# last sql plan 확인 
select * from table(dbms_xplan.display_cursor(null /*sql_id*/, null /*child_cursor*/, 'all allstats last'));

 

---

4. SET AUTOTRACE  

   - SQL*Plus 환경에서 쿼리 실행과 동시에 실행계획과 통계까지 한 번에 제공.  
  
    SET AUTOTRACE ON
    SELECT * FROM employees;

아래처럼 옵션에 따라 결과가 달라진다.

1) set autotrace on  
  -> 실제수행 + 결과 + 실행계획 + 실행통계

2) set autotrace on explain 
  -> 실제수행 + 결과 + 실행계획                 
 
3) set autotrace on statistics 
  -> 실제수행 + 결과                 + 실행통계
  
4) set autotrace trace explain
  ->                          실행계획                  
  
5) set autotrace traceonly  
  -> 실제 수행          + 실행계획 + 실행통계

 

 

---

5.  SQL Trace + TKPROF  

 - 심층 성능 분석용으로, 긴 시간 동안의 실행 상태와 계획 변화를 추적 가능.  

alter session set tracefile_identifier='myid';   -- 구분자 지정
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
--sql 수행
alter session set events '10046 trace name context off'; 

-- 파일을 tkprof 로 변환 

tkprof tracefile.trc outputfile.txt sys=no explain=scott/tiger

* tracefile.trc : 트레이스 파일 이름
* outputfile.txt : 출력할 보고서 이름
* sys=no : sys가 호출하는 내부 SQL(재귀 SQL)은 출력하지 않게