OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 10046 trace 및 tkprof 자동수행 스크립트
오라클 환경에서 10046 trace 를 수집하려면 아래 게시글 방법처럼
trace를 수동으로 켜고, sql을 실행한 다음 trace를 종료하고,
trace 파일을 찾아서 tkprof를 실행해서 txt파일을 얻은 후 읽어야함
오라클 실행계획 확인 및 트레이스 방법 ( https://positivemh.tistory.com/364 )
매번 이렇게 수집하기가 귀찮은 사람들을 위해 간편한 스크립트를 만듬
먼저 대략적인 방식을 설명하자면 system 유저로 sqlplus에 접속해서 tracefile_identifier를 설정하고
이후 10046 트레이스를 키고 유저이름.테이블명 으로 작성된 쿼리를 실행하여 tkprof 결과값까지 가져오는 방식임
스크립트
|
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
54
55
56
57
58
|
#!/bin/bash
#10046.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. 10046 trace 시작
alter session set events '10046 trace name context forever, level 12';
-- 5. SQL 입력 및 실행
-- 10046 trace 를 수집하려는 SQL 작성
select empno, ename from imsi.emp where empno>=1000;
-- 6. 10046 trace 종료
alter session set events '10046 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 b.paddr from v\$px_session a, v\$session b where a.sid(+) = b.sid and a.qcserial# IS null and b.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"
# 10. tkprof 툴을 이용하여 trace 파일을 일반 파일로 변환
echo ""
echo "###Start tkprof###"
$ORACLE_HOME/bin/tkprof $trace_file ${trace_file%.trc}_tkprof.txt sys=no
# 11. 변환 후의 trace 파일 위치 출력
echo ""
echo "###after tkprof trace file location : ${trace_file%.trc}_tkprof.txt"
echo ""
echo "for read by cat command"
echo "cat ${trace_file%.trc}_tkprof.txt"
#rm tmptxt.tmp
|
스크립트 사용전 수정할부분은 system 유저의 패스워드와 "5. SQL 입력 및 실행" 부분의 실행할 SQL임
*20240315 parallel 쿼리 수행시에도 되게끔 32번째 라인에 v$px_session 뷰 추가함
테스트 실행
현재 imsi 계정에 emp 테이블이 존재하고 system 패스워드를 알고있는 상태임
스크립트안에 system 패스워드와 쿼리를 수정해놓은 상태
emp 테이블에서 empno가 1000 이상인 empno와 ename을 조회하는 쿼리를 실행함
|
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
54
55
56
57
58
59
60
61
|
$ sh 10046.sh
###Enter the identifier for the trace file :
EMP_TEST <<<<<<<< tarce identifier 입력 후 엔터
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 7 15:00:05 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Dec 07 2023 14:58:53 +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
---------- ----------
7839 KING
7698 BLAKE
7782 CLARK
7566 JONES
7788 SCOTT
7902 FORD
7369 SMITH
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7876 ADAMS
7900 JAMES
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> 4433
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_4433_EMP_TEST.trc
###Start tkprof###
TKPROF: Release 19.0.0.0.0 - Development on Thu Dec 7 15:00:06 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
###after tkprof trace file location : /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_4433_EMP_TEST_tkprof.txt
|
설명 :
3번 째 라인에서 tarce identifier 를 입력받음
17번 째 라인에서 trace identifier 가 설정됨
20번 째 라인에서 10046 trace 가 설정됨
23번 째 라인에서 trace 를 수집할 sql이 실행됨
43번 째 라인에서 10046 trace 가 해제됨
45~47번 째 라인에서 diagnostic_dest 경로와 unique_name, pid가 수집됨
51번 째 라인에서 10046 trace 파일을 확인할 수 있음
53번 째 라인에서 해당 파일에 대해 tkprof가 실행됨
61번째 라인에서 tkprof 출력 파일 경로를 보여줌
이후 cat 명령으로 tkprof 변환된 파일 확인
|
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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
$ cat /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_4433_EMP_TEST_tkprof.txt
TKPROF: Release 19.0.0.0.0 - Development on Thu Dec 7 15:00:06 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Trace file: /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_4433_EMP_TEST.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: 0ytk436w33gf2 Plan Hash: 3956160932
select empno, ename
from
imsi.emp where empno>=1000
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 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 14
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=60 us starts=1 cost=3 size=280 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 9
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 8 0 14
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_4433_EMP_TEST.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
47 lines in trace file.
0 elapsed seconds in trace file.
|
참조 :
https://positivemh.tistory.com/364
오라클 실행계획 확인 및 트레이스 방법
OS환경 : Oracle Linux6.8(64bit) DB 환경 : Oracle Database 11.2.0.4 방법 : 오라클 SQL 실행계획 확인 및 트레이스 방법 순서 A. Autotrace B. DBMS_XPLAN.DISPLAY C. DBMS_XPLAN.DISPLAY_CURSOR D. SQL 트레이스 A. Autotrace Autotrace 설
positivemh.tistory.com
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 재기동시 발생하는 wait event (0) | 2023.12.08 |
|---|---|
| 오라클 19c ash 뷰 데이터 1분마다 백업하는 스크립트 (0) | 2023.12.07 |
| 오라클 19c not in 절에 null 허용 컬럼 관련 테스트 (0) | 2023.09.21 |
| 오라클 19c 병렬 dml 실행계획 확인 테스트 (0) | 2023.03.05 |
| 오라클 19c leading use_nl 힌트 제어 테스트 (0) | 2023.03.04 |