OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.18.0.0
방법 : 오라클 19c 병렬 dml 실행계획 확인 테스트
친절한 sql 튜닝 책 443페이지에 보면 병렬 dml이 잘 동작하는지 확인하는 방법에 대해 설명하고 있음
dml 작업을 각 병렬 프로세스가 처리하는지 아니면 qc가 처리하는지를 실행계획에서 확인할수 있고,
update(또는 delete/insert)가 'PX COORDINATOR' 아래쪽에 나타나면 update를 각 병렬프로세스가 처리하는 것이고,
update가 'PX COORDINATOR' 위쪽에 나타나면 update를 qc가 처리하는것이라고 함
병렬 udpate 쿼리를 통해 테스트 해봄
샘플 테이블 생성
|
1
2
3
|
SQL> create table e2 as select * from emp;
Table created.
|
병렬 dml 비활성화 상태에서 parallel 힌트와 함께 update 실행(enable_parallel_dml 힌트 미사용)
|
1
2
3
4
5
6
|
SQL>
update /*+ gather_plan_statistics parallel(e2 4) */ e2
set empno = 1111
where empno >= 0;
14 rows updated.
|
플랜 확인
|
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> SELECT * FROM TABLE(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 | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | | 3 (100)| | | | | 0 |00:00:00.02 | 7 | 2 |
| 1 | UPDATE | E2 | 1 | | | | | | | | 0 |00:00:00.02 | 7 | 2 |
| 2 | PX COORDINATOR | | 1 | | | | | | | | 14 |00:00:00.02 | 3 | 1 |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 0 | 14 | 56 | 3 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 14 | 56 | 3 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | 0 |
|* 5 | TABLE ACCESS FULL| E2 | 0 | 14 | 56 | 3 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
5 - UPD$1 / E2@UPD$1
- parallel(e2 4)
Note
-----
- Degree of Parallelism is 4 because of table property
- PDML is disabled in current session
|
실행계획에서 Id 1 UPDATE가 Id 2 PX COORDINATOR 보다 위에 있어 병렬dml이 제대로 작동하지 않음을 알수 있음
정확히 말하면 emp 테이블을 읽을때만 병렬로 읽고 update 시에는 병렬dml이 작동하지 않음
그리고 Note 부분에도 PDML is disabled in current session 이라고 표시됨
동일 테스트를 한번 더하기 위해 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
병렬 dml 활성화 상태에서 parallel 힌트와 함께 update 실행(enable_parallel_dml 힌트 사용)
|
1
2
3
4
5
6
|
SQL>
update /*+ gather_plan_statistics enable_parallel_dml parallel(e2 4) */ e2
set empno = 1111
where empno >= 0;
14 rows updated.
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> SELECT * FROM TABLE(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 | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | | 2 (100)| | | | | 8 |00:00:00.02 | 13 | 3 |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.02 | 13 | 3 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 |
| 3 | UPDATE | E2 | 0 | | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | 0 |
|* 5 | TABLE ACCESS FULL| E2 | 0 | 14 | 56 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
5 - UPD$1 / E2@UPD$1
- parallel(e2 4)
Note
-----
- Degree of Parallelism is 4 because of table property
|
실행계획에서 Id 3 UPDATE가 Id 2 PX COORDINATOR 보다 아래에 있어 병렬dml이 제대로 작동함을 알수 있음
정확히 말하면 emp 테이블을 읽을때와 update 시 모두 병렬로 읽고 병렬로 update 함
그리고 이전 플랜의 Note 부분에 존재하던 PDML is disabled in current session 도 사라짐
추후를 위해 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
참조 :
친절한 sql 튜닝 책
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 (0) | 2023.12.07 |
|---|---|
| 오라클 19c not in 절에 null 허용 컬럼 관련 테스트 (0) | 2023.09.21 |
| 오라클 19c leading use_nl 힌트 제어 테스트 (0) | 2023.03.04 |
| 오라클 19c nl 조인 순서 제어 테스트 (0) | 2023.03.03 |
| 오라클 19c 옵티마이저에 영향을 주는 파라미터 목록 (0) | 2023.01.30 |