오라클 19c 다른 세션 트레이스 설정

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 다른 세션 트레이스 설정

오라클에서 내 세션에 10046 트레이스를 걸기위해선 아래 링크처럼
내 세션에서 alter session set events '10046 trace name context forever, level 12'; 명령을 수행한 뒤 쿼리를 실행해야함, 이 명령으로는 다른세션에 트레이스를 설정할수 없음
오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 ( https://positivemh.tistory.com/960 )
오라클 실행계획 확인 및 트레이스 방법 ( https://positivemh.tistory.com/364 )
다른 세션에 10046 트레이스를 걸려면 dbms_monitor 패키지를 이용해야함
본문에서는 다른 세션에 트레이스를 거는 방법을 설명하고 트레이스 결과를 확인해봄

 

 

dbms_monitor 구문

1 2 3 4 5 6

DBMS_MONITOR.SESSION_TRACE_ENABLE(     session_id   IN  BINARY_INTEGER DEFAULT NULL,     serial_num   IN  BINARY_INTEGER DEFAULT NULL,     waits        IN  BOOLEAN DEFAULT TRUE,     binds        IN  BOOLEAN DEFAULT FALSE,     plan_stat    IN  VARCHAR2 DEFAULT NULL);

인자 설명
session_id : 트레이스를 실행할 세션 id를 입력, NULL 입력시 본인 세션을 트레이스함
원문 : Client Identifier for which SQL trace is enabled. If omitted (or NULL), the user's own session is assumed.
serial_num : 트레이스를 실행할 serial#을 입력, NULL 입력시 세션을 식별할 때 session_id만 사용함
원문 : Serial number for this session. If omitted (or NULL), only the session ID is used to determine a session.
waits : true로 설정하는 경우 wait 정보까지 포함함
원문 : If TRUE, wait information is present in the trace
binds : true로 설정하는 경우 바인드 변수 정보까지 포함함
원문 : If TRUE, bind information is present in the trace
plan_stat : row source 통계를 수집하는 빈도를 정함, never(안함), first_execution(처음만 수집, null과 동일), all_execution(모두 수집) 중 하나를 입력해야함
원문 : Frequency at which we dump row source statistics. Value should be 'NEVER', 'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'.

 

 

10046 트레이스 레벨을 아래와 같이 설정할 수 있는데
dbms_monitor에서 wait만 설정하면 10046 트레이스 level 8 과 동일한듯하고, binds만 설정하면 10046 트레이스 level 4 과 동일한듯하고
wait, binds, plan_stat 까지 설정하면 10046 트레이스 level 12와 동일한 설정인듯함
LEVEL 0 : 트레이스 안 함. SQL_TRACE=false 로 설정한 것과 동일한 효과
LEVEL 1 : 일반적인 SQL 트레이스(SQL_TRACE=true) 정보 제공
LEVEL 4: SQL TRACE 정보 + BIND 정보 제공
LEVEL 8: SQL TRACE 정보 + WAIT EVENT 정보 제공
LEVEL 12: SQL TRACE 정보 + BIND 정보 + WAIT EVENT 정보 제공

 

 

테스트
트레이스 대상 세션 확인

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL>  set lines 200 pages 1000 col username for a10 col tracefile for a100 select s.username, s.sid, s.serial#, s.sql_trace, p.tracefile from v$session s, v$process p where s.paddr=p.addr and type != 'BACKGROUND' and s.username = 'IMSI';   USERNAME          SID    SERIAL# SQL_TRAC TRACEFILE ---------- ---------- ---------- -------- ---------------------------------------------------------------------------------------------------- IMSI               22      29713 DISABLED /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_9792.trc

sql_trace 가 disable 상태임

 

 

프로세스 트레이스 설정(sid와 serial# 사용)

1 2 3

SQL> exec dbms_monitor.session_trace_enable(22, 29713, true, true);   PL/SQL procedure successfully completed.

 

 

트레이스 대상 세션 재확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL>  set lines 200 pages 1000 col username for a10 col tracefile for a100 select s.username, s.sid, s.serial#, s.sql_trace, p.tracefile from v$session s, v$process p where s.paddr=p.addr and type != 'BACKGROUND' and s.username = 'IMSI'; USERNAME          SID    SERIAL# SQL_TRAC TRACEFILE ---------- ---------- ---------- -------- ---------------------------------------------------------------------------------------------------- IMSI               22      29713 ENABLED  /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_9792.trc

sql_trace 컬럼이 enabled로 변경됨

 

 

대상 세션에서 쿼리 수행

1 2 3 4 5

SQL> select * from emp where empno = 7839;        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7839 KING       PRESIDENT            17-NOV-81       5000                    10

 

 

프로세스 트레이스 해제(sid와 serial# 사용)

1 2 3

SQL> exec dbms_monitor.session_trace_disable(22, 29713);   PL/SQL procedure successfully completed.

 

 

트레이스 파일 tkprof 로 변환

1 2 3 4 5 6

$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/ $ tkprof oracle19_ora_9792.trc oracle19_ora_9792_tkpof.txt sys=no    TKPROF: Release 19.0.0.0.0 - Development on Thu Apr 11 13:50:53 2024   Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

 

트레이스 파일 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

$ cat oracle19_ora_9792_tkpof.txt   TKPROF: Release 19.0.0.0.0 - Development on Thu Apr 11 13:50:53 2024   Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.   Trace file: oracle19_ora_9792.trc Sort options: default   ******************************************************************************** count    = number of times OCI procedure was executed cpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executing disk     = number of physical reads of buffers from disk query    = number of buffers gotten for consistent read current  = number of buffers gotten in current mode (usually for update) rows     = number of rows processed by the fetch or execute call ********************************************************************************   SQL ID: 2qy6kzx420vfp Plan Hash: 2949544139   select * from  emp where empno = 7839     call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1      0.00       0.00          0          2          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3      0.00       0.00          0          2          0           1   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=23 us starts=1 cost=1 size=38 card=1)          1          1          1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=11 us starts=1 cost=0 size=0 card=1)(object id 27292)     Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       1        0.00          0.00       ********************************************************************************

정상적으로 10046 트레이스 결과가 확인됨

 

 

참고로 이 방식으로 백그라운드 프로세스의 트레이스도 걸수 있음

 

 

참조 : 

오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 ( https://positivemh.tistory.com/960 )
오라클 실행계획 확인 및 트레이스 방법 ( https://positivemh.tistory.com/364 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_MONITOR.html#GUID-C9054D20-3A70-484F-B11B-CC591A10D609