오라클(ORACLE) 10046 trace 생성과 TKPROF 사용법

Last Updated on 5월 9, 2021 by 태랑(정현호)

10046 Event는 Oracle 7.3 이상 버전 가능

Oracle 10046 Trace Level 별 정보

event 활성화

# 세션 레벨

ALTER SESSION SET SQL_TRACE=TRUE;   

sys나 system 유저가 아닌 일반 유저로 실행 했을 때 에러( "ORA-01031: 권한이 불충분합니다")

발생 하며, alter session 권한을 부여 합니다

SQL> grant alter session to 유저명;

# 시스템 레벨

ALTER SYSTEM SET SQL_TRACE=TURE;   

/* SYSTEM 단위로 변경하게 되면 시스템부하가 심각할 수 있음 */

# 트레이스 레벨 지정

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

/* 레벨을 지정하면 SQL_TRACE=TRUE 를 해주지 않아도 됩니다. */

# 트레이스 파일 네이밍 지정

ALTER SESSION SET TRACEFILE_IDENTIFIER='MYTRACE';     

/* USER TRACE 파일을 찾기 쉽게 네이밍을 하는 것 입니다. */

SQL 실행

SELECT * FROM sales

WHERE time_id ='16-DEC-98' AND cust_id=2000;

event 비활성화        

- SQL_TRACE=TRUE 로 실행 했을 때

ALTER SESSION SET SQL_TRACE=FALSE;

- LEVEL 지정 하였을 때

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

생성된 trace file 확인

 admin/db_sid/udump 에 mytrace.trc 이름으로 위치해 있습니다

udump위치를 모르면 show parameter user_dump  로 알수 있습니다

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

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) ;

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

위의 쿼리로 조회 하거나 오렌지 툴 에서 trace tool 기능을 이용하게 되면 아래 5개 테이블을 조회해야 함으로 아래와 같이 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

TKPROF

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

TKPROF 를 통한 trace file 변환 및 조회

tkprof 원본trace파일 원하는파일명

tkprof tracefile outputfile sys=no explain=유저/비밀번호

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

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

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

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

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



연관된 글 : dbms_sqltune.report_sql_monitor 를 활용한 sql수행 모니터링

Oracle(오라클) 11gR2 SQL Monitoring(dbms_sqltune.report_sql_monitor)

Oracle 11gR2 SQL Monitoring Oracle 11gR2에서 Real-Time SQL Monitoring 기능이 추가되었습니다.

태랑(정현호)

Senior DBA(MySQL, Oracle) - 현재 위메프에서 많은 새로움을 경험중입니다
At WeMakePrice / Previous - Oracle Korea ACS Support / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io