오라클(ORACLE) 실행 계획 - 10046 trace 생성과 TKPROF

Share

Last Updated on 9월 24, 2022 by Jade(정현호)

10046 Event

10046 Event 는 Oracle 7.3 이상 버전에서 사용할 수 있는 SQL Trace 생성 이벤트 입니다.

실행한 SQL 에 대해서 실행 계획 및 SQL 실행 에 따른 여러 Resource 의 사용정보 및 실행에 사용한 Bind 정보, 실행과정에서 발생되는 Wait Event 정보를 기록합니다.
          

Oracle 10046 Trace Level 별 정보

10046 트레이스는 지정한 레벨 별로  수집 되는 정보의 종류가 달라지게 됩니다

level 0  

 SQL_TRACE=FALSE 와 동일.

level 1  

 SQL_TRACE=TRUE
 와 동일 일반적인 Trace 정보 제공(default)

level 4  

 level 1 + bind 변수 정보

level 8  

 level 1 + wait event 정보

level 12 

 level 1 + bind 변수 + wait event 정보

            

Event 활성/비활성화

이벤트의 활성화/비활성화는 세션 레벨 , 시스템 레벨 등으로 나눌수 있습니다.
       

세션 레벨

SQL> ALTER SESSION SET SQL_TRACE=TRUE;


sys나 system 유저가 아닌 일반 유저로 실행 시 ORA-01031 발생
일반 유저로 실행시 에러( "ORA-01031: 권한이 불충분합니다") 발생하게 되면 alter session 권한을 부여 합니다

SQL> grant alter session to 유저명;
         

시스템 레벨

SQL> ALTER SYSTEM SET SQL_TRACE=TURE;

SYSTEM 단위로 Event 를 설정시 모든 SQL이 Trace 가 생성 됨으로 시스템 부하가 심각하게 발생 할 수 있습니다.

SYSTEM 단위는 부득이 하게 특별한 이유가 있을 경우, 매우 한시적(짧은 시간)으로 설정 후 바로 비활성화를 하는 편으로 고려 해야 합니다.
     

트레이스 레벨 지정

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

레벨을 지정하면 SQL_TRACE=TRUE 를 해주지 않아도 활성화가 됩니다.
    

트레이스 파일 네이밍 지정

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MYTRACE';     

트레이스 파일에 네이밍 지정은 필수는 아니지만 USER TRACE 파일을 찾기 쉽게 네이밍을 하는 것 입니다.
다수의 유저가 계속적, 반복적으로 10046 을 생성하는 환경일 경우 내가 수행한 Trace 를 찾는게 어려울 수 있습니다.

이럴 경우 별도의 네이밍을 지정하여 Trace 를 수행시 서버에서 생성된 Event Trace 파일을 조금 더 손 쉽게 찾을 수 있습니다
     

생성된 trace 파일 확인

10g 버전 
$ORACLE_BASE/admin/db_sid/udump 에 mytrace.trc 이름으로 위치해 있습니다


11g 이상
diag trace 경로 아래에 있습니다.

디렉토리 경로 정보는 show parameter user_dump  나 show parameter drag 로 경로를 확인 하면 됩니다

또는 아래 쿼리로 파일을 조회할 수 있습니다

col "User Trace File" format a70 

select r.value || case 
when d.platform_name like 'M%' then '\'else '/' END || lower(t.instance_name) || '_ora_' || ltrim(to_char(p.spid)) || '.trc' trace_file         
from v$process p, v$session s, v$parameter r, v$instance t, v$database d
where p.addr = s.paddr                                   
and r.name = 'user_dump_dest'                           
and s.sid = (select sid from v$mystat where rownum = 1);

** 고도화 책 스크립트 수정본 **


위의 쿼리를 일반 유저가 조회를 할 경우 아래 5개 테이블에 대해서 SYS 유저로 조회 권한을 부여 해야 쿼리가 가능 합니다.


조회 권한이 필요한 테이블

SYS.V_$MYSTAT, SYS.V_$INSTANCE ,
SYS.V_$PARAMETER , SYS.V_$SESSION ,
SYS.V_$PROCESS, SYS.V_$DATABASE


SYS 유저로 일반 유저에게 SELECT 권한 부여

GRANT SELECT ON SYS.V_$DATABASE TO 유저명;
GRANT SELECT ON SYS.V_$MYSTAT TO 유저명;
GRANT SELECT ON SYS.V_$INSTANCE TO 유저명;
GRANT SELECT ON SYS.V_$PARAMETER TO 유저명;
GRANT SELECT ON SYS.V_$SESSION TO 유저명;
GRANT SELECT ON SYS.V_$PROCESS TO 유저명;


조회 결과

User Trace File
-----------------------------------------------------
/oracle/admin/testdb/udump/testdb_ora_2839.trc
       

Event 비활성화

SQL_TRACE=TRUE 로 실행 했을 경우

SQL> ALTER SESSION SET SQL_TRACE=FALSE;



LEVEL 지정 하여 활성화 하였을 경우

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

          

TKPROF

TKPROF 는 생성된 Trace 을 분석이 가능한 형식으로 변환합니다


명령어 사용법
tkprof    trace파일   원하는파일명


사용 예시
tkprof tracefile outputfile sys=no explain=유저/비밀번호


옵션 설명 

tracefile : 생성된 유저 trace 파일이름

outputfile : TKPROF 수행후 출출 되는 파일이름

sys: 대상 SQL을 수행하기 위해 오라클이 내부적으로 수행하는 SQL에 대한 정보 추출여부
sys=no  는 RECURSIVE SQL 을 출력 하지 않습니다.

만약 SQL TRACE 생성을 sys 유저로 수행 후 sys=no 옵션을 붙이면 실제로 수행한 SQL 에 대한 정보도 나오지 않게 됩니다. 그러므로 sys유저로 trace 를 생성 후 sys=no 옵션은 붙이지 않고 변환 해야 합니다.


expain : 추출하길 원하는 대상 지정



연관된 다른 글

       

0
글에 대한 당신의 생각을 기다립니다. 댓글 의견 주세요!x