SQL 실행계획

사내 교육으로 듣게된 SQL 튜닝 기법에 대한 강의 내용을 정리해본다.

 

1. 실행계획의 정의

실행계획 : 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때, 옵티마이저가 수립하는 작업절차, SQL의 Performance를 결정짓는 요인. 실행계획이 목적했던 대로 실행되지 않으면, SQL Performance에 문제가 발생함.

OPTIMIZER : SQL 해석 > 실행계획(데이터 처리 절차) 수립 > 실행


2. 실행계획의 확인 방법

오라클에서는 아래 명령어를 사용해 확인 가능함.
(특별한 권한부여가 필요 없다.)

*EXPLAIN PLAN
- SQL에 대한 실행계획만을 확인할 수 있음
- 명령을 사용할 때 데이터를 처리하지 않음. (DB에 부하를 미치지 않음.)
- 데이터를 읽지 않기에 소요시간과 I/O, Sorting 관련 정보는 확인이 어렵다.

*SET AUTOTRACE
- EXPLAIN PLAN 명령과 달리 한 번의 명령으로, 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있음.
- 다양하게 옵션을 사용할 수 있어서 여러 가지의 정보를 선택적으로 확인할 수 있음. (SET AUTOTRACE 뒤에 ON EXPLAIN, ON STATISTICS, TRACEONLY, TRACEONLY EXPLAIN, TRACEONLY STATISTICS, OFF 등의 옵션들이 붙는다.)

큰 데이터에 대한 실행계획을 알아보기 위해서는 실제 데이터를 읽지 않는게 좋은 방법이지만, 데이터의 크기가 작다면 데이터를 읽어가면서 실행계획을 확인하는게 좋은 방법이 될 수 있다.

 

3. 실행계획 분석

 실행계획은 데이터 처리를 위한 작업 방법으로, 실행계획을 구성하는 내용의 분석을 통해 SQL의 비효율적인 부분을 확인할 수 있음. 실행계획 분석을 통해 SQL이 작성자의 의도대로 실행되었는지 확인할 수 있으며, 그렇지 않았을 경우 성능상 문제가 되는 부분을 찾아낼 수 있음. 실행계획의 정확한 분석을 통해 SQL문장의 튜닝 포인트를 도출할 수 있음.

명령어 예시) 
SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT/*+USE_NL(e d) */
e.name, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

실행결과 예시)

ID | Opreation                                                      | Name

0   |  SELECT STATMENT                                   |
1   |  NESTED LOOPS                                         |
2   |      NESTED LOOPS                                     |
3   |          TABLE ACCESS FULL                        | EMP
4   |          INDEX UNIQUE SCAN                       | PK_DEPT
5   |      TABLE ACCESS BY INDEX ROWID      | DEPT

들여쓰기 depth가 같다면 위에 있는 것이 먼저 실행되며,  실행계획을 도식화하면 아래 트리구와 같다.

위 트리구조의 실행순서 : 3 -> 4 -> 2 -> 5 -> 1 -> 0

가장 먼저실행되는 것은 가장 왼쪽하단에 위치한 노드이며, 왼쪽 하단 노드 -> 오른쪽 하단 노드 -> 중간 상단 노드의 순서로 실행되는 트리구조이다.