다른 명령
ㄹㅇㄹ
10046 Event
record_voice_over 10046 Event는 SQL Tuning에 있어서 필요한 Tool인가?
- Plan Statistics가 제공하지 못하는 중요한 정보를 제공해주기 때문
- - 모든 SQL 문장의 Bind 변수 값을 추적할 수 있는 유일한 방법(Level 4)
- - 모든 SQL 문장의 대기 현상(Wait)을 추적할 수 있는 유일한 방법(Level 8)
10046 trace 실행 절차
트레이스 식별자 지정
alter session set tracefile_identifier='test'; ==> orcl_ora_10024_test.trc 형태로 저장됨.
sql trace 시작
ALTER SESSION SET SQL_TRACE = TRUE; -- level 1로 트레이스 활성화https://dbcafe2.mycafe24.com/w/index.php/10046 event 트레이스
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로
sql 실행
select * from tb_xxx;
sql trace 종료
ALTER SESSION SET SQL_TRACE = FALSE;
10046 event 끄기
- 꺼주지 않으면, 수행하는 모든 SQL 에 대한 정보를 모은다.
- level 0으로 지정해서 비활성화 하지 않는것에 유의할것.
ALTER SESSION SET events '10046 trace name context off';
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을 동시에 활성화
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 &1
바인드변수 추적 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와 같은 역할은 불가능)
Wait 이벤트 현상을 Monitoring View
- - 그 어떤 View도 10046 SQL Trace가 제공하는 것과 같은 포괄적인 추적 정보를 제공하지는 않는다.