10046 event 트레이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 10046 Event[편집]
record_voice_over 10046 Event는 SQL Tuning에 있어서 필요한 Tool인가?
- Plan Statistics가 제공하지 못하는 중요한 정보를 제공해주기 때문
- - 모든 SQL 문장의 Bind 변수 값을 추적할 수 있는 유일한 방법(Level 4)
- - 모든 SQL 문장의 대기 현상(Wait)을 추적할 수 있는 유일한 방법(Level 8)
1.1 10046 trace 실행 절차[편집]
1.1.1 트레이스 식별자 지정[편집]
alter session set tracefile_identifier='test'; ==> orcl_ora_10024_test.trc 형태로 저장됨.1.1.2 sql trace 시작[편집]
ALTER SESSION SET SQL_TRACE = TRUE; -- level 1로 트레이스 활성화1.1.3 event 를 level 12 로 설정[편집]
- level 12 = (level 4 + 8) , SQL trace 정보, 실행계획, 바인드 변수, 대기 이벤트 정보를 볼 수 있다.
ALTER SESSION SET events '10046 trace name context forever, level 12';- 10046 = Query가 수행되는 이벤트. 즉 Query를 수행하는 "이벤트"를 의미.
- trace name = Trace를 수행. 사건이 발생했을 때의 행위(Action)를 의미.
- context forever, level 12 = 사건이 발생할 때마다 행위를 수행하되, 행위의 레벨을 12로
1.2 10046 디버깅 이벤트 레벨[편집]
| 레벨 | 설명 |
|---|---|
| 0 | 디버깅 이벤트 비활성화 |
| 1 |
|
| 4 |
|
| 8 |
|
| 16 |
|
| 32 |
|
| 64 |
|
- 레벨을 조합하여서 사용할 수 있음.(레벨 1을 제외한 조합 가능)
- 레벨 12(4 + 8) : 레벨 4 + 레벨 8을 동시에 활성화
- 레벨 28(4 + 8 + 16) : 레벨 4 + 레벨 8 + 레벨 16 을 동시에 활성화
- 레벨 68(4 + 64) : 레벨 4 + 레벨 64을 동시에 활성화
1.3 10046 Event Trace 자동화[편집]
- 10046 Event를 이용해 SQL Trace 수행을 자동화 스크립트
Connect …
@trace on 10046 8
-- execute query
select *
from …
where …
@trace_off
@trace_file
@tkprof trc.out
ed trc.out
trace_on.sql과 trace_off.sql Script는 Event 을 활성화 / 비활성화하는 역할.
-- trace on.sql
alter session set events ‘&1 trace name context forever, level &2’;
-- trace off.sql
alter session set events ’&1 trace name context off’;
자동화의 핵심은 trace_file의 이름을 얻는데 있다.
-- @name : trace file
-- @author : dion cho
-- @description : get trace file name
column trace file name new value trace file
select
-- 아래 부분은 환경에 맞게 적절히 수정
d.value ||’/’||p.value||’_ora_’||s.spid||’.trc’as trace_file_name
from (select value from v$parameter where name = ’instance_name’) p
, (select value from v$parameter where name = ‘user_dump_dest’) d
, (select spid from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1))) s
;
prompt &trace_file- SQL*Plus의 column 명령을 이용해서 Trace File의 이름을 얻어내는 기법을 유심히 보자.
- 잘 응용하면 많은 작업들을 SQL*Plus 내에서 자동화시킬 수 있다.
- Host Command로 tkprof를 호출하면 Formatting된 Report를 얻을 수 있다.
-- tkprof.sql
Ho tkprof &trace_file &11.4 바인드변수 추적 V$SQL_BIND_CAPTURE[편집]
- V$SQL_BIND_CAPTURE View를 통해 Bind 변수를 확인 가능
- 15분 간격으로 Capture를 수행.
- Capture주기는 _CURSOR_BIND_CAPTURE_INTERVAL Parameter값에 의해 결정.
- 기본값 : 900 (초) , 이 값을 줄임으로써 보다 공격적으로 Capture를 수행할 수 있다. 약간의 성능 저하를 감수해야 한다.
- 아무리 Capture 주기를 줄인 다고 해도 모든 SQL 문장의 Bind 변수를 Capture 하는 것은 불가능.
- 하나의 SQL Cursor에 대해 정해진 메모리 크기만큼만 Bind Capture를 수행.
- 메모리 크기의 한계는 _CURSOR_BIND_CAPTURE_AREA_SIZE Parameter 값에 의해 결정.(기본값 : 2000(Byte))
- 많은 수의 Bind 변수를 사용하는 경우, V$SQL_BIND_CAPTURE View에 일부 Bind 변수 값이 누락되는 현상이 발생할 수 있는데 이런 제약 조건 때문이다.
- Oracle은 Select 문장과 Bind 변수가 Where 절에서 사용되었을 경우에만 Bind Capture를 수행.
- V$SQL_BIND_CAPTURE View는 가장 최근에 Capture된 Bind 변수 값을 보여준다.(Extended SQL Trace와 같은 역할은 불가능)
1.5 Wait 이벤트 현상을 Monitoring View[편집]
- - 그 어떤 View도 10046 SQL Trace가 제공하는 것과 같은 포괄적인 추적 정보를 제공하지는 않는다.