1장. [SQL 분석 도구] 1-3. 트레이스 수집

트레이스를 수집 및 분석하는 것은 튜닝의 시작과 끝이라고 할 수 있다. 그 정도로 중요하다.

 

1. 트레이스 수집

/* 수집시작 */
alter session set sql_trace = true;

/* 수집 종료 */
alter session set sql_trace = false;

해당 명령어를 실행하면 트레이스를 수집해서 DB 서버에 저장 된다.

 

2. 트레이스 파일 찾기

/* 트레이스 파일 위치 */
SELECT r.value || '/' || LOWER(t.instance_name) || '_ora_' || ltrim(to_char(p.spid)) || '.trc' trace_file
FROM v$process p, v$session s, v$parameter r, v$instance t
WHERE p.addr = s.paddr
AND r.name = 'user_dump_dest'
AND s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

/* 오라클에서 제공하는 */
select value
from v$diag_info
where name = 'Diag Trace';

/* */
select value
from v$diag_info
where name = 'Default Trace File';

일정한 규칙으로 네이밍이 되서 저장이 되기때문에 세션을 이용하여 위 명령어를 실행해보면 해당 파일들의 위치를 보여준다.

 

3. 리포트 파일 생성 (cmd 창에서 실행)

cd /oracle/diag/rdbms/ora11g/trace

vi ora11g_ora_22827.trc

tkprof

/* 
- sys=no 이 구문을 작성하지 않으면 우리가 분석에 필요하지 않은 부분까지 모두 수집되게 된다.
하여 해당 명령어를 추가해서 분석에 필요한 정보들만 수집하게 하는 조건이다. 
- 확장자는 아무거나 해도 된다.
*/
tkprof ora11g_ora_22827.trc report.prf sys=no

tkprof : Trace Kernel Profile, Transient Kernel Profile, Toolkit Profile 라고도 하고 리포트 원시파일을 분석하기 용이하게 포맷팅 해주는 유틸리티이다.

 

4. 트레이스 결과 분석

vi report.prf

명령어를 실행하면 위에 생성한 파일의 Call Statistics와 Plan Statistics 를 확인 할 수 있다.

Call statistics : call 단계별로 각각에 대한 지표를 보여준다.

• query, current : I/O의 총량
• elapsed : 각 Array Fetch의 경과시간 총합, 대기시간 + CPU가 할당되어 실행된 시간, 근데 이 시간이 사용자가 체감하는 시간인 것은 아니다.
• cpu : cpu가 할당되어서 실제로 실행된 시간의 합

Plan Statistics : 실행계획 단계별로 메모리 I/O나 시간 등을 보여준다.

• cr
• pr
• pw

 

통계 컬럼 설명

항목	설명
call	커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여줌 - Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계 - Execute : 커서의 실행 단계에 대한 통계 - Fetch : 레코드를 실제로 Fetch하는 데 대한 통계 (C,U,D는 이 부분이 항상 0이다. SELECT에서 가장많이 지표가 나타난다.)
count	Parse, Execute, Fetch 각 단계가 수행된 횟수
cpu	현재 커서가 각 단계에서 사용한 cpu time
elapsed	현재 커서가 각 단계를 수행하는 데 소요된 시간
disk	디스크로부터 읽은 블록 수
query	Consistent 모드에서 읽은 버퍼 블록 수
current	Current모드에서 읽은 버퍼 블록수
rows	각 단계에서 읽거나 갱신한 처리건수

 

Call Statistics의 query와 실행 통계에서의의미 비교

• query = consistent gets
• current = db block gets
• disk = physical reads
• count = SQL*Net roundtrips to/from client
• rows = rows processed

 

5. 이벤트 트레이스 걸기

alter session set events '10046' trace name context forever, level 8;

• 레벨 1 : Call Statistics + Paln Statistics
• 레벨 4: Call Statistics + Paln Statistics + 바인드변수
• 레벨 8 : Call Statistics + Paln Statistics + 대기 이벤트
• 레벨 12 : Call Statistics + Paln Statistics + 바인드 변수 + 대기 이벤트

* 레벨 4 이상 부터는 트레이스 파일이 매우 급격하게 커지므로 주의

 

본 글은 작성자가 공부한 것을 기록하기 위하여 작성한 것으로, 100% 정확한 정보가 아닐 수 있습니다. 정정을 요청하셔도 좋고 여러 의견을 주시면 너무너무 환영이고 감사하겠습니다.