오라클 19c 10053 trace 자동수행 스크립트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 10053 trace 자동수행 스크립트

오라클 환경에서 10053 trace 를 수집하려면 
trace를 수동으로 켜고, sql을 실행한 다음 trace를 종료하고 trace 파일을 찾아서 vi로 읽어야함


매번 이렇게 수집하기가 귀찮은 사람들을 위해 간편한 스크립트를 만듬
먼저 대략적인 방식을 설명하자면 system 유저로 sqlplus에 접속해서 tracefile_identifier를 설정하고 
이후 10053 트레이스를 키고 유저이름.테이블명 으로 작성된 쿼리를 실행하여 트레이스 경로까지 가져오는 방식임

 

스크립트

#!/bin/bash
#10053.sh
 
# 0. apply profile
. ~/.bash_profile
 
# 1. 사용자로부터 입력값 받기
echo "###Enter the identifier for the trace file : "
read input
 
# 2. sqlplus를 이용하여 oracle db 접속 및 결과값 파일로 출력
sqlplus system/oracle <<EOF
-- 3. tracefile_identifier 파라미터 설정
set lines 200 pages 1000
alter session set tracefile_identifier='$input';
 
-- 4. 10053 trace 시작
alter session set events '10053 trace name context forever, level 1';
 
-- 5. SQL 입력 및 실행
-- 10053 trace 를 수집하려는 SQL 작성
select empno, ename from imsi.emp where empno>=1000;
 
-- 6. 10053 trace 종료
alter session set events '10053 trace name context off';
 
-- 7. diagnostic_dest 값과 db_unique_name, pid 값을 파일로 출력
set pagesize 0 feedback off verify off heading off echo off
spool tmptxt.tmp
select adr_home from v\$diag_trace_file_contents where adr_home like '%rdbms%' and rownum<=1;
select db_unique_name from v\$database;
select spid from v\$process where addr = (select paddr from v\$session where audsid = userenv('sessionid'));
spool off;
exit;
EOF
 
# 8. 결과값 파일을 읽어 변수에 저장
diagnostic_dest=$(awk 'NR==2 {print $1}' tmptxt.tmp)
db_unique_name=$(awk 'NR==4 {print $1}' tmptxt.tmp)
pid=$(awk 'NR==6 {print $1}' tmptxt.tmp)
 
# 9. 생성된 trace 파일 위치 찾기 및 출력
trace_file="${diagnostic_dest}/trace/${db_unique_name}_ora_${pid}_${input}.trc"
echo ""
echo "###trace file location : $trace_file"
echo ""
echo "for read by vi command"
echo "vi $trace_file"
#rm tmptxt.tmp

스크립트 사용전 수정할부분은 system 유저의 패스워드와 "5. SQL 입력 및 실행" 부분의 실행할 SQL임

 

 

테스트 실행
현재 imsi 계정에 emp 테이블이 존재하고 system 패스워드를 알고있는 상태임
스크립트안에 system 패스워드와 쿼리를 수정해놓은 상태
emp 테이블에서 empno가 1000 이상인 empno와 ename을 조회하는 쿼리를 실행함

$ sh 10053.sh
###Enter the identifier for the trace file :
EMP_10053   <<<<<<<< tarce identifier 입력 후 엔터
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 14 16:14:00 2023
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Thu Dec 14 2023 16:13:17 +09:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SQL> SQL>
Session altered.
 
SQL> SQL> SQL>
Session altered.
 
SQL> SQL> SQL> SQL>
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
 
14 rows selected.
 
SQL> SQL> SQL>
Session altered.
 
SQL> SQL> SQL> SQL> SQL> /ORA19/app/oracle/diag/rdbms/oracle19/oracle19
SQL> oracle19
SQL> 30639
SQL> SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
###trace file location : /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_30639_EMP_10053.trc
 
for read by vi command
vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_30639_EMP_10053.trc

설명 :
3번 째 라인에서 tarce identifier 를 입력받음
17번 째 라인에서 trace identifier 가 설정됨
20번 째 라인에서 10053 trace 가 설정됨
23번 째 라인에서 trace 를 수집할 sql이 실행됨
43번 째 라인에서 10053 trace 가 해제됨
45~47번 째 라인에서 diagnostic_dest 경로와 unique_name, pid가 수집됨
51번 째 라인에서 10053 trace 파일을 확인할 수 있음
54번 째 라인에서 vi {trace 파일명} 를 보여줌(복사해서 바로 vi 실행가능)

 

이후 vi 명령으로 10053 트레이스 파일 확인

$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_30639_EMP_10053.trc
Trace file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_30639_EMP_10053.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /ORA19/app/oracle/product/19.0.0/db_1
System name:    Linux
Node name:      oel8
Release:        4.18.0-147.el8.x86_64
Version:        #1 SMP Tue Nov 12 11:05:49 PST 2019
Machine:        x86_64
Instance name: oracle19
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 30639, image: oracle@oel8 (TNS V1-V3)
 
 
*** 2023-12-14T16:14:00.405852+09:00
*** SESSION ID:(9.51867) 2023-12-14T16:14:00.405876+09:00
*** CLIENT ID:() 2023-12-14T16:14:00.405879+09:00
*** SERVICE NAME:(SYS$USERS) 2023-12-14T16:14:00.405882+09:00
*** MODULE NAME:(SQL*Plus) 2023-12-14T16:14:00.405886+09:00
*** ACTION NAME:() 2023-12-14T16:14:00.405888+09:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-12-14T16:14:00.405891+09:00
 
Registered qb: SEL$1 0x343cb80 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=23330 hint_alias="EMP"@"SEL$1"
 
SPM: statement not found in SMB
SPM: capture of plan baseline is OFF
 
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
Global Manual Dop: 1 - Rounded?: no
 
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
.
.
(중략)
.
.
Query Block Registry:
SEL$1 0x343cb80 (PARSER) [FINAL]
 
:
    call(in-use=13928, alloc=109704), compile(in-use=110160, alloc=171512), execution(in-use=9320, alloc=12144)
 
End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================

 

 

참조 : https://positivemh.tistory.com/960

오라클 19c 10046 trace 및 tkprof 자동수행 스크립트