대용량 데이터베이스솔루션 1 (2009년)
SQL_TRACE,3.TKPROF(trace파일출력) 0 0 99,999+

by 구루비스터디 SQL_TRACE TKPROF [2009.04.29]


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 블록단위)

1. Session Level
ALTER SESSION SET 패러미터명 = TRUE;

2. Instance Level
initSID.ora 파일에 설정(패러미터명=TRUE)

=> 인스턴스레벨로 Trace를 수행시키면 전체적인 성능이 2,30% 감소하는 등 성능상 문제가 발생할 수 있어 사용하지 않는 것이 좋다.

II. TKPROF(Trace 파일 출력)

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

2. 변환 문법

3. Outpul file의 결과 컬럼 값

로우/컬럼설 명
Parsesql문이 파싱되는 단계에 대한 통계. 새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함된다.
Executesql문의 실행 단계에 대한 통계. UPDATE, DELETE, INSERT 문은 여기에 수행한 결과가 나타난다.

전체범위방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 Fetch 에는 아주 적은 값이 나타난다.
Fetchsql 문이 실행되면서 페치된 통계. 부분범위방식으로 처리된 select 문들이나 전체범위처리를 한 후 한 건을 추출하는 경우

(전체 집계, Count 등)는 주로 여기에 많은 값들이 나타나고 Execute에는 아주 적은 값이 나타난다.
Countsql문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수
CPUparse,execute,fetch가 실제로 사용한 CPU 시간 (1/100초 단위)
Elapsed작업의 시작에서 종료시까지 실제 소요된 시간
Disk디스크에서 읽혀진 데이터 블럭의 수
Query메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나 아직 commit되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭수.

select 문에서는 거의가 여기에 해당하며 Update, Delete, Insert 시에는 소량만 발생한다.
Current현 세션에서 작업한 내용을 commit하지 않아 오로지 자신에게만 유효한 블럭을 액세스한 블럭 수로 주로 Update, Delete, Insert 작업시 많이 발생한다.
select 문에서는 거의 없거나 아주 적은 양인 경우가 대부분이다.
Rowssql문을 수행한 결과에 의해 최종적으로 액세스된 로우의수

(만약 쿼리인 경우라면 서브쿼리에 의해서 추출된 로우는 제외됨)

Ⅲ. 테스트

  • 테스트 환경: 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 분석 파일

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% 이상이면 버퍼캐시 튜닝 검토

disk, query, current의 숫자는 적을수록 좋다.
이 숫자들이 크다는 것은 메모리 공유 영역의 적중률(Hit Ratio)이 낮다는 것을 의미

    • 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년에 "대용량 데이터베이스 솔루션 1" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/2472

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입