[본문스크랩] 실행 계획 확인 방법 - SQL Trace & TKPROF 이용 방법

!!! | themisoo
http://blog.naver.com/themisoo/110004154686

하나 하나의 문장에 대해서 실행계획을 생성하지 않고, 시스템 전체 혹은 하나의 세션에서 발생하는 모든 SQL 문에 대한 실행계획을 알고자 할 경우에는 Trace File을 만들어서 분석합니다.

 

생성된 Trace File은 생성된 그 자체로든 우리가 분석할 수 없어서 분석할 수 있도록 tkprof라는 tool을 사용해서 분석할 수 있습니다. 또한 SQL*PLUS에서 실행계획의 간략한 정보만 볼수도 있습니다.

 

1. TKPROF Trace 수행 선행 조건

    - MAX_DUMP_FILE_SIZE

    - USER_DUMP_DEST

    - TIMED_STATISTICS

    - Plan Table 생성

    - SQL TRACE Parameter를 True로 설정

        

    ☞ SQL_TRACE Parameter 설정

 

    자신의 Session 만을 변경

 

    SQL> ALTER SESSION SET SQL_TRACE = TRUE;

    SQL> ALTER SESSION SET SQL_TRACE = FALSE;

 

    다른 Session도 변경 가능

 

    SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE); 

    SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, FALSE);

 

2. SQL 실행

    - 위의 TKPROF Trace 수행 선행 조건을 만족시켰다면 실행 계획을 확인하고자 하는 SQL을 수행한다. Trace를 수집하기 위해 실행된 SQL은 USER_DUMP_DESC Parameter에 정의된 Directory에 Trace를 남기게 된다.

 

   SQL> ALTER SESSION SET SQL_TRACE = TRUE;

   세션이 변경되었습니다.

 

   SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;

   세션이 변경되었습니다.

 

   SQL> SELECT *
     2    FROM DEPARTMENTS;

 

   SQL> ALTER SESSION SET SQL_TRACE = FALSE;

   세션이 변경되었습니다.

 

3. TKPROF 사용 구문

- TKPROF TRACEFILE OUTPUTFILE [EXPLAIN=] [TABLE=] [PRINT=] [INSERT=] [SYS=] [SORT=]

  TRACEFILE : 생성된 TRACE 파일명

  OUTPUTFILE : TKPROF를 통해서 출력될 파일명(확장자가 PRF로 됨)

  EXPLAIN : EXPLAIN=USERID/PASSWD로 CONNECT시 사용된 USER 및 PASSWORD

  SYS : SYS=NO로 SYSTEM에 관련된 자료는 포함시키지 않음.

 

  C:\TKPROF C:\oracle\admin\oracle\udump\oracle_ora_2444.trc C:\A.TXT SYS=NO

 

 

☞ TKPROF를 실행하면

 

   - 실행한 SQL문

   - 소요된 시간 등 통계자료

   - 실행계획(EXECUTION PLAN) 등 세가지 요소들로 구성되어진다. 

 

☞ TKPROF 통계정보 내용

 

* Parse
  - SQL문이 파싱되는 단계에 대한 통계
  - 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함됨.
  - 단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PL*SQL에서 보존 커서(Hold cursor)를 지정한 경우에는 한 번만 파싱됨.

 

* Execute
  - SQL문의 실행단계에 대한 통계임.
  - UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타나게 됨.
  - 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 Fetch에는 아주 적은 값이 나타남.

 

* Fetch
  - SQL문이 실행되면서 Fetch된 통계임.
  - 부분범위 방식으로 처리된 SELECT 문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우
    (AGGREGATE, 전체집계, COUNT 등)는 주로 여리게 많은 값들이 나타나고 EXECUTE에는 아주 적은 값이 나타남.

 

* COUNT
  - SQL문이 파싱된 횟수, 실행된 횟수, FETCH가 수행된 횟수

 

* CPU
  - PARSE, EXECUTE, FETCH가 실제로 사용한 CPU 시간(1/100초 단위)

 

* ELAPSED
  - 작업의 시작에서 종료시까지 실제 소요된 총 시간

 

* DISK
  - 디스크에서 읽혀진 데이타 블록의 수

 

* QUERY
  - 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수
  - SELECT 문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT시에는 소량만 발생됨.

 

* CURRENT
  - 현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을 액세스한 블록 수
  - 주로 UPDATE, INSERT, DELETE 작업시 많이 발생
  - SELECT문에서는 거의 없으나 아주 적은 양인 경우가 대부분임.

 

* Logical I/O : QUERY + CURRENT

 

* ROWS
  - SQL문을 수행한 결과에 의해 최종적으로 액세스된 ROW의 수
  - 서브쿼리에 의해서 추출된 ROW는 제외됨.
  - 만약 SUM, AVG, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없음.