오라클 23ai 10046 트레이스 이전 버전과 차이점 비교

2024. 6. 20. 21:19 내맘대로긍정

프린트 하기

OS 환경 : Oracle Linux 8.4 (64bit)

DB 환경 : Oracle Database 23.4.0.24.05 ai Free

방법 : 오라클 23ai 10046 트레이스 이전 버전과 차이점 비교

오라클 23ai 환경에서 10046 트레이스를 보다보니 19c 와 다른점을 몇가지 찾게됨
본문에서는 19c 와 23ai 버전의 10046 트레이스에서 어떤점이 추가되고 변경되었는지 차이점에 대해 설명함

테스트
1. tkprof 변환 전 원시파일 차이 확인
2. tkprof 변환 후 파일 차이 확인
3. direct path insert 문 확인

1. tkprof 변환 전 원시파일 차이 확인
19c 트레이스 실행 및 쿼리 수행

1
2
3
4

SQL>
alter session set events '10046 trace name context forever, level 12';
select /*+ index(emp) */ * from emp where empno >= 7789;
alter session set events '10046 trace name context off';

트레이스 경로 확인

1
2
3
4
5
6
7
8
9

SQL> 
set lines 200 pages 1000
col value for a100
select value from v$diag_info
where name = 'Default Trace File';
 
VALUE
----------------------------------------------------------------------------------------------------
/ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_38551.trc

tkprof 수행

1
2

$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace
$ tkprof oracle19_ora_38551.trc oracle19_ora_38551.txt sys=no

23ai 트레이스 실행 및 쿼리 수행

1
2
3
4

SQL>
alter session set events '10046 trace name context forever, level 12';
select /*+ index(emp) */ * from emp where empno >= 7789;
alter session set events '10046 trace name context off';

트레이스 경로 확인

1
2
3
4
5
6
7
8
9

SQL> 
set lines 200 pages 1000
col value for a100
select value from v$diag_info
where name = 'Default Trace File';
 
VALUE
----------------------------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_84288.trc

tkprof 수행

1
2

$ cd /opt/oracle/diag/rdbms/free/FREE/trace/
$ tkprof FREE_ora_84288.trc FREE_ora_84288.txt sys=no

19c trace 원본파일 앞부분

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_38551.trc
Trace file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_38551.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:      ora19
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: 56
Unix process pid: 38551, image: oracle@ora19 (TNS V1-V3)
 
 
*** 2024-06-18T21:19:17.068740+09:00
*** SESSION ID:(11.9713) 2024-06-18T21:19:17.068755+09:00
*** CLIENT ID:() 2024-06-18T21:19:17.068757+09:00
*** SERVICE NAME:(SYS$USERS) 2024-06-18T21:19:17.068760+09:00
*** MODULE NAME:(SQL*Plus) 2024-06-18T21:19:17.068763+09:00
*** ACTION NAME:() 2024-06-18T21:19:17.068766+09:00
*** CLIENT DRIVER:(SQL*PLUS) 2024-06-18T21:19:17.068768+09:00

23ai trace 원본파일 앞부분

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

$ vi /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_84288.trc
Trace file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_84288.trc
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
Build label:    RDBMS_23.0.0.0.0_LINUX.X64_240424
ORACLE_HOME:    /opt/oracle/product/23ai/dbhomeFree
System name:    Linux
Node name:      ora23
Release:        5.4.17-2102.201.3.el8uek.x86_64
Version:        #2 SMP Fri Apr 23 09:05:57 PDT 2021
Machine:        x86_64
CLID:   P
Instance name: FREE
Instance number: 1
Redo thread mounted by this instance: 1
Oracle process number: 57
Unix process pid: 84288, NID: 4026531836, image: oracle@ora23
 
 
*** 2024-06-18T21:19:12.675982+09:00 (FREEPDB1(3))
*** SESSION ID:(195.10286) 2024-06-18T21:19:12.675996+09:00
*** CLIENT ID:() 2024-06-18T21:19:12.676004+09:00
*** SERVICE NAME:(freepdb1) 2024-06-18T21:19:12.676008+09:00
*** MODULE NAME:(SQL*Plus) 2024-06-18T21:19:12.676013+09:00
*** ACTION NAME:() 2024-06-18T21:19:12.676017+09:00
*** CLIENT DRIVER:(SQL*PLUS) 2024-06-18T21:19:12.676021+09:00
*** CONTAINER ID:(3) 2024-06-18T21:19:12.676026+09:00
*** CLIENT IP:(127.0.0.1) 2024-06-18T21:19:12.676030+09:00
*** CONNECTION ID:(Gykd+d6aST7gYxeJqMCTrA==) 2024-06-18T21:19:12.676035+09:00

trace 원본파일 앞부분 차이점 :
Instance number, NID, CONTAINER ID, CLIENT IP, CONNECTION ID 등의 차이가 존재함

2. tkprof 변환 후 파일 차이 확인
19c tkprof 변환 후 파일(앞부분)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

$ vi oracle19_ora_38551.txt
 
TKPROF: Release 19.0.0.0.0 - Development on Tue Jun 18 21:19:17 2024
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: oracle19_ora_38551.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

23ai tkprof 변환 후 파일(앞부분)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

$ vi FREE_ora_84288.txt
 
TKPROF: Release 23.0.0.0.0 - Development on Tue Jun 18 21:19:12 2024
 
Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: FREE_ora_84288.trc
Trace file version:
   Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
   Version 23.4.0.24.05
   Build label:    RDBMS_23.0.0.0.0_LINUX.X64_240424
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

tkprof 변환 후 파일(앞부분) 차이점 :
23ai 에서 Trace file version 이 추가됨

19c 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

$ vi oracle19_ora_38551.txt
********************************************************************************
SQL ID: 1dd8x2zs6g55m Plan Hash: 2787773736
 
select /*+ index(emp) */ *
from
 emp where empno >= 7789
 
 
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           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           6
 
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
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=2 pr=0 pw=0 time=38 us starts=1 cost=2 size=190 card=5)
         6          6          6   INDEX RANGE SCAN PK_EMP (cr=1 pr=0 pw=0 time=22 us starts=1 cost=1 size=0 card=5)(object id 39747)
 
 
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
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

23ai 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

$ vi FREE_ora_84288.txt
********************************************************************************
 
SQL ID: 1dd8x2zs6g55m Plan Hash: 2787773736
 
select /*+ index(emp) */ *
from
 emp where empno >= 7789
 
 
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          4          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           6
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 139
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=4 pr=0 pw=0 time=43 us starts=1 direct read=0 direct write=0 cost=2 size=1740 card=20)
         6          6          6   INDEX RANGE SCAN PK_EMP (cr=2 pr=0 pw=0 time=32 us starts=1 direct read=0 direct write=0 cost=2 size=0 card=4)(object id 86969)
 
 
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.11          0.11
********************************************************************************

tkprof 변환 후 파일(본문) 차이점 :
Row Source Operation 부분에서 23ai 에 direct read, direct write가 추가됨
19c : INDEX RANGE SCAN PK_EMP (cr=1 pr=0 pw=0 time=22 us starts=1 cost=1 size=0 card=5)(object id 39747)
23ai : INDEX RANGE SCAN PK_EMP (cr=2 pr=0 pw=0 time=41 us starts=1 direct read=0 direct write=0 cost=2 size=0 card=1)(object id 86969)
그리고 Fetch Call이 23ai 에서 1번 더 발생함 어떤 이유인지는 모르겠음(free 버전의 문제일수도 있을듯함)

19c tkprof 변환 후 파일(끝부분)

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

$ vi oracle19_ora_38551.txt
    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: oracle19_ora_38551.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.
      44  lines in trace file.
       0  elapsed seconds in trace file.

23ai tkprof 변환 후 파일(끝부분)

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

$ vi FREE_ora_84288.txt
    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: FREE_ora_84288.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.
      57  lines in trace file.
       0  elapsed seconds in trace file.

tkprof 변환 후 파일(끝부분) 차이점 :
특별한 차이점은 보이지 않음

3. direct path insert 문 확인
샘플 테이블 생성

1
2
3

SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;

10046 트레이스 걸고 append insert 진행

1
2
3
4

SQL> 
alter session set events '10046 trace name context forever, level 12';
insert /*+ append */ into big_emp select * from big_emp;
alter session set events '10046 trace name context off';

트레이스 경로 확인

1
2
3
4
5
6
7
8
9

SQL> 
set lines 200 pages 1000
col value for a100
select value from v$diag_info
where name = 'Default Trace File';
 
VALUE
----------------------------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_104103.trc

tkprof 실행

1
2

$ cd /opt/oracle/diag/rdbms/free/FREE/trace
$ tkprof FREE_ora_104103.trc FREE_ora_104103.txt sys=no

트레이스 파일 확인

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

$ vi /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_104103.txt
********************************************************************************
 
SQL ID: 88wn6vvvpdp7c Plan Hash: 3488680345
 
insert /*+ append */ into big_emp select * from big_emp
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         36          8           0
Execute      1      0.01       0.01        244        260        329       41370
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02        244        296        337       41370
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 139
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  BIG_EMP (cr=259 pr=244 pw=244 time=17618 us starts=1 direct read=0 direct write=0)
     41370      41370      41370   OPTIMIZER STATISTICS GATHERING  (cr=248 pr=244 pw=0 time=6309 us starts=1 direct read=0 direct write=0 cost=71 size=1572060 card=41370)
     41370      41370      41370    TABLE ACCESS FULL BIG_EMP (cr=247 pr=244 pw=0 time=2552 us starts=1 direct read=0 direct write=0 cost=71 size=1572060 card=41370)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Allocate CGA memory from OS                     3        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  db file scattered read                         17        0.00          0.00
  direct path write                               3        0.00          0.00
  direct path sync                                1        0.00          0.00
  Free private memory to OS                       1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

direct path write 이벤트가 발생은 했지만 Row Source Operation 에 있는 direct write 의 수치는 올라가지 않음
버그인지, free 버전의 문제인지 모르겠음.. 정식버전이 나오면 이부분은 다시 확인할 필요가 있어보임
현재 공식문서에서도 이 내용과 관련된 내용은 찾아볼수 없는 상태임
(23ai tkprof 문서에도 direct read, direct write 수치에 대한 내용은 안나와있음 https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/performing-application-tracing.html#GUID-9C03013B-6587-4475-8888-16E84DAD5806 )

결론 :
오라클 19c와 23ai 버전에서 각각 10046 트레이스 원본파일과 tkprof 변환 후 파일을 비교해보았음
먼저 trace 원본파일 앞부분은 23ai에서 Instance number, NID, CONTAINER ID, CLIENT IP, CONNECTION ID 정보가 추가됨
tkprof 변환 후 파일(앞부분)은 23ai에서 Trace file version 정보가 추가됨
tkprof 변환 후 파일(본문)은 23ai에서 Row Source Operation 부분에 direct read, direct write 가 추가됨
하지만 실제 direct path write (insert append) 를 실행했을때 direct path write 이벤트는 발생했지만 이 수치는 올라가지 않았음
이 문제가 버그인지, free 버전의 문제인지는 알아내지 못함, 정식버전이 나오면 이부분은 다시 확인할 필요가 있어보임
튜닝시 클라이언트 정보를 더 정확하게 볼수있다는 점, Row Source Operation 부분에 direct read, direct write 정보가 추가된것 외에는 크게 기능이 향상되었다고 느껴지진 않음
하지만 23ai 에서 대기 이벤트도 더 많아 졌기때문에 이런 정보들을 잘 관측한다면 튜닝에 도움이 될수 있을듯함

참조 :

https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/performing-application-tracing.html#GUID-9C03013B-6587-4475-8888-16E84DAD5806
https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=357
https://positivemh.tistory.com/1081

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 23ai 신기능 sqlplus 추가 명령 (0)	2024.06.26
오라클 23ai 신기능 테이블 엑세스 기록 확인 뷰(dba_table_access_stats) (0)	2024.06.22
오라클 23ai 신기능 Table Value Constructor(TVC) (0)	2024.06.18
오라클 19c 인덱스 리빌드 전후 용량 차이 확인 테스트 (0)	2024.06.17
오라클 23ai 신기능 UMM(Unified Memory Management) 통합메모리 관리 (0)	2024.06.14

내맘대로긍정

박영민 ympark.pro@gmail.com

Category

Menu

Comment

Link