[SQL튜닝] 실행계획

[SQL튜닝] 실행계획

---

주요내용

• 실행계획의 정의
• 실행계획 확인 방법
• 실행계획 분석

학습목표

• SQL 문장의 실행계획을 통해 데이터베이스 성능을 모니터링할 수 있다.
• 실행계획을 통해 비효율적으로 수행되는 SQL문장의 개선항목을 도출할 수 있다.

---

실행계획의 정의

• 실행계획 : 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때, 옵티마이저가 수립하는 작업절차

  SELECT A.ENAME,
       A.DEPTNO,
       B.DNAME
  FROM EMP A, DEPT B
  WHERE A.DEPTNO = B.DEPTNO;

• OPTIMIZER : SQL해석 > 실행계획 수립 > 실행

---

실행계획 확인 방법

1) EXPLAIN PLAN

• SQL에 대한 실행계획만을 확인할 수 있음
• 명령을 사용할 때 데이터를 처리하지 않음

  EXPLAIN PLAN
  SET STATEMENT_ID = 'TEST1' INTO PLAN_TABLE
  FOR
  SELECT e.ename, e.deptno, d.dname
  FROM emp e, dept d
  WHERE e.deptno = d.deptno;

• 아래 쿼리를 또 작성해 주어야 하는 불편함이 있음

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

2) SET AUTOTRACE

• EXPLAIN PLAN 명령과는 달리 한 번의 명령으로, 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있음
• 다양하게 옵션을 사용할 수 있어서 여러 가지의 정보를 선택적으로 확인할 수 있음

  SET AUTOTRACE ON;
  SELECT e.ename, e.deptno, d.dname
  FROM emp e, dept d
  WHERE e.deptno = d.deptno;

• 명령의 옵션

  SET AUTOTRACE ON EXPLAIN;
  SET AUTOTRACE ON STATISTICS;
  SET AUTOTRACE TRACEONLY;
  SET AUTOTRACE TRACEONLY EXPLAIN;
  SET AUTOTRACE TRACEONLY STATISTICS;
  SET AUTOTRACE OFF;

---

실행계획 분석

• 실행계획은 데이터 처리를 위한 작업방법으로, 실행계획을 구성하는 내용의 분석을 통해 SQL의 비효율적인 부분을 확인할 수 있음
• 실행계획의 정확한 분석을 통해 SQL문장의 튜닝 포인트를 도출할 수 있음

  SET AUTOTRACE TRACEONLY EXPLAIN;
  SELECT e.ename, e.deptno, d.dname
  FROM emp e, dept d
  WHERE e.deptno = d.deptno;

  

• 위 이미지에서 1번과 연관된 작업 중 2번과 5번이 같은 depth이며, 들여쓰기가 같으면 같은 depth라고 할 수 있다.
• 같은 depth라도 위에서 아래로 순차적으로 진행되기 때문에 2번이 먼저 실행된다.
  

  

• 위 이미지는 실행계획을 도식화한 것으로, 트리 형태로 구성되어 있다.