I. SQL_TRACE(Trace 파일 생성)
1. SQL_TRACE 기본 개념
- 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일(.TRC)을 만든다.
- Trace 파일은 시스템을 튜닝하는데 필요한 아주 유효한 정보를 제공한다.
- .TRC 파일은 바이너리파일로 TKPROF 유틸리티를 실행하여 분석이 가능하다.
2. SQL_TRACE 사용 방법: SQL Trace와 관련된 패러미터를 설정한다.
- TIMED_STATISTICS
- 추가적인 cpu시간, 실행시간을 수집하게 한다.
- 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의 결과 컬럼 값
| 로우/컬럼 | 설 명 |
|---|
| 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문을 수행한 결과에 의해 최종적으로 액세스된 로우의수
(만약 쿼리인 경우라면 서브쿼리에 의해서 추출된 로우는 제외됨) |
Ⅲ. 테스트
- 테스트 환경: 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로 설정 필요)
문서에 대하여