꿈꾸는 개발자, DBA 커뮤니티 구루비

I. SQL_TRACE(Trace 파일 생성)
1. 1. SQL_TRACE 기본 개념
2. 2. SQL_TRACE 사용 방법: SQL Trace와 관련된 패러미터를 설정한다.
II. TKPROF(Trace 파일 출력)
Ⅲ. 테스트
IV. 10046 TRACE LOG 버전별 비교(8i to 10g)

I. SQL_TRACE(Trace 파일 생성)

SQL을 튜닝할때 가장 많이 사용되는 강력한 도구이다.

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% 감소하는 등 성능상 문제가 발생할 수 있어 사용하지 않는 것이 좋다.

Trace 파일을 쉽게 찾을 수 있는 방법



alter session set tracefile_identifier='oraking';
파일 뒤쪽에 oraking 식별자가 붙게 되어 확인하려고 하는 트레이스 파일을 손쉽게 찾아볼 수 있다.

$cd /usr/local/oracle/admin/ORA10g/udump
$ls -lt *oraking.trc
-rw-r----- 1 oracle dba 4422 May 17 2008 ora10g_ora_14370_oraking.trc

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의 결과 컬럼 값

로우/컬럼	설 명
Parse	sql문이 파싱되는 단계에 대한 통계. 새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함된다.
Execute	sql문의 실행 단계에 대한 통계. UPDATE, DELETE, INSERT 문은 여기에 수행한 결과가 나타난다. 전체범위방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 Fetch 에는 아주 적은 값이 나타난다.
Fetch	sql 문이 실행되면서 페치된 통계. 부분범위방식으로 처리된 select 문들이나 전체범위처리를 한 후 한 건을 추출하는 경우 (전체 집계, Count 등)는 주로 여기에 많은 값들이 나타나고 Execute에는 아주 적은 값이 나타난다.
Count	sql문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수
CPU	parse,execute,fetch가 실제로 사용한 CPU 시간 (1/100초 단위)
Elapsed	작업의 시작에서 종료시까지 실제 소요된 시간
Disk	디스크에서 읽혀진 데이터 블럭의 수
Query	메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나 아직 commit되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭수. select 문에서는 거의가 여기에 해당하며 Update, Delete, Insert 시에는 소량만 발생한다.
Current	현 세션에서 작업한 내용을 commit하지 않아 오로지 자신에게만 유효한 블럭을 액세스한 블럭 수로 주로 Update, Delete, Insert 작업시 많이 발생한다. select 문에서는 거의 없거나 아주 적은 양인 경우가 대부분이다.
Rows	sql문을 수행한 결과에 의해 최종적으로 액세스된 로우의수 (만약 쿼리인 경우라면 서브쿼리에 의해서 추출된 로우는 제외됨)

Autotrace 결과와 일치하는 항목 비교


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

10g부터 service, module, action별로 트레이스를 설정할 수 있는 패키지가 소개되었다.

패키지: dbms_monitor
프로시저: serv_mod_act_trace_enable

커넥션 풀을 사용하는 어플리케이션의 경우 유용하게 사용할 수 있으며
기존에 맺은 세션뿐 아니라 새롭게 맺어지는 세션에 대해서도 자동으로 트레이스가 걸린다.

Ⅲ. 테스트

테스트 환경: 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로 설정 필요)