SQL Trace & tkprof

SQL Trace

 

* 10046 Trace

level 0   - Trace 하지않음 sql_trace=false 와 동일

level 1   - 일반적인 SQL Trace 정보제공

level 4   - SQL Trace 정보 + Bind 정보제공

level 8   - SQL Trace 정보 + Wait Event 정보제공

level 12 - SQL Trace 정보 + Bind 정보제공 + Wait Event 정보제공

 

* 10046 Trace  순서

alter session set sql_trace = true;

alter session set events '10046 trace name context forever, level [0, 1, 4, 8, 12]';

 

SQL 수행 -> user dump dest에 trc파일 생성

 

alter session set sql_trace = false;

alter session set events '10046 trace name context off';

 

* Trace 파일 이름 지정

alter session set tracefile_identifier='MYTRACE';

 

* tkprof 사용

tkprof tracefile [trace file] sys=no output = [파일명]

 

 

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 등의 그룹함수를 사용한 경우라면 큰 의미가 없음.

[출처] 실행 계획 확인 방법 - SQL Trace & TKPROF 이용 방법 |작성자 themisoo

 

session trace

* dbms_system.set_sql_trace_in_session 프로시저를 사용하는 방법

- 오라클에서 권장하지 않음

SQL> dbms_system.set_sql_trace_in_session(sid => 123, serial# => 12345, sql_trace => true);

 

* dbms_monitor 패키지

- Oracle 10g 부터 사용가능

trace 등록

SQL> begin

dbms_monitor.session_trace_enable(

session_id => 123 ,

serial_num => 12345 ,

waits => true ,

binds => true );

end;

/

OR

SQL> exec dbms_monitor.session_trace_enable(123, 123456, true, true);

 

trace 해제

SQL> begin

dbms_monitor.session_trace_disable (

session_id => 123 ,

serial_num => 123456 );

end;

/

OR

SQL> exec dbms_monitor.session_trace_disable(123, 123456 );