OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
방법 : 오라클 19c itas 시 full scan과 index scan 속도 비교 테스트
19c 환경에서 샘플 테이블 생성후
itas(insert into select * from table) 로 데이터 삽입시
full scan으로 읽어서 넣는게 빠른지, index scan으로 읽어서 넣는게 빠른지 테스트해봄
1. OPTIMIZER STATISTICS GATHERING 기능을 ON(default) 상태로 테스트
2. OPTIMIZER STATISTICS GATHERING 기능을 OFF 상태로 테스트
3. OPTIMIZER STATISTICS GATHERING 기능을 OFF + PARALLEL 테스트
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
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)
)
nologging;
|
샘플 테이블 데이터 삽입
|
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
|
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(2010,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;
/
|
샘플 테이블 인덱스 생성
|
1
2
3
|
SQL> create index ix_sam1 on sample_t(id1);
Index created.
|
통계정보 생성
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats('IMSI', 'SAMPLE_T', cascade=>TRUE);
PL/SQL procedure successfully completed.
|
샘플테이블 건수 확인
|
1
2
3
4
5
|
SQL> select count(*) from sample_t;
COUNT(*)
----------
10000000
|
1천만건이 삽입됨
샘플 테이블 용량 조회
|
1
2
3
4
5
6
7
8
|
SQL>
select segment_name, bytes/1024/1024 mb
from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NAME MB
------------- ----------
SAMPLE_T 927
|
약 920MB임
1. OPTIMIZER STATISTICS GATHERING 기능을 ON(default) 상태로 테스트
테스트1. 전체 데이터로 테이블 생성(FULL SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
drop table itable_1 purge;
create table itable_1 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_1
select /*+ full(st) */ * from sample_t st where id1 >= 1;
10000000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 32351 (100)| | 0 |00:00:07.91 | 308K| 117K| 117K| | | |
| 1 | LOAD AS SELECT | ITABLE_1 | 1 | | | | | 0 |00:00:07.91 | 308K| 117K| 117K| 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 10M| 762M| 32351 (2)| 00:00:02 | 10M|00:00:06.55 | 184K| 117K| 0 | 256K| 256K| |
|* 3 | TABLE ACCESS FULL | SAMPLE_T | 1 | 10M| 762M| 32351 (2)| 00:00:02 | 10M|00:00:02.00 | 184K| 117K| 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
전체 데이터 삽입 시 full scan을 이용했을 때 cost가 32351이 나오고 시간이 7초 소요됨
테스트2. 전체 데이터로 테이블 생성(INDEX SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
drop table itable_2 purge;
create table itable_2 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_2
select /*+ index(st ix_sam1) */ * from sample_t st where id1 >= 1;
10000000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 10M(100)| | 0 |00:00:14.74 | 9863K| 139K| 117K| | | |
| 1 | LOAD AS SELECT | ITABLE_2 | 1 | | | | | 0 |00:00:14.74 | 9863K| 139K| 117K| 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 10M| 762M| 10M (1)| 00:06:32 | 10M|00:00:12.67 | 9738K| 139K| 0 | 256K| 256K| |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_T | 1 | 10M| 762M| 10M (1)| 00:06:32 | 10M|00:00:06.99 | 9738K| 139K| 0 | | | |
|* 4 | INDEX RANGE SCAN | IX_SAM1 | 1 | 10M| | 22153 (1)| 00:00:01 | 10M|00:00:01.21 | 22287 | 22286 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
전체 데이터 삽입 시 index scan을 이용했을 때 cost가 10M이 나오고 시간이 14초 소요됨
테스트3. 일부 데이터로 테이블 생성(FULL SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
drop table itable_3 purge;
create table itable_3 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_3
select /*+ full(st) */ * from sample_t st where id1 <= 10000;
100000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 32208 (100)| | 0 |00:00:00.89 | 119K| 117K| 1149 | | | |
| 1 | LOAD AS SELECT | ITABLE_3 | 1 | | | | | 0 |00:00:00.89 | 119K| 117K| 1149 | 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 100K| 7812K| 32208 (1)| 00:00:02 | 100K|00:00:03.30 | 117K| 117K| 0 | 256K| 256K| |
|* 3 | TABLE ACCESS FULL | SAMPLE_T | 1 | 100K| 7812K| 32208 (1)| 00:00:02 | 100K|00:00:03.21 | 117K| 117K| 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
일부 데이터 삽입 시 full scan을 이용했을 때 cost가 32208이 나오고 시간이 0초 89 소요됨
테스트4. 일부 데이터로 테이블 생성(INDEX SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
drop table itable_4 purge;
create table itable_4 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_4
select /*+ index(st ix_sam1) */ * from sample_t st where id1 <= 10000;
100000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 100K(100)| | 0 |00:00:00.17 | 100K| 744 | 1148 | | | |
| 1 | LOAD AS SELECT | ITABLE_4 | 1 | | | | | 0 |00:00:00.17 | 100K| 744 | 1148 | 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 100K| 7812K| 100K (1)| 00:00:04 | 100K|00:00:00.14 | 98820 | 744 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_T | 1 | 100K| 7812K| 100K (1)| 00:00:04 | 100K|00:00:00.06 | 98612 | 744 | 0 | | | |
|* 4 | INDEX RANGE SCAN | IX_SAM1 | 1 | 100K| | 224 (1)| 00:00:01 | 100K|00:00:00.02 | 211 | 209 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
일부 데이터 삽입 시 index scan을 이용했을 때 cost가 100K가 나오고 시간이 0초 17 소요됨
4개 테스트 모두 실행계획에서 공통적으로 OPTIMIZER STATISTICS GATHERING 라는 실행계획이 나옴
이는 벌크 인서트(append등) 시 통계정보를 생성해주는 오라클의 기능인데 이 기능때문에 속도가 조금 더 느려짐
|
1
2
3
4
5
6
7
|
SQL> select table_name, last_analyzed from dba_tables where table_name like 'ITABLE_%';
TABLE_NAME LAST_ANALYZE
----------- ------------
ITABLE_1 13-APR-22
ITABLE_2 13-APR-22
ITABLE_3 13-APR-22
ITABLE_4 13-APR-22
|
OPTIMIZER STATISTICS GATHERING이 활성화 된 상태에서 결론
전체 데이터 삽입
속도 : FULLSCAN : 7초, INDEXSCAN : 14초
COST : FULLSCAN : 32351, INDEXSCAN : 10M
일부 데이터 삽입
속도 : FULLSCAN : 0초 89, INDEXSCAN : 0초 17
COST : FULLSCAN : 32208, INDEXSCAN : 100K
전체 데이터 삽입시에는 FULLSCAN으로 삽입하는게 낫고
일부 데이터 삽입시에는 INDEXSCAN으로 삽입하는게 근소하게 빠름
(데이터 읽는량(인덱스 손익분기점)에 따라 차이가 날듯함)
COST는 오히려 INDEXSCAN시가 더 높게 나옴
2. OPTIMIZER STATISTICS GATHERING 기능을 OFF 상태로 테스트
OPTIMIZER STATISTICS GATHERING 기능을 OFF 한뒤 다시 테스트
|
1
2
3
4
5
|
파라미터 변경
SQL> alter session set "_optimizer_gather_stats_on_load" = false;
또는
SQL 마다 힌트 삽입
SQL> select /*+ no_gather_optimizer_statistics */ from table;
|
테스트1. 전체 데이터로 테이블 생성(FULL SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
drop table itable_1 purge;
create table itable_1 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_1
select /*+ full(st) */ * from sample_t st where id1 >= 1;
10000000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 32351 (100)| | 0 |00:00:03.96 | 241K| 117K| 117K| | | |
| 1 | LOAD AS SELECT | ITABLE_1 | 1 | | | | | 0 |00:00:03.96 | 241K| 117K| 117K| 2070K| 2070K| 2070K (0)|
|* 2 | TABLE ACCESS FULL| SAMPLE_T | 1 | 10M| 762M| 32351 (2)| 00:00:02 | 10M|00:00:01.98 | 117K| 117K| 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
전체 데이터 삽입 시 full scan을 이용했을 때 cost가 32351이 나오고 시간이 3초 소요됨
테스트2. 전체 데이터로 테이블 생성(INDEX SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
drop table itable_2 purge;
create table itable_2 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_2
select /*+ index(st ix_sam1) */ * from sample_t st where id1 >= 1;
10000000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 10M(100)| | 0 |00:00:08.00 | 9797K| 138K| 117K| | | |
| 1 | LOAD AS SELECT | ITABLE_2 | 1 | | | | | 0 |00:00:08.00 | 9797K| 138K| 117K| 2070K| 2070K| 2070K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_T | 1 | 10M| 762M| 10M (1)| 00:06:32 | 10M|00:00:05.69 | 9673K| 138K| 0 | | | |
|* 3 | INDEX RANGE SCAN | IX_SAM1 | 1 | 10M| | 22153 (1)| 00:00:01 | 10M|00:00:01.10 | 22287 | 22076 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
전체 데이터 삽입 시 index scan을 이용했을 때 cost가 10M이 나오고 시간이 8초 소요됨
테스트3. 일부 데이터로 테이블 생성(FULL SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
drop table itable_3 purge;
create table itable_3 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_3
select /*+ full(st) */ * from sample_t st where id1 <= 10000;
100000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 32208 (100)| | 0 |00:00:00.25 | 119K| 117K| 1149 | | | |
| 1 | LOAD AS SELECT | ITABLE_3 | 1 | | | | | 0 |00:00:00.25 | 119K| 117K| 1149 | 2070K| 2070K| 2070K (0)|
|* 2 | TABLE ACCESS FULL| SAMPLE_T | 1 | 100K| 7812K| 32208 (1)| 00:00:02 | 100K|00:00:02.42 | 117K| 117K| 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
일부 데이터 삽입 시 full scan을 이용했을 때 cost가 32208이 나오고 시간이 0초 25 소요됨
테스트4. 일부 데이터로 테이블 생성(INDEX SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
drop table itable_4 purge;
create table itable_4 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append */ into itable_4
select /*+ index(st ix_sam1) */ * from sample_t st where id1 <= 10000;
100000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Writes | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 100K(100)| | 0 |00:00:00.09 | 102K| 1148 | | | |
| 1 | LOAD AS SELECT | ITABLE_4 | 1 | | | | | 0 |00:00:00.09 | 102K| 1148 | 2070K| 2070K| 2070K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_T | 1 | 100K| 7812K| 100K (1)| 00:00:04 | 100K|00:00:00.06 | 100K| 0 | | | |
|* 3 | INDEX RANGE SCAN | IX_SAM1 | 1 | 100K| | 224 (1)| 00:00:01 | 100K|00:00:00.01 | 211 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
일부 데이터 삽입 시 index scan을 이용했을 때 cost가 100K가 나오고 시간이 0초 09 소요됨
OPTIMIZER STATISTICS GATHERING이 비활성화 된 상태에서 결론
전체 데이터 삽입
속도 : FULLSCAN : 3초, INDEXSCAN : 8초
COST : FULLSCAN : 32351, INDEXSCAN : 10M
일부 데이터 삽입
속도 : FULLSCAN : 0초 25, INDEXSCAN : 0초 09
COST : FULLSCAN : 32208, INDEXSCAN : 100K
OPTIMIZER STATISTICS GATHERING이 비활성화 된 상태에서는
전체적으로 활성화 된 상태보다 속도가 빠르게 실행되었고
전체 데이터 삽입시에는 FULLSCAN으로 삽입하는게 낫고
일부 데이터 삽입시에는 INDEXSCAN으로 삽입하는게 근소하게 빠름
(데이터 읽는량(인덱스 손익분기점)에 따라 차이가 날듯함)
COST는 오히려 INDEXSCAN시가 더 높게 나옴
3. OPTIMIZER STATISTICS GATHERING 기능을 OFF + PARALLEL 테스트
parallel dml enable
|
1
2
3
|
SQL> alter session enable parallel dml;
Session altered.
|
테스트1. parallel 전체 데이터로 테이블 생성(FULL SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
drop table itable_1 purge;
create table itable_1 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append parallel(i1 4) */ into itable_1 i1
select /*+ full(st) parallel(st 4) */ * from sample_t st where id1 >= 1;
10000000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 8971 (100)| | | | | 8 |00:00:02.62 | 28 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:02.62 | 28 | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 10M| 762M| 8971 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| ITABLE_1 | 0 | | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 15M| 15M| 2070K (0)|
| 4 | PX BLOCK ITERATOR | | 0 | 10M| 762M| 8971 (1)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 5 | TABLE ACCESS FULL | SAMPLE_T | 0 | 10M| 762M| 8971 (1)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
전체 데이터 삽입 시 parallel full scan을 이용했을 때 cost가 8971이 나오고 시간이 2초 소요됨
테스트2. parallel 전체 데이터로 테이블 생성(INDEX SCAN)
|
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>
drop table itable_2 purge;
create table itable_2 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append parallel(i2 4) */ into itable_2 i2
select /*+ index(st ix_sam1) parallel(st 4) */ * from sample_t st where id1 >= 1;
10000000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 10M(100)| | | | | 8 |00:00:07.86 | 9 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:07.86 | 9 | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 10M| 762M| 10M (1)| 00:06:32 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB) | ITABLE_2 | 0 | | | | | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 16M| 16M| 2070K (0)|
| 4 | PX RECEIVE | | 0 | 10M| 762M| 10M (1)| 00:06:32 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 0 | 10M| 762M| 10M (1)| 00:06:32 | Q1,00 | S->P | RND-ROBIN | 0 |00:00:00.01 | 0 | | | |
| 6 | PX SELECTOR | | 0 | | | | | Q1,00 | SCWC | | 0 |00:00:00.01 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_T | 0 | 10M| 762M| 10M (1)| 00:06:32 | Q1,00 | SCWC | | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | IX_SAM1 | 0 | 10M| | 22153 (1)| 00:00:01 | Q1,00 | SCWP | | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
전체 데이터 삽입 시 parallel index scan을 이용했을 때 cost가 10M이 나오고 시간이 7초 소요됨
테스트3. parallel 일부 데이터로 테이블 생성(FULL SCAN)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
drop table itable_3 purge;
create table itable_3 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append parallel(i3 4) */ into itable_3 i3
select /*+ full(st) parallel(st 4) */ * from sample_t st where id1 <= 10000;
100000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 8931 (100)| | | | | 8 |00:00:00.09 | 29 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.09 | 29 | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 100K| 7812K| 8931 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| ITABLE_3 | 0 | | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 3260K| 3260K| 2070K (0)|
| 4 | PX BLOCK ITERATOR | | 0 | 100K| 7812K| 8931 (1)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 5 | TABLE ACCESS FULL | SAMPLE_T | 0 | 100K| 7812K| 8931 (1)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
일부 데이터 삽입 시 full scan을 이용했을 때 cost가 8931이 나오고 시간이 0초 09 소요됨
테스트4. parallel 일부 데이터로 테이블 생성(INDEX SCAN)
|
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>
drop table itable_4 purge;
create table itable_4 as select * from sample_t where 1=0;
SQL>
insert /*+ gather_plan_statistics append parallel(i4 4) */ into itable_4 i4
select /*+ index(st ix_sam1) parallel(st 4) */ * from sample_t st where id1 <= 10000;
100000 rows created.
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 100K(100)| | | | | 8 |00:00:00.11 | 9 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.11 | 9 | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 100K| 7812K| 100K (1)| 00:00:04 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB) | ITABLE_4 | 0 | | | | | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 3701K| 3701K| 2070K (0)|
| 4 | PX RECEIVE | | 0 | 100K| 7812K| 100K (1)| 00:00:04 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 0 | 100K| 7812K| 100K (1)| 00:00:04 | Q1,00 | S->P | RND-ROBIN | 0 |00:00:00.01 | 0 | | | |
| 6 | PX SELECTOR | | 0 | | | | | Q1,00 | SCWC | | 0 |00:00:00.01 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_T | 0 | 100K| 7812K| 100K (1)| 00:00:04 | Q1,00 | SCWC | | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | IX_SAM1 | 0 | 100K| | 224 (1)| 00:00:01 | Q1,00 | SCWP | | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
일부 데이터 삽입 시 index scan을 이용했을 때 cost가 100K가 나오고 시간이 0초 11 소요됨
OPTIMIZER STATISTICS GATHERING이 비활성화 된 상태 + parallel 병렬 처리 까지 했을 때 결론
전체 데이터 삽입
속도 : FULLSCAN : 2초, INDEXSCAN : 7초
COST : FULLSCAN : 9871, INDEXSCAN : 10M
일부 데이터 삽입
속도 : FULLSCAN : 0초 09, INDEXSCAN : 0초 11
COST : FULLSCAN : 8931, INDEXSCAN : 100K
OPTIMIZER STATISTICS GATHERING이 비활성화 된 상태에서는 parallel 까지 사용한 결과
전체적으로 parallel 을 사용하기 전보다 속도가 빠르게 실행되었고
전체 데이터 삽입시에는 FULLSCAN으로 삽입하는게 낫고
일부 데이터 삽입시에도 FULLSCAN으로 삽입하는게 근소하게 빠름
(데이터 읽는량(인덱스 손익분기점)에 따라 차이가 날듯함)
COST는 오히려 INDEXSCAN시가 더 높게 나옴
전체 비교
결론 :
ITAS 시 통계정보 수집하는 파라미터 on off 여부는 선택이지만 parallel 은 CPU만 여유롭다면 사용하는게 속도측면에서 이익이 있음
전체 데이터를 넣을 경우 FULLSCAN을 사용하는게 낫고 소량 데이터를 넣을 경우 INDEX를 이용하는게 나음
참조 :
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 실행계획 변경 시 spm 적용 방법 (0) | 2022.08.08 |
|---|---|
| 오라클 11gR2 파티션 변경(split, drop, add) 시 커서 상태 변화 확인 (0) | 2022.07.18 |
| 오라클 19c 실행계획 비교 DBMS_XPLAN.COMPARE_PLANS (0) | 2022.03.21 |
| sqlp 실기튜닝실습 17-1 PUSH PREDICATE 문제 devDo (2) | 2022.03.06 |
| sqlp 실기튜닝실습 20-1 인덱스 최적화 구성 devDo (0) | 2022.03.06 |