I. SQL_TRACE(Trace 파일 생성)

1. SQL_TRACE 기본 개념

• 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일(.TRC)을 만든다.
• Trace 파일은 시스템을 튜닝하는데 필요한 아주 유효한 정보를 제공한다.
• .TRC 파일은 바이너리파일로 TKPROF 유틸리티를 실행하여 분석이 가능하다.

2. SQL_TRACE 사용 방법: SQL Trace와 관련된 패러미터를 설정한다.

• TIMED_STATISTICS
  • 추가적인 cpu시간, 실행시간을 수집하게 한다.
• SQL_TRACE
  • sql_trace의 수행여부를 설정한다.
• USER_DUMP_DEST
  • Trace파일이 생성되는 디렉토리를 지정한다.
• MAX_DUMP_FILE_SIZE
  • Trace파일의 최대 크기를 설정한다.(단위: O/S 블록단위)

II. TKPROF(Trace 파일 출력)

1. SQL_TRACE를 통해 생성된 Trace 파일을 분석이 가능한 형식으로 전환하여 출력한다.

2. 변환 문법

• TKPROF tracefile outputfile SORT = parameters PRINT = number EXPLAIN = username/password
  예제: TKPROF ora_12343.trc trc1.txt SORT=(EXECPU) EXPLAIN = scott/tiger
  • tracefile: Trace 파일명
  • outputfile: Output 파일명
  • sort: 지정된 패러미터의 역순으로 sql문을 정렬
  • print: 출력할 sql문의 수
  • explain: sql문의 실행 계획 출력

3. Outpul file의 결과 컬럼 값

Ⅲ. 테스트

• 테스트 환경: Oracle 10.2.0.4(10g)

1) 스키마 생성 스크립트

-- create objects
create table testtrc as
select rownum as rnum, object_name
from all_objects 
where rownum <= 500000;

create table test_master as
select object_id, owner, object_name
from all_objects
where length(object_name) <= 5;

create index idx_testtrc on testtrc(object_name);
create index idx_master on test_master(owner);

analyze table testtrc compute statistics;
analyze index idx_testtrc compute statistics;
analyze table test_master compute statistics;
analyze index idx_master compute statistics;

2) SQL_TRACE 설정 및 SQL문 실행

alter session set sql_trace = true;

select rnum, b.object_name, object_id, owner 
from testtrc.testtrc a, testtrc.test_master b
where a.object_name = b.object_name
and b.owner = 'SYS'
union
select sum(rnum), min(b.object_name), avg(object_id), max(owner)
from testtrc.testtrc a, testtrc.test_master b
where a.object_name = b.object_name
order by 4,1,3,2 desc;		

3) Trace file과 tkprof 분석 파일

• Trace file
• tkprof 분석파일

4) TKPROF 분석 파일의 이해

• Misses im library cache during parse:1
  공유 SQL 영역에서 파싱된 결과를 찾지 못하여 실제 파싱작업을 하게 되었다는 것을 의미한다.

• COUNT
  • parse, execute, fetch 동일
    embedeed sql에서 hold_cursor=no로 설정하면 메모리에 sql 수행한 결과를 남겨 놓지 않아 같은 sql이라도 계속 파싱을 수행한다.
    이값을 yes로 수행하거나 pl/sql 루프 사용하면 parse 작업 감소하고 명시적 커서를 사용하면 execute 작업도 감소한다.
  • execute, fetch 동일
    Execute count 와 Fetch count 가 동일하게 크다고하면 ARRAY FETCH 사용을 고려한다.

• CPU
  • execute 값이 클 때
    전체범위처리
    order by, group by
    넒은 처리범위
    비효율적인 인덱스(인덱스 전부 탐색)
  • fetch 값이 클 때
    부분범위
    count, max, min
    부적절한 실행계획
    비효율 인덱스

• ELAPSED
  CPU 시간과 ELAPSED 시간의 차이는 적을수록 좋다. 만약 CPU시간에 비해 ELAPSED 시간이 훨씬 많다면, 그 원인은 다음 중 하나일 가능성이 높다.
  • 주변의 다른 세션에서 많은 부하를 발생시켜 시스템 전체에 부하가 많이 걸려있는 경우
  • 어플리케이션의 문제이거나 다량의 데이타 처리에 따른 I/O 병목현상이 발생한 경우

• OVERALL TOTALS
  • cpu
    parse count * 0.01 보다 cpu가 크면 라이브러리 캐쉬 튜닝 검토
  • disk
    parse count * 0.03 보다 disk(데이터 딕셔너리 정보를 계속 디스크로부터 로딩한다는 뜻)가 크면 딕셔너리 캐쉬 튜닝 검토
  • query + current = memory
    execute disk + fetch disk가 execute query + fetch query + execute current + fetch current 의 10% 이상이면 버퍼캐시 튜닝 검토

• • recursive statement
    sql문 장 실행시 실행한 문장뿐 아니라 테이블 존재, 컬럼 존재 등의 검증위한 recursive 문 실행
    이 분석정보는 의미는 그다지 없음

IV. 10046 TRACE LOG 버전별 비교(8i to 10g)

1. 10046 TRACE란?

• 일반적으로 SQL_TRACE와 동일하나 레벨에 따라 추가적인 정보 확인이 가능하다.
• LEVEL 0 : 트레이스 안 함. SQL_TRACE=false 로 설정한 것과 동일한 효과
• LEVEL 1 : 일반적인 SQL 트레이스(SQL_TRACE=true) 정보 제공
• LEVEL 4: SQL TRACE 정보 + BIND 정보 제공
• LEVEL 8: SQL TRACE 정보 + WAIT EVENT 정보 제공
• LEVEL 12: SQL TRACE 정보 + BIND 정보 + WAIT EVENT 정보 제공

2. 사용설정 방법

• ALTER SESSION 사용
  • SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
  • SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL; (9i, 10g 에서만 사용)

3. 10046 TRACE LOG(level 12)의 tkprof 결과 비교

• 버전별 큰 차이가 나지 않는다.
• 8i에서는 대기이벤트 정보를 확인할 수 없다.

4. 10046 TRACE LOG 비교

• Trace Log Header
  오라클 서버 및 이 SQL을 수행한 세션의 정보
  8i, 9i는 거의 흡사한 구조를 가지며 10g의 세션 정보중 SERVICE NAME(분산 트랜잭션, RAC에서의 모니터링 가능) 추가

• Trace Log Content
  각 단위 SQL의 수행정보의 모음(메타정보 + Parsing, Execute, Fetch정보)
  • bind정보
    8i, 9i는 동일하며 10g에서 많은 변화가 있는 것처럼 보이나 필드명이 변경된 것일 뿐이다.
  • execute, fetch 정보
    버전별로 정보가 거의 동일하나 8i의 row수는 16진수로 표시되는 차이가 있다.
  • stat, event 정보
    stat은 tkprof 파일의 plan 정보를 보여주는 부분을 의미한다.
    9i부터 가장 오랫동안 지속된 operation, 얼마나 많은 블록을 사용했는지에 대한 정보가 보강되었다.
    (STATISTICE_LEVEL 패러미터 ALL로 설정 필요)

문서에 대하여

• 최초작성자 : 이신재
• 최초작성일 : 2009년 3월 23일
• 이 문서는 오라클클럽 01.대용량 데이터베이스 스터디 모임에서 작성하였습니다.