꿈꾸는 개발자, DBA 커뮤니티 구루비

이 강좌는 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문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수