OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 :오라클 19c 인덱스 생성시 nosort 옵션 대기 이벤트 확인
인덱스 생성시 정렬 작업을 하는 경우 pga가 모자란 경우 temp 영역에서 정렬 작업을 하기 때문에 direct path read, direct path write(또는 direct path read temp, direct path write temp)가 발생할수도 있다고 생각드는데
인덱스 nosort 옵션 사용해 생성 시에도 direct path read, direct path write 이벤트가 발생 하는것을 확인함
인덱스 생성시 정렬 작업을 하지 않는다면 이 이벤트도 발생하지 않아야 할것 같은데, 왜 발생하는지에 대한 의문이 생겨 조금 더 테스트 해봄
이전 테스트와 같이 용량이 큰 테이블에 nosort 옵션을 사용해 인덱스를 만들기 위해 테이블스페이스는 mssm 방식으로 생성함
*이전 테스트 : 오라클 19c 인덱스 생성시 nosort 옵션(mssm 환경) ( https://positivemh.tistory.com/1016 )
초기 환경 구성
샘플 테이블스페이스 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop tablespace noso including contents and datafiles;
create tablespace noso datafile '/oradata1/oracle19/noso01.dbf' size 5g autoextend on segment space management manual;
Tablespace created.
SQL> alter user imsi quota unlimited on noso;
User altered.
|
샘플 대용량 테이블 생성 및 데이터 삽입
|
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
|
SQL>
conn imsi/imsi
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
tablespace noso
nologging;
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..1000000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..1000000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
여러번 반복 insert
|
용량 확인
|
1
2
3
4
5
6
7
8
|
SQL>
col segment_name for a20
select segment_name, bytes/1024/1024 mb from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NAME MB
-------------------- ----------
SAMPLE_T 912
|
테스트1. nosort 옵션 없이 인덱스 생성
sort1 테이블 생성(id1, id2 순으로 정렬)
|
1
2
3
|
SQL>
drop table sort1 purge;
create table sort1 tablespace noso as select * from sample_t order by id1, id2;
|
trace 실행 후 sort1 테이블 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index sort1_ix01 on sort1(id1, id2) tablespace noso;
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
|
생성된 trace 경로 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a20
col value for a80
select name, value from v$diag_info
where name = 'Default Trace File';
NAME VALUE
-------------------- --------------------------------------------------------------------------------
Default Trace File /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31574.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31574.trc oracle19_ora_31574_t1_tkprof.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 17 10:06:20 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
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31574_t1_tkprof.txt
SQL ID: bqj5n3j4pc5yz Plan Hash: 423868738
create index sort1_ix01 on sort1(id1, id2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 2 0
Execute 1 4.37 5.64 155490 116440 37302 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.38 5.64 155490 116441 37304 0
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 INDEX BUILD NON UNIQUE SORT1_IX01 (cr=116446 pr=155490 pw=72706 time=5639714 us starts=1)(object id 0)
10000000 10000000 10000000 SORT CREATE INDEX (cr=116143 pr=155489 pw=39366 time=2744622 us starts=1)
10000000 10000000 10000000 TABLE ACCESS FULL SORT1 (cr=116143 pr=116123 pw=0 time=471619 us starts=1 cost=31618 size=130000000 card=10000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
index (re)build lock or pin object 4 0.00 0.00
db file scattered read 923 0.00 0.08
PGA memory operation 266 0.00 0.00
direct path write temp 1271 0.00 0.38
direct path read temp 1540 0.00 0.04
db file sequential read 1 0.00 0.00
direct path write 8535 0.00 1.15
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
인덱스 생성 시 정렬작업을 하기때문에 PGA memory operation, direct path write temp, direct path read temp 이벤트가 발생함
추가로 direct path write 이벤트도 발생함
테스트2. nosort 옵션 사용 인덱스 생성
sort1 테이블 기존 인덱스 삭제
|
1
|
SQL> drop index sort1_ix01;
|
트레이스 설정을 위해 sqlplus 세션 재접속
trace 실행 후 sort1 테이블 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index sort1_ix02 on sort1(id1, id2) tablespace noso nosort;
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
|
생성된 trace 경로 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a20
col value for a80
select name, value from v$diag_info
where name = 'Default Trace File';
NAME VALUE
-------------------- --------------------------------------------------------------------------------
Default Trace File /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31976.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31976.trc oracle19_ora_31976_t2_tkprof.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 17 10:16:20 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
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31976_t2_tkprof.txt
SQL ID: 5jdrp7csymmv0 Plan Hash: 35634007
create index sort1_ix02 on sort1(id1, id2) nosort
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 2.61 3.53 116123 116440 37298 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.61 3.53 116123 116441 37298 0
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 INDEX BUILD NON UNIQUE SORT1_IX02 (cr=116446 pr=116123 pw=33340 time=3531915 us starts=1)(object id 0)
10000000 10000000 10000000 TABLE ACCESS FULL SORT1 (cr=116143 pr=116123 pw=0 time=902691 us starts=1 cost=31618 size=130000000 card=10000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
index (re)build lock or pin object 4 0.00 0.00
Disk file operations I/O 2 0.00 0.00
db file scattered read 923 0.00 0.11
direct path write 8535 0.00 1.05
log file sync 1 0.00 0.00
PGA memory operation 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 6.68 6.68
********************************************************************************
|
인덱스 생성 시 nosort 옵션을 사용했기 때문에 정렬 작업을 하지 않아 direct path write temp, direct path read temp 이벤트는 발생하지 않았지만
direct path write 이벤트가 발생했고, Times Waited도 가장 높음
원인을 찾아보기 위해 tkprof 변환전 원시 trc 파일 확인
|
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 /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_31976.trc
.
.
=====================
PARSING IN CURSOR #139790210848392 len=314 dep=1 uid=0 oct=6 lid=0 tim=623926157394 hv=3096556448 ad='689f0600' sqlid='0kkhhb2w93cx0'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #139790210848392:c=36,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2170058777,tim=623926157394
WAIT #139790212811272: nam='PGA memory operation' ela= 6 p1=65536 p2=1 p3=0 obj#=-1 tim=623926157426
BINDS #139790210848392:
.
.
WAIT #140186187303432: nam='direct path write' ela= 146 file number=7 first dba=235652 block cnt=4 obj#=27751 tim=624844187635
WAIT #140186187303432: nam='PGA memory operation' ela= 5 p1=65536 p2=1 p3=0 obj#=27751 tim=624844187674
WAIT #140186187303432: nam='direct path write' ela= 132 file number=7 first dba=235656 block cnt=4 obj#=27751 tim=624844188099
WAIT #140186187303432: nam='PGA memory operation' ela= 4 p1=65536 p2=1 p3=0 obj#=27751 tim=624844188124
WAIT #140186187303432: nam='direct path write' ela= 110 file number=7 first dba=235708 block cnt=4 obj#=27751 tim=624844194426
WAIT #140186187303432: nam='direct path write' ela= 120 file number=7 first dba=235712 block cnt=4 obj#=27751 tim=624844194813
WAIT #140186187303432: nam='direct path read' ela= 229 file number=7 first dba=118272 block cnt=128 obj#=27751 tim=624844195215
WAIT #140186187303432: nam='direct path write' ela= 140 file number=7 first dba=235716 block cnt=4 obj#=27751 tim=624844195520
WAIT #140186187303432: nam='direct path write' ela= 119 file number=7 first dba=235720 block cnt=4 obj#=27751 tim=624844195887
WAIT #140186187303432: nam='direct path write' ela= 142 file number=7 first dba=235724 block cnt=4 obj#=27751 tim=624844196373
WAIT #140186187303432: nam='direct path write' ela= 116 file number=7 first dba=235728 block cnt=4 obj#=27751 tim=624844196738
WAIT #140186187303432: nam='direct path write' ela= 110 file number=7 first dba=235732 block cnt=4 obj#=27751 tim=624844197193
WAIT #140186187303432: nam='direct path write' ela= 172 file number=7 first dba=235736 block cnt=4 obj#=27751 tim=624844197617
WAIT #140186187303432: nam='direct path write' ela= 156 file number=7 first dba=235740 block cnt=4 obj#=27751 tim=624844198094
WAIT #140186187303432: nam='direct path write' ela= 211 file number=7 first dba=235744 block cnt=4 obj#=27751 tim=624844198609
WAIT #140186187303432: nam='direct path write' ela= 142 file number=7 first dba=235748 block cnt=4 obj#=27751 tim=624844199080
WAIT #140186187303432: nam='direct path read' ela= 246 file number=7 first dba=118400 block cnt=128 obj#=27751 tim=624844199499
|
인덱스 생성 과정 중 시스템 뷰인 seg$ 를 update 할때 direct path write, direct path read, PGA memory operation 이벤트가 발생하고 있었음
이 WAIT에 있는 file number=7은 noso 테이블스페이스의 파일 번호임
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
set lines 200 pages 1000
col file_name for a70
select file_id, file_name, tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------------------------------------- ------------------------------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf SYSTEM
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf SYSAUX
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf UNDOTBS1
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf USERS
5 /ORA19/app/oracle/oradata/ORACLE19/imsits01.dbf IMSITS
6 /oradata1/oracle19/users02.dbf USERS
7 /oradata1/oracle19/noso01.dbf NOSO
7 rows selected.
|
이 WAIT에 있는 obj#=27751은 SORT1 테이블의 object_id임
|
1
2
3
4
5
6
|
SQL> col object_name for a20
SQL> select object_id, object_name, object_type from dba_objects where object_id = '27751';
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -----------------------
27751 SORT1 TABLE
|
결론 :
인덱스 생성시 nosort 옵션을 사용한 경우 인덱스 생성을 위한 정렬 작업을 하지않아
direct path write temp, direct path read temp 이벤트가 발생하지 않지만
direct path write, direct path read 이벤트는 여전히 발생함
이는 정렬 작업으로 인한 이벤트가 아닌 인덱스 생성으로 인한 내부 오브젝트 값(seg$) 업데이트 작업에 의해 발생한 이벤트임
참조 :
오라클 19c 인덱스 생성시 nosort 옵션(assm 환경) ( https://positivemh.tistory.com/1015 )
오라클 19c 인덱스 생성시 nosort 옵션(mssm 환경) ( https://positivemh.tistory.com/1016 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__I2129491
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-52FE1A8C-74EA-4B81-B1AC-69FD34252659
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html#GUID-5254F3ED-DDFA-4797-B09C-A49DC62E80B0
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-684E6324-A874-4304-8015-5634199BEE81
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-4AF2D61A-8675-4D48-97A4-B20F401ADA16
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-conventional-and-direct-loads.html#GUID-321928FB-C86C-4F1F-9250-05111A988B7B
https://www.dba-oracle.com/t_create_index_nosort.htm
https://docs.oracle.com/en/error-help/db/ora-01409/
https://www.orafaq.com/wiki/Data_block_address
https://jonathanlewis.wordpress.com/2012/11/27/iot-load/
https://oracleappstechnology.blogspot.com/2008/05/seg-fet-uet.html
TRANSACTION INTERNALS IN ORACLE 10GR2 서적 182p
How to Address High Wait Times for the 'direct path write temp' Wait Event(Doc ID 1576956.1)
Resolving Issues Where 'direct path write' Waits When I/O is Slow (Doc ID 1477235.1)
Resolving Issues Where 'direct path write' Waits When I/O is NOT Slow and Cause is Unknown (Doc ID 1475655.1)
WAITEVENT: "direct path write" Reference Note (Doc ID 50416.1)
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c Prefetch, Batch I/O, Table access by rowid batched 설명 (0) | 2024.02.29 |
|---|---|
| 오라클 19c sqlplus의 statementcache(애플리케이션 커서 캐시) (0) | 2024.02.24 |
| 오라클 19c 하나의 쿼리블록에 힌트 여러개 작성시 나타나는 현상 (0) | 2024.01.21 |
| 오라클 19c 10053 trace 자동수행 스크립트 (0) | 2023.12.14 |
| 오라클 19c 재기동시 발생하는 wait event (0) | 2023.12.08 |