내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 인덱스 생성시 nosort 옵션(mssm 환경)
이전 게시글에서 assm 환경에서 인덱스 생성시 nosort 옵션을 사용하여 테스트 해보았음
assm 환경에서는 익스텐트 내 블록을 할당 받을때 순차적으로 받지 않아 nosort 옵션을 제대로 사용할수 없었음
하지만 mssm 환경에선 익스텐트 내 블록을 순차적으로 할당받아 대량 데이터로도 테스트가 가능하였음
참고 : 오라클 19c 인덱스 생성시 nosort 옵션(assm 환경) ( https://positivemh.tistory.com/1015 )
nosort 옵션 설명
이 옵션은 인덱스 생성 시 정렬작업을 생략하는 명령어임(인덱스 생성시 기본값은 sort 이고 nosort 가 옵션임)
기본적으로 인덱스는 정렬된 구조로 되어있는데 어떻게 정렬을 생략하면서 인덱스를 만들수 있는지 의문이 들수있음
이 옵션은 무조건 정렬를 하지 않는것이 아닌 인덱스를 만드려는 컬럼이 정렬된 상태로 존재 할 때 정렬을 생략하는 옵션임
실제로 정렬이 된 상태로 nosort 옵션과 함께 인덱스를 만들면 인덱스가 정상적으로 생성이 되고
정렬이 되지 않은 상태로 nosort 옵션과 함께 인덱스를 만들면 ORA-01409 에러가 발생함
그리고 블록의 순서대로(rowid를 결정함) 데이터가 들어가 있지 않으면 nosort 옵션 사용시 에러가 발생함
그리고 여러 익스텐트에 걸쳐 데이터가 존재하는 경우 두 번째 익스텐트의 첫 번째 블록의 dba(data block address)가
첫 번째 익스텐트의 마지막 dba(data block address)보다 작은 경우에도 nosort 옵션 사용시 에러가 발생함
nosort 옵션 제약조건 정리
- REVERSE 옵션과 같이 사용불가
- 파티션 클러스터 인덱스 또는 비트맵 인덱스를 생성 불가
- IOT 테이블에 secondary index로 사용불가
- 테이블 데이터가 정렬(rowid 순으로)이 되어있지 않으면 사용 불가
- 여러 익스텐트에 걸쳐 데이터가 존재하는 경우 두 번째 익스텐트의 첫 번째 블록의 dba가 첫 번째 익스텐트의 마지막 dba보다 작은 경우 사용 불가
이렇게 제약조건이 많아서 실제 업무에선 사용하기 어려울수 있지만
이 제약조건을 피해서 인덱스를 생성 하는 경우 정렬하는 시간을 아낄수 있어 시간을 절약할 수 있음
이 게시글에선 mssm 환경에서 nosort 옵션을 이용해 인덱스를 생성 했을시 시간이 얼마나 절약 되는지와 플랜 등을 확인해봄
테스트1. 일반 insert 구문으로 삽입된 소량 데이터
테스트2. ctas 구문으로 생성된 데이터
초기 환경 구성
샘플 테이블스페이스 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop tablespace noso2 including contents and datafiles;
create tablespace noso2 datafile '/ORA19/app/oracle/oradata/ORACLE19/noso02.dbf' size 100m autoextend on segment space management manual;
Tablespace created.
SQL> alter user imsi quota unlimited on noso2;
User altered.
|
테스트1. 일반 insert 구문으로 삽입된 소량 데이터
샘플 테이블 생성
|
1
2
3
4
5
|
SQL>
drop table sort2 purge;
create table sort2 (col1 number) tablespace noso2;
Table created.
|
sort2 테이블에 100000개 row 입력
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
DECLARE
i NUMBER := 1;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO sort2 (col1) VALUES (i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
extent, block 확인
|
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
|
SQL>
select extent_id, file_id, block_id, blocks, bytes
from dba_extents
where segment_name = 'SORT2';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ---------- ----------
0 8 128 8 65536
1 8 136 8 65536
2 8 144 8 65536
3 8 152 8 65536
4 8 160 8 65536
5 8 168 8 65536
6 8 176 8 65536
7 8 184 8 65536
8 8 192 8 65536
9 8 200 8 65536
10 8 208 8 65536
11 8 216 8 65536
12 8 224 8 65536
13 8 232 8 65536
14 8 240 8 65536
15 8 248 8 65536
16 8 256 128 1048576
17 rows selected.
|
17개 extent가 존재함
블록 헤더 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col segment_name for a10
select segment_name, header_file, header_block, blocks
from dba_segments
where segment_name = 'SORT2';
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS
---------- ----------- ------------ ----------
SORT2 8 128 256
|
sort2 테이블에 인덱스 생성(nosort)
|
1
2
3
|
SQL> create index sort2_ix02 on sort2(col1) nosort;
Index created.
|
정상적으로 생성됨
sort2 테이블의 각 row별 file_id, block_id, rowid 확인(rowid 순으로 정렬해서 확인)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set lines 200 pages 1000
select
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
rowid,
col1
from sort2
order by rowid;
REL_FNO BLOCKNO ROWID COL1
---------- ---------- ------------------ ----------
8 129 AAAGQJAAIAAAACBAAA 1
8 129 AAAGQJAAIAAAACBAAB 2
8 129 AAAGQJAAIAAAACBAAC 3
8 129 AAAGQJAAIAAAACBAAD 4
.
.
8 280 AAAGQJAAIAAAAEYAFQ 99997
8 280 AAAGQJAAIAAAAEYAFR 99998
8 280 AAAGQJAAIAAAAEYAFS 99999
8 280 AAAGQJAAIAAAAEYAFT 100000
100000 rows selected.
|
col1 컬럼과 rowid가 순서대로 정렬되어있음
sort2 테이블 블록별 row 갯수 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
select
dbms_rowid.rowid_block_number(rowid) blockno,
count(rowid)
from sort2
group by dbms_rowid.rowid_block_number(rowid)
order by 1;
BLOCKNO COUNT(ROWID)
---------- ------------
129 660
130 660
131 660
.
.
278 660
279 660
280 340
152 rows selected.
|
하나의 블록당 660개 row가 들어감
이렇게 mssm 방식을 사용했을때는 블록이 순서대로 할당 받아져서 데이터를 넣는 순서대로 블록을 사용하게됨
그렇기 때문에 이 상태에서는 nosort 옵션을 이용해서 인덱스를 생성할 수 있음
테스트2. ctas 구문으로 생성된 데이터
이번엔 dba_objects에서 데이터를 가져와 sample 테이블을 만들고
이 테이블을 ctas를 이용해 정렬하면서 대량 데이터를 인덱스 생성용 테이블에 삽입한뒤 nosort 옵션으로 인덱스를 생성해봄
샘플 테이블 생성
|
1
2
3
4
|
SQL>
drop table sample purge;
create table sample tablespace noso2 as
select * from dba_objects;
|
샘플 데이터 삽입(assm 테스트보다 더 많이 삽입)
|
1
2
3
4
5
|
SQL>
insert /*+ append parallel */ into sample select /*+ parallel */ * from sample;
commit;
(반복)
5908736 rows created.
|
sort3 테이블 생성(object_id 순으로 정렬)
|
1
2
3
|
SQL>
drop table sort3 purge;
create table sort3 tablespace noso2 as select * from sample order by object_id;
|
trace 실행 후 인덱스 생성
|
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 sort3_ix01 on sort3(object_id);
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_7082.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_7082.trc m_sort3_01.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Mon Jan 29 16:33:59 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
|
$ vi m_sort3_01.txt
SQL ID: bx0wu5kbucyfn Plan Hash: 1700858464
create index sort3_ix01 on sort3(object_id)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 3 4 5 0
Execute 1 7.48 9.41 231278 196745 29337 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.49 9.42 231281 196749 29342 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD NON UNIQUE SORT3_IX01 (cr=196754 pr=231281 pw=60506 time=9424094 us starts=1)(object id 0)
11817472 11817472 11817472 SORT CREATE INDEX (cr=196471 pr=231277 pw=34812 time=5845399 us starts=1)
11817472 11817472 11817472 TABLE ACCESS FULL SORT3 (cr=196471 pr=196465 pw=0 time=894426 us starts=1 cost=53467 size=59087360 card=11817472)
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
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00
PGA memory operation 208 0.00 0.00
direct path read 3070 0.01 1.75
direct path write temp 1360 0.01 0.65
direct path read temp 1361 0.00 0.14
db file sequential read 4 0.00 0.00
Disk file operations I/O 1 0.00 0.00
direct path write 6584 0.00 1.47
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 8.16 8.16
********************************************************************************
|
인덱스 생성 중 sort를 한것이 확인됨(Row Source Operation 의 SORT CREATE INDEX 부분)
인덱스 생성시 시간이 9424094 us(9.4초) 소요됨
sqlplus 세션 재접속 후 진행
테스트를 위해 기존 인덱스 삭제
|
1
2
3
|
SQL> drop index sort3_ix01;
Index dropped.
|
trace 실행 후 인덱스 생성(nosort)
|
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 sort3_ix02 on sort3(object_id) 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_7141.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_7141.trc m_sort3_02.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Mon Jan 29 16:35:46 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
|
$ vi m_sort3_02.txt
SQL ID: 7vuk0vrjhvn36 Plan Hash: 4070344199
create index sort3_ix02 on sort3(object_id) nosort
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 5.23 7.17 196465 196747 29328 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.23 7.17 196465 196748 29328 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD NON UNIQUE SORT3_IX02 (cr=196757 pr=196465 pw=25694 time=7174745 us starts=1)(object id 0)
11817472 11817472 11817472 TABLE ACCESS FULL SORT3 (cr=196471 pr=196465 pw=0 time=1590466 us starts=1 cost=53467 size=59087360 card=11817472)
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
PGA memory operation 14 0.00 0.00
Disk file operations I/O 1 0.00 0.00
direct path read 3070 0.01 1.85
direct path write 6584 0.01 1.96
log file switch completion 1 0.00 0.00
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.07 1.07
********************************************************************************
|
기존 인덱스 생성 트레이스에 있던 SORT CREATE INDEX 부분이 없음
인덱스 생성시 시간이 7174745 us(7.1초) 소요됨
결론 :
mssm을 사용했을때는 블록 할당이 순서대로 할당되어 대량 데이터로도 nosort 옵션으로 인덱스를 생성하는 테스트가 가능했음
기존 인덱스 생성 시 9.4초 걸리던것에 비해 nosort 옵션 사용시 7.1초로 2초정도 빠르게 수행됨
하지만 일반적으로는 대부분이 assm 방식을 사용하기 때문에 제대로 사용을 하지 못할 가능성이 높지만
만약 의도적으로 mssm을 사용하는 곳이 있다면 nosort 옵션을 통해 인덱스 생성시 시간을 절약할 수 있음
참조 :
오라클 19c 인덱스 생성시 nosort 옵션(mssm 환경) ( https://positivemh.tistory.com/1015 )
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://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/
TRANSACTION INTERNALS IN ORACLE 10GR2 서적 182p
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 Edelivery 소프트웨어 다운로드 사이트 사용 가이드 (0) | 2024.03.02 |
|---|---|
| 오라클 19c VS Code에서 실시간 SQL 모니터링 (0) | 2024.02.18 |
| 오라클 19c 인덱스 생성시 nosort 옵션(assm 환경) (0) | 2024.02.02 |
| 오라클 19c AWR 비교 리포트 수집 방법 (0) | 2024.01.30 |
| 오라클 19c AWR 란 (0) | 2024.01.30 |