SQL 튜닝비법 - 실행계획

  1. 실행계획이란? 사용자가 SQL문으로 필요한 데이터를 추출하려고 할 때 어떤 과정으로 작업이 이루어지를 나타내는 작업 절차를 의미한다.
  2. 실행계획을 확인하는 방법
    1. MSSQL의 경우는 쿼리 창에서 오른쪽 마우스를 클릭하게 되면 "예상 실행 계획 표시"를 통해서 볼 수 있다.
    2. Oracle에서는 명령을 통해서 실행계획을 확인해볼 수 있다.
      1. EXPLAIN PLAN : SQL에 대한 실행계획만 확인할 수 있으며, 데이터를 처리하지 않는다.
        EXPLAIN PLAN 사용법
        EXPLAIN PLAN
        SET STATEMENT_ID = 'PLAN_TEST_1' INTO PLAN_TABLE
        FOR
        SELECT
         E.ENAME,
         E.DEPNO,
         D.DNAME
        FROM EMP E,
                   DEPT D
        WHERE E.DEPNO = D.DEPNO
        ;

        -- 실행계획 출력
        SELECT *
        FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','PLAN_TEST_1','ALL'))
        ;
      2. AUTOTRACE : SQL에 대한 실행계획뿐만 아니라 옵션에 따라서 여러가지 정보를 선택적으로 확인이 가능하며 실행된 데이터도 나타낼 수 있다.
        AUTOTRACE 사용법
        SET AUTOTRACE ON;

        SELECT 
         E.ENAME,
         E.DEPNO,
         D.DNAME
        FROM EMP E,
                   DEPT D
        WHERE E.DEPNO = D.DEPNO
        ;

        -- 실행된 데이터 결과와 실행계획이 모두 출력
      3. 명령 옵션
        1. SET AUTOTRACE ON : SQL 실행 결과를 보여주고, 실행계획과 통계정보를 모두 출력
        2. SET AUTOTRACE ON EXPLAIN : SQL 실행 결과를 보여주고, 실행계획만 출력
        3. SET AUTOTRACE ON STATISTICS : SQL 실행 결과를 보여주고, 통계정보만 출력
        4. SET AUTOTRACE TRACEONLY : SQL 실행 결과는 보여주지 않고 실행계획과 통계 정보 모두 출력
        5. SET AUTOTRACE TRACEONLY EXPLAIN : SQL 실행 결과는 보여주지 않고, 실행계획만 출력
        6. SET AUTOTRACE TRACEONLY STATISTICS : SQL 실행 결과는 보여주지 않고, 통계 정보만 출력
        7. SET AUTOTRACE OFF : 실행계획, 통계정보를 출력해주지 않는다.
  3. 실행계획 분석 방법
    1. 실행계획 읽는 순서
      1. 위에서 아래로 읽어 내려가면서 제일 먼저 읽을 스텝을 찾는다.
      2. 내려가는 과정에서 같은 들여쓰기가 존재하면 무조건 위 -> 아래 순으로 읽는다.
      3. 읽고자하는 스텝보다 들여쓰기가 된 하위 스텝이 존재하면, 가장 안쪽으로 들여쓰기 된 스텝을 시작으로 하여 한 단계씩 상위 스텝으로 읽어 나온다.
    2. 순서에 따른 읽는 순서 확인
      실행계획 읽는 순서 예시
      예시
      Id
      Operation
      Name
      Rows
      Bytes
      Cost (%CPU)
      Time
      0 SELECT STATEMENT


      		3(100)
      1  NESTED LOOPS
      		1 117 3(0) 00:00:01
      2   TABLE ACCESS BY INDEX ROWID EMP 1 87 2(0) 00:00:01
      3    INDEX UNIQUE SCAN PK_EMP 1
      		1(0) 00:00:01
      4   TABLE ACCESS BY INDEX ROWID DEPT 409 12270 1(0) 00:00:01
      5    INDEX UNIQUE SCAN PK_DEPT 1
      		0(0)
      실행 순서
      3 -> 2 -> 5 -> 4 -> 1 -> 0

참고도서 : 실전사례로 살펴보는 SQL 튜닝비법