복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 병렬 itas를 이용한 Clustering Factor 개선 테스트
오라클에서 인덱스의 clustering factor를 향상 시키기 위해 테이블을 재생성 한뒤
order by를 포함한 itas를 수행해 데이터를 다시 넣는 경우가 있음(insert into 테이블신규 select * from 테이블원본 방식)
본문에서는 이때 속도를 빠르게 하기 위해 parallel 병렬 기능을 사용하여 clustering factor에 영향이 있는지를 확인해봄
구체적으로 itas중 select 구문에만 parallel을 넣어보기도 하고 insert 구문과 select 구문에 모두 parallel을 넣어보면서 확인해봄
참고로 parallel 작업을 수행하게되면 parallel 할당수 만큼 병렬 프로세스가 생김
그리고 order by등 정렬 작업을 사용한 경우에는 parallel 할당수 * 2만큼의 병렬 프로세스가 생김
이때 데이터가 정렬되어야 하는 경우에는 실행계획에 PX SEND QC (ORDER)와 같이 order이 표시됨
이점을 참고해서 테스트를 확인하면됨
테스트
0. 환경설정
1. No-Parallel ITAS 삽입
2. Parallel ITAS 삽입(읽기, 쓰기 모두 병렬)
3. Parallel ITAS 삽입2(읽기만 병렬, 쓰기는 직렬)
4. 전체 결과 비교 및 분석
테스트
0. 환경설정
샘플 테이블 제거
|
1
2
3
4
5
|
SQL>
drop table table_noparallel purge;
drop table table_parallel purge;
drop table table_parallel2 purge;
drop table sample_base purge;
|
플랜확인을 위해 statistics_level 파라미터 all로 설정
|
1
2
3
|
SQL> alter session set statistics_level=all;
Session altered.
|
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
|
SQL>
create table sample_base (
c1 number,
c2 number,
c3 number,
pad varchar2(100)
)
pctfree 60;
|
샘플 데이터 100만건 삽입
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
insert /*+ append */ into sample_base
select
dbms_random.value(1, 100000) as c1,
dbms_random.value(1, 100000) as c2,
dbms_random.value(1, 100000) as c3,
rpad('x', 100, 'x')
from dual
connect by level <= 1000000;
commit;
|
1. No-Parallel ITAS 삽입
order by를 통해 데이터를 물리적으로 정렬하며 입력해봄
테이블 생성 및 데이터 입력(no parallel)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
create table table_noparallel pctfree 60 as
select * from sample_base where 1=0;
insert /*+ append no_parallel */ into table_noparallel
select * from sample_base order by c1, c2;
select prev_sql_id from v$session
where sid = (select sid from v$mystat where rownum = 1);
commit;
|
prev_sql_id 쿼리 결과는 적지 않았지만 이 insert 구문의 sql_id는 65yb86db9cg1j임
인덱스 생성
|
1
2
3
|
SQL>
create index idx_nopar_c12 on table_noparallel(c1, c2);
create index idx_nopar_c3 on table_noparallel(c3);
|
통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(user, 'table_noparallel', cascade => true);
PL/SQL procedure successfully completed.
|
2. Parallel ITAS 삽입(읽기, 쓰기 모두 병렬)
세션레벨 병렬 dml 활성화
|
1
2
3
|
SQL> alter session enable parallel dml;
Session altered.
|
테이블 생성 및 병렬 데이터 입력(parallel)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
create table table_parallel pctfree 60 as
select * from sample_base where 1=0;
insert /*+ append parallel(t 4) */ into table_parallel t
select /*+ parallel(x 4) */ * from sample_base x order by c1, c2;
select prev_sql_id from v$session
where sid = (select sid from v$mystat where rownum = 1);
commit;
|
prev_sql_id 쿼리 결과는 적지 않았지만 이 insert 구문의 sql_id는 49pyu0wdc5z5h임
인덱스 생성
|
1
2
3
|
SQL>
create index idx_par_c12 on table_parallel(c1, c2);
create index idx_par_c3 on table_parallel(c3);
|
통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(user, 'table_parallel', cascade => true);
PL/SQL procedure successfully completed.
|
3. Parallel ITAS 삽입2(읽기만 병렬, 쓰기는 직렬)
세션레벨 병렬 dml 활성화
|
1
2
3
|
SQL> alter session enable parallel dml;
Session altered.
|
테이블 생성 및 데이터 입력(select만 parallel)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
create table table_parallel2 pctfree 60 as
select * from sample_base where 1=0;
insert /*+ append */ into table_parallel2
select /*+ parallel(x 4) */ * from sample_base x order by c1, c2;
select prev_sql_id from v$session
where sid = (select sid from v$mystat where rownum = 1);
commit;
|
prev_sql_id 쿼리 결과는 적지 않았지만 이 insert 구문의 sql_id는 75va8ajmxkzgv임
인덱스 생성
|
1
2
3
|
SQL>
create index idx_par2_c12 on table_parallel2(c1, c2);
create index idx_par2_c3 on table_parallel2(c3);
|
통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(user, 'table_parallel2', cascade => true);
PL/SQL procedure successfully completed.
|
4. 전체 결과 비교 및 분석
인덱스 통계 비교
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col table_name for a20
col index_name for a20
select dt.table_name, index_name, clustering_factor, di.num_rows, dt.blocks
from dba_indexes di, dba_tables dt
where di.table_name = dt.table_name
and di.table_name in ('TABLE_NOPARALLEL', 'TABLE_PARALLEL', 'TABLE_PARALLEL2')
order by di.table_name;
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR NUM_ROWS BLOCKS
-------------------- -------------------- ----------------- ---------- ----------
TABLE_NOPARALLEL IDX_NOPAR_C12 55556 1000000 55879 <<-- No-Parallel ITAS 삽입
TABLE_NOPARALLEL IDX_NOPAR_C3 999986 1000000 55879
TABLE_PARALLEL IDX_PAR_C12 55557 1000000 55872 <<-- Parallel ITAS 삽입(읽기, 쓰기 모두 병렬)
TABLE_PARALLEL IDX_PAR_C3 999983 1000000 55872
TABLE_PARALLEL2 IDX_PAR2_C12 55556 1000000 55879 <<-- Parallel ITAS 삽입2(읽기만 병렬, 쓰기는 직렬)
TABLE_PARALLEL2 IDX_PAR2_C3 999986 1000000 55879
6 rows selected.
|
일단 세가지 경우 모두 Clustering Factor에 큰 차이가 없음
insert, select 시 모두 parallel을 사용한 경우만 Clustering Factor가 1 높음
세가지 케이스 실행계획 확인
No-Parallel ITAS 삽입시 실행계획
|
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
|
SQL> select * from table(dbms_xplan.display_cursor('65yb86db9cg1j', null, 'advanced allstats last -Projection -qbregistry -rows -bytes' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 65yb86db9cg1j, child number 0
-------------------------------------
insert /*+ append no_parallel */ into table_noparallel select * from
sample_base order by c1, c2
Plan hash value: 3162832671
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 51842 (100)| | 0 |00:00:05.20 | 115K| 77296 | 77296 | | | | |
| 1 | LOAD AS SELECT | TABLE_NOPARALLEL | 1 | | | 0 |00:00:05.20 | 115K| 77296 | 77296 | 2070K| 2070K| 2070K (0)| |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 51842 (1)| 00:00:03 | 1000K|00:00:02.47 | 55676 | 77296 | 21740 | 256K| 256K| | |
| 3 | SORT ORDER BY | | 1 | 51842 (1)| 00:00:03 | 1000K|00:00:01.91 | 55564 | 77296 | 21740 | 191M| 4637K| 102M (1)| 170M|
| 4 | TABLE ACCESS FULL | SAMPLE_BASE | 1 | 15161 (1)| 00:00:01 | 1000K|00:00:00.35 | 55561 | 55556 | 0 | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / SAMPLE_BASE@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "TABLE_NOPARALLEL"@"INS$1")
FULL(@"SEL$1" "SAMPLE_BASE"@"SEL$1")
END_OUTLINE_DATA
*/
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - no_parallel
Note
-----
- Degree of Parallelism is 1 because of hint
50 rows selected.
|
실행계획에서 별다른 특이사항 없음, 모든 작업이 직렬로 실행됨
Parallel ITAS 삽입시 실행계획(읽기, 쓰기 모두 병렬)
|
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
|
SQL> select * from table(dbms_xplan.display_cursor('49pyu0wdc5z5h', null, 'advanced allstats last -Projection -qbregistry -rows -bytes' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 49pyu0wdc5z5h, child number 0
-------------------------------------
insert /*+ append parallel(t 4) */ into table_parallel t select /*+
parallel(x 4) */ * from sample_base x order by c1, c2
Plan hash value: 3569992811
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 14397 (100)| | | | | 8 |00:00:05.74 | 119 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | 8 |00:00:05.74 | 119 | 5 | 214K| 214K| 190K (0)|
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 14397 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TABLE_PARALLEL | 0 | | | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 0 | 10M| 10M| 2070K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 0 | 14397 (1)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 0 | 256K| 256K| 512K (0)|
| 5 | SORT ORDER BY | | 0 | 14397 (1)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 0 | 398M| 6600K| 88M (0)|
| 6 | PX RECEIVE | | 0 | 4207 (1)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 0 | | | |
| 7 | PX SEND RANGE | :TQ10000 | 0 | 4207 (1)| 00:00:01 | Q1,00 | P->P | RANGE | 0 |00:00:00.01 | 0 | 0 | | | |
| 8 | PX BLOCK ITERATOR | | 0 | 4207 (1)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 9 | TABLE ACCESS FULL | SAMPLE_BASE | 0 | 4207 (1)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
9 - SEL$1 / X@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "T"@"INS$1")
FULL(@"SEL$1" "X"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(:Z>=:Z AND :Z<=:Z)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - INS$1 / T@INS$1
- parallel(t 4)
9 - SEL$1 / X@SEL$1
- parallel(x 4)
Note
-----
- Degree of Parallelism is 4 because of table property
63 rows selected.
|
실행계획에서 PX SEND QC (RANDOM), LOAD AS SELECT (HYBRID TSM/HWMB) 플랜이 보임
Parallel ITAS 삽입시 실행계획(읽기만 병렬, 쓰기는 직렬)
|
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
|
SQL> select * from table(dbms_xplan.display_cursor('75va8ajmxkzgv', null, 'advanced allstats last -Projection -qbregistry -rows -bytes' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 75va8ajmxkzgv, child number 0
-------------------------------------
insert /*+ append */ into table_parallel2 select /*+ parallel(x 4) */ *
from sample_base x order by c1, c2
Plan hash value: 745855944
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 14397 (100)| | | | | 0 |00:00:04.63 | 60034 | 4 | 55556 | | | |
| 1 | LOAD AS SELECT | TABLE_PARALLEL2 | 1 | | | | | | 0 |00:00:04.63 | 60034 | 4 | 55556 | 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14397 (1)| 00:00:01 | | | | 1000K|00:00:01.68 | 112 | 4 | 0 | 256K| 256K| |
| 3 | PX COORDINATOR | | 1 | | | | | | 1000K|00:00:01.14 | 5 | 1 | 0 | 214K| 214K| 190K (0)|
| 4 | PX SEND QC (ORDER) | :TQ10001 | 0 | 14397 (1)| 00:00:01 | Q1,01 | P->S | QC (ORDER) | 0 |00:00:00.01 | 0 | 0 | 0 | | | |
| 5 | SORT ORDER BY | | 0 | 14397 (1)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 0 | 0 | 209M| 4848K| 46M (0)|
| 6 | PX RECEIVE | | 0 | 4207 (1)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 0 | 0 | | | |
| 7 | PX SEND RANGE | :TQ10000 | 0 | 4207 (1)| 00:00:01 | Q1,00 | P->P | RANGE | 0 |00:00:00.01 | 0 | 0 | 0 | | | |
| 8 | PX BLOCK ITERATOR | | 0 | 4207 (1)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | 0 | 0 | | | |
|* 9 | TABLE ACCESS FULL | SAMPLE_BASE | 0 | 4207 (1)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
9 - SEL$1 / X@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "TABLE_PARALLEL2"@"INS$1")
FULL(@"SEL$1" "X"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(:Z>=:Z AND :Z<=:Z)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
9 - SEL$1 / X@SEL$1
- parallel(x 4)
Note
-----
- Degree of Parallelism is 4 because of table property
- PDML disabled because object is not decorated with parallel clause
61 rows selected.
|
실행계획에서 PX SEND QC (ORDER) 플랜이 보임
rowid 순으로 정렬하여 c1 값 확인
|
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
|
SQL>
set lines 200 pages 1000
select
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
rowid,
c1
from table_noparallel
order by rowid;
REL_FNO BLOCKNO ROWID C1
---------- ---------- ------------------ ----------
...
4 114877 AAAMReAAEAAAcC9AAM 11850.2128
4 114877 AAAMReAAEAAAcC9AAN 11850.2761
4 114877 AAAMReAAEAAAcC9AAO 11850.2793
4 114877 AAAMReAAEAAAcC9AAP 11850.4207
4 114877 AAAMReAAEAAAcC9AAQ 11850.4823
4 114877 AAAMReAAEAAAcC9AAR 11850.5259 <<-- 정렬 유지
4 114878 AAAMReAAEAAAcC+AAA 11850.5493 <<-- 정렬 유지
4 114878 AAAMReAAEAAAcC+AAB 11850.5659
4 114878 AAAMReAAEAAAcC+AAC 11850.5674
4 114878 AAAMReAAEAAAcC+AAD 11850.5973
4 114878 AAAMReAAEAAAcC+AAE 11850.6317
...
SQL>
set lines 200 pages 1000
select
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
rowid,
c1
from table_parallel2
order by rowid;
REL_FNO BLOCKNO ROWID C1
---------- ---------- ------------------ ----------
...
4 248617 AAAMRkAAEAAA8spAAN 3233.87902
4 248617 AAAMRkAAEAAA8spAAO 3233.99063
4 248617 AAAMRkAAEAAA8spAAP 3234.05061
4 248617 AAAMRkAAEAAA8spAAQ 3234.06362
4 248617 AAAMRkAAEAAA8spAAR 3234.21536 <<-- 정렬 유지
4 248618 AAAMRkAAEAAA8sqAAA 3234.24367 <<-- 정렬 유지
4 248618 AAAMRkAAEAAA8sqAAB 3234.32654
4 248618 AAAMRkAAEAAA8sqAAC 3234.32802
4 248618 AAAMRkAAEAAA8sqAAD 3234.36388
4 248618 AAAMRkAAEAAA8sqAAE 3234.42606
...
|
rowid 순으로 확인했을 때 parallel을 사용하지 않은 경우(1번 케이스 table_noparallel)와
select 시에만 parallel을 사용한 경우(3번 케이스 table_parallel2)는 c1 값이 순서대로 정렬되어 있었음
rowid 순으로 정렬하여 c1 값 확인(select와 insert시 모두 parallel을 사용한 경우)
|
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
|
SQL>
set lines 200 pages 1000
select
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
rowid,
c1
from table_parallel
order by rowid;
REL_FNO BLOCKNO ROWID C1
---------- ---------- ------------------ ----------
...
4 178403 AAALbSAAGAAArjjAAJ 22564.3538
4 178403 AAALbSAAGAAArjjAAK 22564.5958
4 178403 AAALbSAAGAAArjjAAL 22564.6543
4 178403 AAALbSAAGAAArjjAAM 22564.8332
4 178403 AAALbSAAGAAArjjAAN 22564.8456
4 178403 AAALbSAAGAAArjjAAO 22564.98
4 178403 AAALbSAAGAAArjjAAP 22565.2553
4 178403 AAALbSAAGAAArjjAAQ 22565.4675
4 178403 AAALbSAAGAAArjjAAR 22565.4941 <<-- 정렬 미유지
4 178404 AAALbSAAGAAArjkAAA 96023.7804 <<-- 정렬 미유지
4 178404 AAALbSAAGAAArjkAAB 96023.8758
4 178404 AAALbSAAGAAArjkAAC 96024.1353
4 178404 AAALbSAAGAAArjkAAD 96024.3547
4 178404 AAALbSAAGAAArjkAAE 96024.3653
4 178404 AAALbSAAGAAArjkAAF 96024.3997
...
|
select와 insert시 모두 parallel을 사용한 경우(2번 케이스)는 위 결과처럼
하나의 블록 단위 내에서는 c1 값이 정렬 되어 있었지만 c1 값이 전체적으로는 정렬되어 있지 않았음
아래와 같이 178403 블록에서는 2만대의 값(22565)이 나오다가 178404 블록에서는 9만대의 값(96023)이 나옴
이것만 봤을때는 아직까진 이 정렬이 블록 단위 정렬인지 익스텐트 단위 정렬이 되는것인지 알수 없음
TABLE_PARALLEL 테이블이 사용중인 익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col owner for a10
col segment_name for a20
SELECT
owner,
segment_name,
extent_id, -- 익스텐트 번호
block_id, -- 익스텐트의 시작 블록
blocks -- 익스텐트 내 블록 수
FROM dba_extents
WHERE segment_name = 'TABLE_PARALLEL';
OWNER SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
---------- -------------------- ---------- ---------- ----------
...
IMSI TABLE_PARALLEL 106 176640 1024
IMSI TABLE_PARALLEL 107 177664 1024 <<-- !!
IMSI TABLE_PARALLEL 108 178688 1024
..
|
위에서 확인한 블록(178403, 178404)들은 모두 107 익스텐트에 들어가 있음
177664 블록부터 178687 블록까지 1024개의 블록이 107 익스텐트에 속함
이 말은 익스텐트간 row 정렬이 되어있는것이 아닌 블록내에서만 정렬이 이루어 졌다는것을 의미함
사실 실행계획에서도 그 내용을 확인할 수 있었음
id 8을 보면 PX BLOCK ITERATOR 으로 표시됨
이는 데이터를 여러 개의 조각(블록 단위)으로 나눈 뒤 여러 병렬 서버 프로세스가 각 조각을 반복(Iterator)하며 나누어 처리하는 작업 방식임
3번 케이스 실행계획(Parallel ITAS 삽입2(읽기만 병렬, 쓰기는 직렬))
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | LOAD AS SELECT | TABLE_PARALLEL2 |
| 2 | OPTIMIZER STATISTICS GATHERING | |
| 3 | PX COORDINATOR | |
| 4 | PX SEND QC (ORDER) | :TQ10001 | <<--!! ORDER
| 5 | SORT ORDER BY | |
| 6 | PX RECEIVE | |
| 7 | PX SEND RANGE | :TQ10000 |
| 8 | PX BLOCK ITERATOR | | <<--!!
|* 9 | TABLE ACCESS FULL | SAMPLE_BASE |
------------------------------------------------------------
|
2번 케이스 실행계획(Parallel ITAS 삽입(읽기, 쓰기 모두 병렬))
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | <<--!! RANDOM
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TABLE_PARALLEL |
| 4 | OPTIMIZER STATISTICS GATHERING | |
| 5 | SORT ORDER BY | |
| 6 | PX RECEIVE | |
| 7 | PX SEND RANGE | :TQ10000 |
| 8 | PX BLOCK ITERATOR | | <<--!!
|* 9 | TABLE ACCESS FULL | SAMPLE_BASE |
-------------------------------------------------------------
|
그리고 일반적으로 병렬입력에서 값이 정렬되어서 들어가는 경우 아래와 같이 PX SEND QC (ORDER)로 표시됨(3번 케이스)
하지만 2번 케이스의 경우 PX SEND QC가 RANDOM으로 표시가 되었음
이는 Parallel Insert 시 각 병렬 슬레이브가 독립적으로 데이터를 Write하기 때문임
PX BLOCK ITERATOR를 통해 분배된 데이터가 각 슬레이브 내에서 SORT ORDER BY를 거쳐 블록 단위의 정렬은 보장하지만
여러 슬레이브가 동시에 Write를 수행하는 병렬 부하의 특성상 테이블 전체의 물리적 Rowid 순서와 정렬 순서가 일치하지 않는 현상이 발생한것
참고1. Parallel Read + Parallel Write 데이터 흐름
|
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
|
1 단계: 디스크에서 블록 읽기 & 분배
[ 데이터베이스 디스크 (SAMPLE_BASE 테이블) ]
| (PX BLOCK ITERATOR 단위 분배)
v
+-----------+ +-----------+ +-----------+ +-----------+
| PX Slave1 | | PX Slave2 | | PX Slave3 | | PX Slave4 | <-- [Producer Group]
+-----------+ +-----------+ +-----------+ +-----------+
| | | |
+-------------+-------------+-------------+
|
| 2 단계: Range 분배 및 정렬 (Inter-Operation Parallelism)
| (값의 범위에 따라 다른 슬레이브로 셔플링)
v
+-----------+ +-----------+ +-----------+ +-----------+
| PX Slave5 | | PX Slave6 | | PX Slave7 | | PX Slave8 | <-- [Consumer Group]
+-----------+ +-----------+ +-----------+ +-----------+
(Sort 수행) (Sort 수행) (Sort 수행) (Sort 수행)
| | | |
| (독립 Write)| (독립 Write)| (독립 Write)| (독립 Write)
v v v v
[ Block A ] [ Block B ] [ Block C ] [ Block D ] <-- (Direct Path Insert)
\ \ / /
\ \ / /
v v v v
[ TABLE_PARALLEL 테이블 (물리적 정렬 꼬임 발생!) ]
|
| 3 단계: 작업 완료 보고 (PX SEND QC RANDOM)
v
[ Query Coordinator (QC) ] <-- (세션 프로세스)
|
참고2. Parallel Read + Serial Write 데이터 흐름
|
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
|
1 단계: 디스크에서 블록 읽기 & 분배
[ 데이터베이스 디스크 (SAMPLE_BASE 테이블) ]
| (PX BLOCK ITERATOR 단위 분배)
v
+-----------+ +-----------+ +-----------+ +-----------+
| PX Slave1 | | PX Slave2 | | PX Slave3 | | PX Slave4 | <-- [Producer Group]
+-----------+ +-----------+ +-----------+ +-----------+
| | | |
+-------------+-------------+-------------+
|
| 2 단계: Range 분배 및 정렬 (Inter-Operation Parallelism)
| (값의 범위에 따라 다른 슬레이브로 셔플링)
v
+-----------+ +-----------+ +-----------+ +-----------+
| PX Slave5 | | PX Slave6 | | PX Slave7 | | PX Slave8 | <-- [Consumer Group]
+-----------+ +-----------+ +-----------+ +-----------+
(Sort 수행) (Sort 수행) (Sort 수행) (Sort 수행)
| | | |
+-------------+-------------+-------------+
|
| 3 단계: 정렬된 순서대로 QC에게 전송 (PX SEND QC ORDER)
v
[ Query Coordinator (QC) ] <-- (세션 프로세스)
|
| 4 단계: 단일 프로세스가 순차적으로 Write (직렬 삽입)
v
[ Block A -> Block B -> Block C -> Block D ... ]
|
v
[ TABLE_PARALLEL2 테이블 (물리적 정렬 보장) ]
|
결론 :
Clustering Factor 개선을 위한 ITAS 작업 시 속도를 위해 Parallel Insert를 사용해보았음
이때 parallel을 미사용한 경우, select시에만 parallel을 사용한 경우, insert, select 모두에 parallel을 사용한 세가지 경우 모두 Clustering Factor는 비슷했음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col table_name for a20
col index_name for a20
select dt.table_name, index_name, clustering_factor, di.num_rows, dt.blocks
from dba_indexes di, dba_tables dt
where di.table_name = dt.table_name
and di.table_name in ('TABLE_NOPARALLEL', 'TABLE_PARALLEL', 'TABLE_PARALLEL2')
order by di.table_name;
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR NUM_ROWS BLOCKS
-------------------- -------------------- ----------------- ---------- ----------
TABLE_NOPARALLEL IDX_NOPAR_C12 55556 1000000 55879 <<-- No-Parallel ITAS 삽입
TABLE_NOPARALLEL IDX_NOPAR_C3 999986 1000000 55879
TABLE_PARALLEL IDX_PAR_C12 55557 1000000 55872 <<-- Parallel ITAS 삽입(읽기, 쓰기 모두 병렬)
TABLE_PARALLEL IDX_PAR_C3 999983 1000000 55872
TABLE_PARALLEL2 IDX_PAR2_C12 55556 1000000 55879 <<-- Parallel ITAS 삽입2(읽기만 병렬, 쓰기는 직렬)
TABLE_PARALLEL2 IDX_PAR2_C3 999986 1000000 55879
6 rows selected.
|
parallel을 미사용한 경우, select시에만 parallel을 사용한 경우에는 테이블 전체의 물리적 순서 정렬이 되어있지만,
insert, select 모두에 parallel을 사용한 경우에는 테이블 전체의 물리적 순서 정렬이 되어있지 않음
이는 Parallel Insert 시 각 병렬 슬레이브가 독립적으로 데이터를 Write하기 때문임
PX BLOCK ITERATOR를 통해 분배된 데이터가 각 슬레이브 내에서 SORT ORDER BY를 거쳐 블록 단위의 정렬은 보장하지만
여러 슬레이브가 동시에 Write를 수행하는 병렬 부하의 특성상 테이블 전체의 물리적 Rowid 순서와 정렬 순서가 일치하지 않는 현상이 발생한것임
결과는 이렇지만 CF만 본다면 CF 개선을 위해 굳이 select에는 parallel을 사용하고 insert에는 serial로 하는 방식은 사용하지 않아도 될듯함
select, insert 모두에 parallel을 사용하면 될듯함
그리고 추가로 이 방식을 사용하여 테이블을 재생성하면 정렬 대상이 되는 인덱스만 클라스터링 펙터가 좋아지고 나머지 인덱스는 더 나빠질 수 있음
참조 :
오라클 19c 인덱스 생성시 nosort 옵션(assm 환경) ( https://positivemh.tistory.com/1015 )
오라클 19c 인덱스 생성시 nosort 옵션(mssm 환경) ( https://positivemh.tistory.com/1016 )
오라클 19c 인덱스 생성시 nosort 옵션 대기 이벤트 확인 ( https://positivemh.tistory.com/1023 )
'ORACLE > Performance Tuning' 카테고리의 다른 글
| 오라클 19c stale percent 변경 및 자동통계수집 (0) | 2026.06.22 |
|---|---|
| 오라클 19c 펜딩 통계(Pending Statistics) (0) | 2026.06.18 |
| 오라클 19c AWR 스냅샷의 플랜을 SPM(SQL Plan Management)으로 고정 (0) | 2026.05.24 |
| 오라클 19c SQL Profile 등록된 플랜 SPM으로 변경 자동화 스크립트 (0) | 2026.05.20 |
| 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 (0) | 2026.05.18 |