Last Updated on 9월 24, 2022 by Jade(정현호)
Contents [숨기기]
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 |
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 : 추출하길 원하는 대상 지정
연관된 다른 글
![](https://hoing.io/storage/1/4930700097.jpg)
![](https://hoing.io/storage/2021/10/11899985_983476881715150_4455927366784676131_n.jpg)
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io