1. 실행계획의 정의
* 실행계획이란?
사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업절차
실행계획이 목적했던 대로 수립되지 않으면, SQL 퍼포먼스에 문제가 발생
2. 실행계획 확인 방법
* 오라클 DBMS를 통해 실행계획 확인하는 방법
EXPLAIN PLAN / SET AUTOTRACE
- EXPLAIN PLAN
EXPLAIN PLAN
SET STATEMENT_ID = 'TEST1' INTO PLAN_TABLE
FOR
SELECT /*+USE_NL(e d)*/
e.name, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;- SQL에 대한 실행계획만을 확인할 수 있음
- 명령을 사용할 때 실제 데이터를 처리하지 않기 때문에 데이터베이스에 어떤 부하도 주지 않음
- 튜닝하고자 하는 SQL이 다수일 경우 매번 명령을 수행시켜야하는 불편함이 존재
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY
('PLAN_TABLE', 'TEST1', 'ALL'));- PLAN_TABLE에 저장된 결과를 확인하기 위해 다음과 같은 별도의 SELECT명령어를 실행시켜야함
- 데이터를 처리하지 않기 때문에 I/O관련 시간도 측정할 수 없음
- SET AUTOTRACE
SET AUTOTRACE ON;
SELECT /*+USE_NL(e d)*/
e.name, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;- EXPLAIN 명령과 달리 한 번의 명령으로 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있음
- 다양하게 옵션을 사용할 수 있어서 여러 가지의 정보를 선택적으로 확인할 수 있음
* SET AUTOTRACE ON에 사용하는 명령의 옵션
SET AUTOTRACE ON EXPLAIN;
출력결과와 실행계획까지만 나타내고 통계정보는 생략
SET AUTOTRACE ON STATISTICS;
출력결과와 실행계획을 생략하고 I/O관련 정보를 선택적으로 보여줌
SET AUTOTRACE ON TRACEONLY;
출력의 건수가 많을때 사용하는데 출력결과를 나타내지 않음
SET AUTOTRACE ON TRACEONLY EXPLAIN;
데이터를 처리하지 않고 SQL이 소유하는 실행계획만을 보여줌 (큰데이터를 처리할 때 부하없이 사용가능하다)
SET AUTOTRACE ON TRACEONLY STATISTICS;
출력된 결과를 화면에 나타내지않고 I/O관련 정보를 보여줌
SET AUTOTRACE ON OFF;
사용하지 않을 때 사용
~ 이러한 실행계획을 확인하고 해석하여 SQL의 성능을 향상시키는데 목적을 둠!
3. 실행계획 분석
SET AUTOTRACE ON TRACEONLY EXPLAIN;
SELECT /*+USE_NL(e d)*/
e.name, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;- SET AUTOTRACE ON TRACEONLY; 을 사용하였기 때문에 출력결과를 나타내지는 않고 반면 실행계획을 나타냄
아래는 위 쿼리의 실행계획이다.
- 맨 왼쪽에 ID가 부여 되었음 전반적인 실행계획은 들여쓰기를 기준으로 구분할 수 있음
- 1번 작업과 연관된 작업이 2번과 5번임을 알 수 있고 위에 있는 명령이 먼저 실행되므로 2번이 실행되고 5번이 실행됨
- 같은 예로 2번 작업과 연관된 작업이 3번과 4번임을 알 수 있다.
아래는 위의 실행계획을 트리구조로 나타낸 것이다.
- 들여쓰기에서 같은 DEPTH인 2번을 왼쪽에 5번을 왼쪽에 작성하고 그 다음으로 3번과 4번을 작성한다
- 이때 제일 먼저 실행되는 작업은 가장 왼쪽에서 아래에 있는 노드이다. 그림에서는 3 -> 4 -> 2 -> 5 -> 1 -> 0 순으로 실행된다.
- 실행계획은 데이터 처리를 위한 작업방법
- 실행계획 구성 내용의 분석을 통해 SQL의 비효율적인 부분을 확인 할 수 있음
- 실행계획의 정확한 분석을 통해 SQL문장의 튜닝 포인트를 도출할 수 있음