반응형
Parallel DML을 사용하여 DML을 수행하다가, 신경쓰지 않은 부분이 있었는데 그것은 바로 실제로 프로세스들이 다 일을 할까? 였습니다.
세션에서만 보면 당연히 프로세스들이 parallel 힌트를 준 만큼 생성되어 있었기 때문에 잘 수행되고 있다고 생각했지만, 속도적인 부분에서 차이가 있는것을 알게되었고, 테스트를 통해 어떤 차이 때문에 그렇게 되는지 확인해보고자 했습니다.
테스트는 이전에 통계정보 EXPORT,IMPORT 했을 때 사용했던 테이블과 INSERT문을 사용했습니다.
INSERT 시 여러 옵션들을 사용해서 프로세스들의 일하는 것을 확인해보았습니다.
https://bae9086.tistory.com/455
테스트 결과를 간단히 앞에 이야기하면 /*+ enable_parallel_dml parallel(4) */ 로 확실하게 힌트를 주고 DML을 하는게 병렬프로세스들이 모두 일할 수 있습니다.
준비
테이블 생성
CREATE TABLE DEV.ORD_ITEM_RANGE2
(
ORD_NO NUMBER(10) NOT NULL,
ITEM_ID NUMBER(10) NOT NULL,
ORD_ITEM_QTY NUMBER(4),
ORD_YM VARCHAR2(6) NOT NULL,
ORD_DT VARCHAR2(8),
ORD_HMS VARCHAR2(6),
UPPER2 VARCHAR2(2),
UPPER_CASE VARCHAR2(10),
LOWER_CASE VARCHAR2(10),
ALPHABET VARCHAR2(400),
ALPHABET_NUMERIC VARCHAR2(400)
)
TABLESPACE MY_DATA
NOLOGGING
PARTITION BY RANGE (ORD_YM)
(
PARTITION P201201 VALUES LESS THAN ('201202')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201202 VALUES LESS THAN ('201203')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201203 VALUES LESS THAN ('201204')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201204 VALUES LESS THAN ('201205')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201205 VALUES LESS THAN ('201206')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201206 VALUES LESS THAN ('201207')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201207 VALUES LESS THAN ('201208')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201208 VALUES LESS THAN ('201209')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201209 VALUES LESS THAN ('201210')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201210 VALUES LESS THAN ('201211')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201211 VALUES LESS THAN ('201212')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201212 VALUES LESS THAN ('201213')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201301 VALUES LESS THAN ('201302')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201302 VALUES LESS THAN ('201303')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201303 VALUES LESS THAN ('201304')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201304 VALUES LESS THAN ('201305')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201305 VALUES LESS THAN ('201306')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201306 VALUES LESS THAN ('201307')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201307 VALUES LESS THAN ('201308')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201308 VALUES LESS THAN ('201309')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201309 VALUES LESS THAN ('201310')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201310 VALUES LESS THAN ('201311')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201311 VALUES LESS THAN ('201312')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P201312 VALUES LESS THAN ('201313')
TABLESPACE MY_DATA
STORAGE
(
INITIAL 8M
NEXT 1M
)
NOLOGGING
NOCOMPRESS,
PARTITION P_DEFAULT VALUES LESS THAN (MAXVALUE)
TABLESPACE MY_DATA
NOLOGGING
NOCOMPRESS
);
데이터 삽입
아래 INSERT 문장에 힌트를 줘서 테스트를 진행할 예정입니다.
--삽입
INSERT INTO ORD_ITEM_RANGE2
SELECT * FROM ORD_ITEM_RANGE;
COMMIT;
--초기화
truncate table ORD_ITEM_RANGE2;그리고 프로세스가 동작하고 있는 것을 확인하는 쿼리는 아래의 글의 쿼리를 사용하였습니다.
https://bae9086.tistory.com/456
1번.
힌트로 /*+ parallel(4) */ 만 줄때
INSERT /*+ parallel(4) */INTO ORD_ITEM_RANGE2
SELECT /*+ parallel(4) */ * FROM ORD_ITEM_RANGE;NST_ID SPID PROCESS SESS_STATUS TX_STATUS USERNAME OSUSER MACHINE MODULE COMMAND_NAME EVENT SQL_ID EXEC_TIME WAIT_TIME LAST_CALL_ET SESS_PGA SQL_TEXT KILL
--------- ---------------------------------- ---------------------------------- ----------- ---------------- ----------- ----------------- ------------------ ---------------------------------------- ----------------- -------------------------- ------------- ----------------- --------- ------------ ------------------------------------------- ---------------------------- ----------------------------------------------------
1 13173144 1120:14576 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT db file sequential read 48fdxznzrwg7m 20230117 15:25:34 0 727 5 MB INSERT /*+ parallel(4) */ alter system kill session '2467,43274,@1' immediate;
1 40108534 40108534 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 48fdxznzrwg7m 20230117 15:25:34 0 727 7 MB INSERT /*+ parallel(4) */ alter system kill session '199,19192,@1' immediate;
1 39977284 39977284 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 48fdxznzrwg7m 20230117 15:25:34 0 727 7 MB INSERT /*+ parallel(4) */ alter system kill session '763,5551,@1' immediate;
1 42140108 42140108 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 48fdxznzrwg7m 20230117 15:25:34 0 727 7 MB INSERT /*+ parallel(4) */ alter system kill session '575,38900,@1' immediate;
1 38469946 38469946 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 48fdxznzrwg7m 20230117 15:25:34 0 727 7 MB INSERT /*+ parallel(4) */ alter system kill session '387,29101,@1' immediate;
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
------------------------------------------------------------------------------------------------------------------------ --------- ---------
Servers Busy 5 0
1 rows selected.
총 소요시간 263초
2번
힌트로 /*+ enable_parallel_dml parallel(4) */ 만 줄때
INSERT /*+ enable_parallel_dml parallel(4) */INTO ORD_ITEM_RANGE2
SELECT /*+ parallel(4) */ * FROM ORD_ITEM_RANGE;INST_ID SPID PROCESS SESS_STATUS TX_STATUS USERNAME OSUSER MACHINE MODULE COMMAND_NAME EVENT SQL_ID EXEC_TIME WAIT_TIME LAST_CALL_ET SESS_PGA SQL_TEXT KILL
--------- ---------------------------------- ---------------------------------- ----------- ---------------- ---------- ------------------ ---------------- ---------------------------------------- ---------------- ------------------------- ------------- ----------------- --------- ------------ ------------------------------------------- ---------------------------- -----------------------------------------------------
1 13173144 1120:14576 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq: Execute Reply 6k2d0c7zf6f5h 20230117 16:07:38 1 11 7 MB INSERT /*+ enable_paralle alter system kill session '2467,43274,@1' immediate;
1 40108534 40108534 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path read 6k2d0c7zf6f5h 20230117 16:07:38 0 11 10 MB INSERT /*+ enable_paralle alter system kill session '199,16805,@1' immediate;
1 39977284 39977284 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 6k2d0c7zf6f5h 20230117 16:07:38 0 11 9 MB INSERT /*+ enable_paralle alter system kill session '766,63002,@1' immediate;
1 42140108 42140108 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 6k2d0c7zf6f5h 20230117 16:07:38 0 11 10 MB INSERT /*+ enable_paralle alter system kill session '575,24604,@1' immediate;
1 38469946 38469946 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 6k2d0c7zf6f5h 20230117 16:07:38 0 11 10 MB INSERT /*+ enable_paralle alter system kill session '387,62052,@1' immediate;
5 rows selected.
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
------------------------------------------------------------------------------------------------------------------------ --------- ---------
Servers Busy 5 0
1 rows selected.
총 소요시간 17초
3번
힌트로 /*+ append parallel(4) */을 줄때
INSERT /*+ append parallel(4) */INTO ORD_ITEM_RANGE2
SELECT /*+ parallel(4) */ * FROM ORD_ITEM_RANGE;
INST_ID SPID PROCESS SESS_STATUS TX_STATUS USERNAME OSUSER MACHINE MODULE COMMAND_NAME EVENT SQL_ID EXEC_TIME WAIT_TIME LAST_CALL_ET SESS_PGA SQL_TEXT KILL
--------- ---------------------------------- ---------------------------------- ----------- ---------------- ----------- ------------------- ------------------ ----------------------------------------- -------------- ---------------------------- ------------- ----------------- --------- ------------ ------------------------------------------- --------------------------- -----------------------------------------------------
1 13173144 1120:14576 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 4k5j9g897hkmz 20230117 16:09:53 0 7 10 MB INSERT /*+ append paralle alter system kill session '2467,43274,@1' immediate;
1 40108534 40108534 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: need buffer 4k5j9g897hkmz 20230117 16:09:53 0 7 8 MB INSERT /*+ append paralle alter system kill session '199,9498,@1' immediate;
1 39977284 39977284 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 4k5j9g897hkmz 20230117 16:09:53 0 7 8 MB INSERT /*+ append paralle alter system kill session '766,55702,@1' immediate;
1 42140108 42140108 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 4k5j9g897hkmz 20230117 16:09:53 0 7 8 MB INSERT /*+ append paralle alter system kill session '575,4370,@1' immediate;
1 38469946 38469946 ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq Credit: send blkd 4k5j9g897hkmz 20230117 16:09:53 0 7 8 MB INSERT /*+ append paralle alter system kill session '387,2175,@1' immediate;
5 rows selected.
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
------------------------------------------------------------------------------------------------------------------------ --------- ---------
Servers Busy 5 0
총 소요시간 73초
4번
alter session enable parallel dml + 힌트 parallel(4) 줄때
alter session enable parallel dml;
INSERT /*+ parallel(4) */INTO ORD_ITEM_RANGE2
SELECT /*+ parallel(4) */ * FROM ORD_ITEM_RANGE;INST_ID SPID PROCESS SESS_STATUS TX_STATUS USERNAME OSUSER MACHINE MODULE COMMAND_NAME EVENT SQL_ID EXEC_TIME WAIT_TIME LAST_CALL_ET SESS_PGA SQL_TEXT KILL
--------- ---------------------------------- ---------------------------------- ----------- ---------------- --------- ----------------- ------------------ ------------------------------------------- ---------------- ------------------------- ------------- ----------------- --------- ------------ ------------------------------------------- --------------------------- ------------------------------------------------------
1 13173144 1120:14576 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT PX Deq: Execute Reply 48fdxznzrwg7m 20230117 16:12:15 1 3 7 MB INSERT /*+ parallel(4) */ alter system kill session '2467,43274,@1' immediate;
1 40108534 40108534 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 48fdxznzrwg7m 20230117 16:12:15 0 3 8 MB INSERT /*+ parallel(4) */ alter system kill session '194,43644,@1' immediate;
1 39977284 39977284 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 48fdxznzrwg7m 20230117 16:12:15 0 3 9 MB INSERT /*+ parallel(4) */ alter system kill session '766,37039,@1' immediate;
1 42140108 42140108 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path read 48fdxznzrwg7m 20230117 16:12:15 0 3 9 MB INSERT /*+ parallel(4) */ alter system kill session '575,28751,@1' immediate;
1 38469946 38469946 ACTIVE ACTIVE DEV 110420201094793 K10420201094793 Trusted Orange for ORACLE (Unicode) DBA INSERT direct path write 48fdxznzrwg7m 20230117 16:12:15 0 3 7 MB INSERT /*+ parallel(4) */ alter system kill session '387,46198,@1' immediate;
5 rows selected.
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
------------------------------------------------------------------------------------------------------------------------ --------- ---------
Servers Busy 5 0
1 rows selected.
총 소요시간 50초
4가지의 테스트를 수행한 결과.
alter session enable parallel dml 명령어를 수행하여 세션level로 parallel DML이 가능한 상태에서 힌트 parellel를 주었을때와 힌트내부에 enable_parallel_dml parallel를 주어서 parallel DML을 활성화 시긴 경우에 모든 병렬프로세스들이 일하는 것을 확인할 수 있었습니다. (2번 과 4번 테스트)
확인은 확인 쿼리의 TX_STATUS를 확인하였습니다. 트랜잭션이 2번과 4번의 경우에는 parallel process들까지 ACTIVE 상태여서 모두 일을 하고 있다는 것을 확인할 수 있으며, 1번과 3번의 경우메인 세션의 process 하나만 ACTIVE 상태이고 나머지는 ACTIVE 표시가 되어 있지 않은 것을 확인 할 수 있습니다.
결론적으로는
alter session DDL 이든, 쿼리 내부에 힌트로 enable_parallel_dml 든 parallel dml이 활성화가 되어 있어야지 프로세스도 잘 만들어지고 일 분배도 잘됩니다.
두개 중에 더 확실한거는 /*+ enable_parallel_dml parallel(4) */ 로 확실하게 힌트를 주는게 낫습니다.
그리고 event에 direct path write ,direct path read 이벤트가 발생하면 모든 프로세스가 일을 하고, PX Deq Credit: send blkd 이벤트가 발생할 경우 sequential read로 main process 하나만 일합니다.
반응형
'Oracle > 운영' 카테고리의 다른 글
| 특정 테이블스페이스에서 오브젝트 용량 증가량 조회 (0) | 2023.02.21 |
|---|---|
| Autonomous Health Framework (AHF) 업그레이드 방법 (0) | 2023.02.07 |
| 세션 모니터링 쿼리 (0) | 2023.01.18 |
| Oracle 데이터 중복값 쿼리로 확인하기 (0) | 2022.12.08 |
| [split] maxvalue가 있는 range 파티션에 다음 파티션 추가하기 (0) | 2022.11.11 |
댓글