JNB
rss

skin by 이글루스

오라클

sql trace 10.03.23 9:51
☞ SQL Trace


SQL Trace
 
  - SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 트레이스 파일을 만듭니다.

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

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

  - .TRC파일은 직접 읽을수가 없으므로 반드시 TKPROF 유틸리티를 실행 시켜야 합니다.

  - init.ora파일에서 SQL Trace를 지정하여 인스턴스 레벨로 Trace를 수행시키면
    전체적인 수행능력이 20~30% 정도 감소합니다.

 


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 실행 방법
 
1. ALTER SESSION SET SQL_TRACE=TRUE;
 
2 .EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(TRUE)
 
3. EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(session_id, serial_id, TRUE)
 
4. Init parameter 설정: SQL_TRACE = TRUE


-- SQL_TRACE수행
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
명령문이 처리되었습니다.
 

 


☞ TKPROF

TKPROF(trace 파일 출력)


  - TKPROF는 SQL Trace를 통해 생성된 트레이스파일을 분석이 가능한 형식으로 전환하여
     출력 합니다.
 
  Syntax

   TKPROF tracefile  outputfile  [SORT=number] [PRINT = number]
                [EXPLAIN=username/password]


  - tracefile : SQL Trace 의해 생성된 통계정보를 가진 파일명

  - outputfile : TKPROF가 읽기가능한 텍스트 파일로 생성할 파일명

  - sort=option : 지정된 OPION(EXECPU,FCHDSK,PRSCPU)에 ASCENDING 순으로
                      SQL 문을 정렬합니다.
       ex) SORT=EXECPU -> 가장나쁜 Execute CPU값을 가진 통계값을 먼저 출력합니다.

  - print : 지정된 수의 SQL문에 대해서만 TRACE 결과를 PRINT 합니다.

  - explain : SQL문의 EXECUTION PLAN(실행계획) 을 수립하고 저장합니다.

 


◈ TKPROF 실행
 
--SQL TRACE 파일 위치의 파악
SVRMGR> 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시간(1/100초 단위)

elapsed

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

 disk

 디스크에서 읽혀진 데이터 블럭의 수

 query

 메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나
 아직 커밋되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭 수
 SELECT문에서는 거의가 여기에 해당하며 Update,Insert,Delete작업시에는
 소량만 발생 합니다.

 current

 현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한
 블럭(Dirty Block)을 액세스한 블럭 수
 주로 Update, Insert, Delete작업시 많이 발생 합니다.

 rows

 SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수
 (서브쿼리에서 추출된 로우는 제외됩니다.)



대용량 데이터베이스 솔루션 1 참고
  ================================================
    * 데이터베이스 정보공유 커뮤니티 oracleclub.com
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
※ oracleclub 강좌는 개인의 학습용으로만 사용 할 수 있습니다. 학원 홍보용이나 수익을 얻기 위한 용도로
    사용을 하시면 안됩니다. ^^

        

    
Copyright 1999-2020 Zeroboard / skin by JY