Oracle Database TIP
SQL Trace와 TKPROF 유틸리티 22 2 33,387

by 김정식 SQL TRACE TKPROF TIMED_STATISTICS SQL_TRACE USER_DUMP_DEST [2003.07.20]


이 강좌는 2003년도에 작성 되었습니다. 관련 강좌로 Oracle Tuning 강좌를 참고하세요

SQL Trace란?

  SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만든다.

  SQL Trace는 세션과 인스턴스 레벨에서 SQL문장들을 분석 할 수 있다.

  SQL Trace에 의해 생성된 파일의 확장자는 .TRC 이다.

  .TRC파일은 직접 읽기 불편하고, TKPROF 유틸리티를 이용하면 쉽게 분석 할 수 있다.

  인스턴스 레벨로 Trace를 수행시키면 전체적인 수행능력이 20~30% 정도 감소하므로, 될 수 있으면 세션 레벨로 Trace 파일을 생성해야 한다.

SQL Trace에서 제공하는 정보

  - parse, execute, fetch count : 오라클의 SQL 처리 작업에서 parse,execute,fetch 작업들이 처리된 횟수

  - 수행된 CPU 프로세스 시간과 경과(Elapsed)된 질의 시간들 : SQL문을 실행하는데 소비된 CPU시간과 실질적인 경과시간

  - 물리적(Disk)/논리적(Memory) 읽기를 수행한 횟수 : 질이의 parse, execute, fetch 부분들에 대해 디스크에 있는 데이터 파일들로부터 읽은 데이터 블록들의 전체 개수

  - 처리된 로우수 : 결과 set을 생성하기 위해 오라클에 의해 처리된 행의 전체 개수

  - 라이브러리 캐쉬 miss : 분석된 문장이 사용되기 위해 라이브러리 캐쉬 안으로 로드되어야 하는 횟수

SQL Trace와 관련된 파라미터

  - TIMED_STATISTICS : RDBMS가 추가적인 CPU시간, 실행 시간등을 모을수 있게 한다.
      이 시간통계는 SQL악성 여부를 판단하는 중요한 요소가 된다.
      ALTER SESSION SET TIMED_STATISTICS=TRUE 또는 init.ora파일에 설정

  - SQL_TRACE : SQL Trace의 수행여부, ALTER SESSION SET sql_trace=TRUE 또는 init.ora파일에 설정

  - USER_DUMP_DEST : Trace파일이 생성되는 디렉토리를 지정

  - MAX_DUMP_FILE_SIZE : 트레이스파일의 최대 크기(단위: OS블럭수)

SQL Trace 실행 방법

  SQL Trace를 실행하는 방법은 아래와 같이 여러 방법이 있다.

 
-- SESSION LEVEL로 실행 방법
SQL> ALTER SESSION SET SQL_TRACE = TRUE;

-- SESSION LEVEL로 실행 방법 
SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(TRUE)
 
-- SESSION LEVEL로 실행 방법
SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(session_id, serial_id, TRUE)

-- 10046 TRACE EVENT를 이용한 방법
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- 인스턴스 LEVEL로 실행 방법
Init parameter 설정: SQL_TRACE = TRUE
    

TKPROF(trace 파일 출력)

  TKPROF는 SQL Trace를 통해 생성된 Trace파일을 분석이 가능한 형식으로 전환하여 출력 해준다.

 
-- 문법
TKPROF tracefile  outputfile
       [SORT=number] 
       [PRINT = number]
       [EXPLAIN=username/password]
    
  • - tracefile : SQL Trace 의해 생성된 통계정보를 가진 파일명
  • - outputfile : TKPROF가 읽기가능한 텍스트 파일로 생성할 파일명
  • - sort=option : 지정된 OPION(EXECPU,FCHDSK,PRSCPU)에 ASC 순으로 SQL 문을 정렬한다.
      ex) SORT=EXECPU -> 가장나쁜 Execute CPU값을 가진 통계값을 먼저 출력
  • - print : 지정된 수의 SQL문에 대해서만 TRACE 결과를 PRINT
  • - explain : SQL문의 EXECUTION PLAN(실행계획) 을 수립하고 저장한다.

TKPROF 실행

 
SQL> CONN / AS SYSDBA

--SQL TRACE 파일 위치의 파악
SQL> SHOW PARAMETER USER_DUMP_DEST;
NAME             TYPE     VALUE
---------------- -------- -----------------------------
user_dump_dest   string   C:\oracle\admin\oracle\udump
 

-- TKPROF실행
C:\> TKPROF C:\Oracle\admin\oracle\udump\oracle_ora_1584.trc storm.txt 
     EXPLAIN=storm/storm
 
 
-- 생성된 storm.txt파일의 내용을 확인해 보면 
-- 실행된 SQL문과 분석정보, 실행계획등이 생성되어 있다. 
============================================================================ 
SELECT a.day, SUM(a.counter), ROUND(SUM(a.counter)/b.tot, 2)*200 rate, b.tot
FROM storm_menu_counter a,
        (SELECT max(aa.counter) tot
          FROM
            (SELECT SUM(counter) counter
              FROM storm_menu_counter
              WHERE year = 2001
                   AND month= 7
              GROUP BY day)aa)b
 WHERE a.year = 2001
      AND a.month = 7
GROUP BY day , b.tot
ORDER BY day
 

call     count    cpu  elapsed  disk  query  current   rows
------- ------  ----- -------- ----- ------ --------  -----
Parse        1    0.01     0.04     1      1       0      0
Execute      1    0.00     0.00     0      0       0      0
Fetch        3    0.00     0.01    55    116       0     23
------- ------  ----- -------- ----- ------ --------  -----
total        5    0.01     0.06    56    117       0     23
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 65  (STORM)
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (GROUP BY)
      0    NESTED LOOPS
      0     VIEW
      0      SORT (AGGREGATE)
      0       VIEW
      0        SORT (GROUP BY)
      0         TABLE ACCESS (FULL) OF 'STORM_MENU_COUNTER'
      0     TABLE ACCESS (FULL) OF 'STORM_MENU_COUNTER'
      
============================================================================
    

TKPROF 결과 값

로우/컬럼 설 명
Parse SQL문이 파싱되는 단계에 대한 통계. 새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함 된다.
Execute SQL문의 실행 단계에 대한 통계. Update, Insert, Delete 문장들은 여기에 수행한 결과만 나온다.
Fetch SQL문이 실행되면서 페치된 통계
count SQL문이 파싱/실행/페치가 수행된 횟수
cpu parse, execute, fetch가 실제로 사용한 CPU시간
elapsed 작업의 시작에서 종료시까지 실제 소요된 시간
disk 디스크에서 읽혀진 데이터 블럭의 수
query 메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나 아직 커밋되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭 수. SELECT문에서는 대부분 여기에 해당하며 Update, Insert, Delete 작업시에는 소량만 발생 합니다.
current 현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한 블럭(Dirty Block)을 액세스한 블럭 수. 주로 Update, Insert, Delete 작업시 많이 발생 한다
rows SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수

참고링크

- 강좌 URL : http://www.gurubee.net/lecture/1842

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 장태길 [2007.08.28 17:03:54]
용운이 화이팅 ㅡ_ㅡ+

by 찌짐이 [2011.03.08 13:46:46]
좋은자료 잘 볼께요 ^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입