[SQL 튜닝] 1. 실행계획(Execution plan)

1. 실행계획

실행계획이란?

사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업절차를 뜻한다.

이런 실행계획을 수립하는 옵티마이저의 실행 단계는 3단계로 분리할 수 있다.

1) SQL 해석

2) 실행계획 수립

3) 실행

1. 오라클 DBMS를 통해 실행계획 확인하는 방법

오라클 DBMS에서 제공하는 두가지 명령어  

1) explain plan

2) set autotrace

Explain plan 방법

1 2 3 4 5 6 7 8

-- 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;

cs

SQL에 대한 생행계획만을 확인할 뿐 데이터를 처리를 하지 않음

때문에 데이터베이스에 어떠한 부하도 주지 않는다.

튜닝하고자 하는 SQL이 다수일 경우 매번 명령을 수행시켜야하는 불편함이 존재한다.

데이터를 처리하지 않기 때문에 I/O관련 시간도 측정할 수 없다.(SORTING 포함)

PLAN_TABLE에 저장된 결과를 확인하기 위해 다음과 같은 SELECT명령어를 실행시켜야한다.

1 2 3

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY             ('PLAN_TABLE', 'TEST1', 'ALL'));

cs

Set autotrace 방법

EXPLAIN 명력과 달리 한번의 명령으로 여러개의 SQL에 대한 실행계획을 볼 수 있다.

또한, 다양하게 옵션을 사용할 수 있어서 여러가지의 정보를 선택적으로 확인할 수 있다.

1 2 3 4 5

SET AUTOTRACE ON;  SELECT /*+USE_NL(e d)*/           e.name, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

cs

여기서 set autotrace on 이부분에 옵션을 추가하는데 몇가지 예를 알아보도록 하자

-- SET AUTOTRACE 의 몇가지 옵션 예

SET AUTOTRACE ON EXPLAIN;

출력결과와 실행계획까지만 나타냄 통계정보는 생략

SET AUTOTRACE ON STATISTICS;

출력결과와 실행계획을 생략하고 I/O관련 정보를 선택적으로 보여줌

SET AUTOTRACE ON TRACEONLY;

데이터가 상당히 클때 사용하는데 출력결과를 나타내지 않음

SET AUTOTRACE ON TRACEONLY EXPLAIN;

데이터를 처리하지 않고 실행계획만을 보여줌(많이 사용함)

SET AUTOTRACE ON TRACEONLY STATISTICS;

데이터를 처리하지 않고 I/O관련 정보를 보여줌

SET AUTOTRACE ON OFF;

사용하지 않을때 사용

지금까지 옵티마이저의 실행계획을 확인하는 방법을 알아보았다. 이런 실행계획은 분석하여 SQL의 성능을 향상시키는데 목적을 둔다. 때문에 실행계획을 확인하는 것보다 분석하여 어떻게 개선할 것이지를 정하는 것이 더욱 중요하다.

2. 실행계획 분석

1 2 3 4 5

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;

cs

이 쿼리의 실행계획을 보면 다음과 같다.

들여쓰기를 기준으로 구분할 수 있는데 1번 작업과 연관되는 작업이 2번과 5번임을 알 수 있다. 같은 들여쓰기라면 위에 있는 명령이 선실행되므로 2번이 실행되고 5번이 실행된다.

즉, 같은 들여쓰기 DEPTH를 가지고 있다면 위에 있는 작업이 먼저 실행되는 작업이고 아래에 있는 것이 나중에 실행되는 것이다.

또 2번과 연관된 작업이 3번과 4번이기 때문에 이 실행계뢱을 보아 트리구조로 나타내면 다음과 같다.

이때 가장먼저 실행되는 작업은 가장 왼쪽에서 아래에 있는 노드이다.

3 -> 4 -> 2 -> 5 -> 1 -> 0 순으로 실행된다.

실행계획을 어떻게 확인하고 분석하는지 알아보았다. 다음장에서는 옵티마이저에 대해 알아보도록 한다.