Last Updated on 1월 4, 2021 by 태랑(정현호)
10046 Event는 Oracle 7.3 이상 버전 가능
Oracle 10046 Trace Level 별 정보
level 0 | SQL_TRACE=FALSE 와 동일. |
level 1 | SQL_TRACE=TRUE |
level 4 | level 1 + bind 변수 정보 |
level 8 | level 1 + wait event 정보 |
level 12 | level 1 + bind 변수 + wait event 정보 |
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수행 모니터링

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