[SQLP] SQL 트레이스

접속 세션에 트레이스 수집 설정

alter session set sql_trace = true;

수집된 트레이스 파일 위치 및 파일명 확인

• 트레이스 파일이 생성된 서버 디렉토리 확인

select value
  from v$diag_info
 where name = 'Diag Trace';
 
VALUE
----------------------------------
/oracle/diag/rdbms/ora11g/trace

• 트레이스 파일이 생성된 서버 디렉토리 및 파일명 확인

select value
  from v$diag_info
 where name = 'Default Diag Trace';
 
VALUE
----------------------------------
/oracle/diag/rdbms/ora11g/trace/ora11g_ora_22827.trc

• oracle 10g 이하 버전
  

리포트 생성

트레이스 파일 그대로 분석하기 어려움 → TKProf 유틸리티를 사용해 파일을 보기 쉽게 포맷하여 리포트를 생성 및 분석

$ tkprof [트레이스 파일명.trc][리포트 파일명.prf] sys=no

$ tkprof ora11g_ora_22827.trc report.prf sys=no

sys=no : sys사용자가 실행한 쿼리는 보고서에 나타나지 않게, SQL을 파싱하는 과정에서 내부적으로 수행되는 쿼리 제외

생성된 리포트(.prf) 출력 예시

트레이스 결과 분석

1. Call 통계(Statistics)

항목	설명
call	커서의 진행 상태에 따른 통계 정보 출력 * Parse : SQL을 파싱하고 실행 계획 생성 * Execute : SQL 커서 실행 * Fetch : 레코드를 실제로 Fetch
count	Parse, Execute, Fetch 단계가 수행된 횟수
cpu	현재 커서가 각 단계에서 사용한 cpu time
elapsed	현재 커서가 각 단계를 수행하는 데 소요된 시간
disk	디스크에서 읽은 블록 수
query	Consistent 모드로 읽은 블록 수 (MVCC model)
current	Current 모드로 읽은 블록 수 (MVCC model)
rows	각 단계에서 읽거나 갱신한 row 수

SQL 트레이스와 AutoTrace 항목 매핑

SQL 트레이스	AutoTrace	설명
current	db block gets	Current 모드로 읽은 블록 수
query	consistent gets	Consistent 모드로 읽은 블록 수
disk	physical reads	디스크에서 읽은 블록 수
fetch count	SQL*Net roundtrips to/from client	조회 결과 전송을 위해 클라이언트가 발행한 Fetch Call 횟수
fetch rows	rows processed	조회 건수

2. 실행계획 통계(Plan Statistics)

항목	설명
Rows	각 수행 단계에서 출력(Flow-Out)된 row 수
cr	Consistent 모드 블록 읽기
pr	디스크 블록 읽기
pw	디스크 블록 쓰기
time	소요시간(us=microsecond)

참고할 것
상위 노드(부모)는 하위 노드(자식)의 값을 포함한다.
아래 실행계획 통계에서 인덱스를 읽고 난 후 테이블을 엑세스하는 단계의 순수 cr은 1이다.