OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c Parallel DML 시 Direct Path Write 이벤트 관련
오라클 공식 문서중 direct path write and direct path write temp 부분을 보면
Direct Path Write 를 수행할 수 있는 작업에는 디스크 정렬, 병렬 DML 작업, direct-path INSERT, parallel CTAS 및 일부 LOB 작업이 포함된다고 나와있음
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/instance-tuning-using-performance-views.html#GUID-EF32702E-D74B-49CB-B05F-CEC7A9613EF1
원문
direct path write and direct path write temp
When a process is writing buffers directly from PGA (as opposed to the DBWR writing them from the buffer cache), the process waits on this event for the write call to complete. Operations that could perform direct path writes include sorts on disk, parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations.
Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session waits if it has processed all buffers in the PGA and cannot continue work until an I/O request completes.
실제로 append insert(direct-path insert)를 수행하면 direct path write 이벤트가 발생해 direct path 기능이 동작함을 알수있음
하지만 parallel dml(insert, update, delete)를 수행할때도 정말 direct path write가 동작하는지 확인해보기 위해 테스트를 진행해봄
테스트
1. insert append 테스트
2. parallel dml(insert) 테스트
3. parallel dml(insert) + noappend 테스트
4. parallel dml(update) 테스트
5. parallel dml(delete) 테스트
6. 파라미터 변경 후 parallel dml(update) 테스트
7. 파라미터 변경 후 parallel dml(delete) 테스트
테스트
1. insert append 테스트
샘플 테이블 생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 412 2
direct path write temp 1 0
|
현재 412번 발생한 상태임
append insert 진행
|
1
2
3
|
SQL>
alter session set statistics_level = all;
insert /*+ append */ into big_emp select * from big_emp;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 414 3
direct path write temp 1 0
|
412에서 414로 2가 증가함
direct path write 동작함
실행계획 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 69 (100)| | 0 |00:00:00.03 | 518 | 73 | 244 | | | |
| 1 | LOAD AS SELECT | BIG_EMP | 1 | | | | | 0 |00:00:00.03 | 518 | 73 | 244 | 2070K| 2070K| 2070K (0)|
| 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 45430 | 3859K| 69 (2)| 00:00:01 | 41370 |00:00:00.01 | 251 | 72 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
append 힌트가 잘 동작함('LOAD TABLE CONVENTIONAL' 이 아닌 'LOAD AS SELECT' 플랜이 나옴)
2. parallel dml(insert) 테스트
샘플 테이블 재생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 418 4
direct path write temp 1 0
|
현재 418번 발생한 상태임
parallel dml(insert) 진행
|
1
2
3
4
|
SQL>
alter session set statistics_level = all;
alter session enable parallel dml;
insert /*+ parallel(big_emp) */ into big_emp select * from big_emp;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 420 5
direct path write temp 1 0
|
418에서 420로 2가 증가함
direct path write 동작함
실행계획 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 69 (100)| | | | | 8 |00:00:00.12 | 11 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.12 | 11 | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| BIG_EMP | 0 | | | | | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 1042K| 1042K| 1042K (0)|
| 4 | PX RECEIVE | | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | 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 FULL | BIG_EMP | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | Q1,00 | SCWP | | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
parallel 힌트가 정상적으로 잘 동작함('LOAD AS SELECT'가 'PX COORDINATOR' 아래쪽에 나타남)
3. parallel dml(insert) + noappend 테스트
샘플 테이블 재생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 424 6
direct path write temp 1 0
|
현재 424번 발생한 상태임
parallel dml(insert) 진행
|
1
2
3
4
|
SQL>
alter session set statistics_level = all;
alter session enable parallel dml;
insert /*+ parallel(big_emp) noappend */ into big_emp select * from big_emp;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 424 6
direct path write temp 1 0
|
동일함
direct path write 미동작한것으로 보임
실행계획 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 69 (100)| | | | | 8 |00:00:00.09 | 11 |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.09 | 11 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 3 | LOAD TABLE CONVENTIONAL | BIG_EMP | 0 | | | | | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 |
| 4 | PX RECEIVE | | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | 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 FULL | BIG_EMP | 0 | 45430 | 3859K| 69 (2)| 00:00:01 | Q1,00 | SCWP | | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
- Direct Load disabled because noappend hint used
|
noappend 힌트가 잘 동작함('LOAD AS SELECT' 이 아닌 'LOAD TABLE CONVENTIONAL' 플랜이 나옴)
parallel 힌트가 정상적으로 잘 동작함('LOAD TABLE CONVENTIONAL'이 'PX COORDINATOR' 아래쪽에 나타남)
하지만 Note 부분을 보면 Direct Load가 noappend 힌트에 의해 비활성화 되었다고 나옴
4. parallel dml(update) 테스트
샘플 테이블 재생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 426 2
direct path write temp 1 0
|
현재 426번 발생한 상태임
parallel dml(update) 진행
|
1
2
3
4
|
SQL>
alter session set statistics_level = all;
alter session enable parallel dml;
update /*+ parallel(big_emp) */ big_emp set empno = 9999;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 426 2
direct path write temp 1 0
|
동일함
direct path write 미동작한것으로 보임
실행계획 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | | 19 (100)| | | | | 8 |00:00:00.14 | 14 |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.14 | 14 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 45430 | 576K| 19 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 3 | UPDATE | BIG_EMP | 0 | | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 45430 | 576K| 19 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL| BIG_EMP | 0 | 45430 | 576K| 19 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|
parallel 힌트가 정상적으로 잘 동작함('UPDATE'가 'PX COORDINATOR' 아래쪽에 나타남)
5. parallel dml(delete) 테스트
샘플 테이블 재생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 440 2
direct path write temp 1 0
|
현재 426번 발생한 상태임
parallel dml(delete) 진행
|
1
2
3
4
|
SQL>
alter session set statistics_level = all;
alter session enable parallel dml;
delete /*+ parallel(big_emp) */ big_emp;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 440 2
direct path write temp 1 0
|
동일함
direct path write 미동작한것으로 보임
실행계획 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | | 19 (100)| | | | | 8 |00:00:00.39 | 14 |
| 1 | PX COORDINATOR | | 1 | | | | | | | 8 |00:00:00.39 | 14 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 45430 | 19 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 3 | DELETE | BIG_EMP | 0 | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 45430 | 19 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL| BIG_EMP | 0 | 45430 | 19 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------
|
parallel 힌트가 정상적으로 잘 동작함('DELETE'가 'PX COORDINATOR' 아래쪽에 나타남)
위 테스트와 별개로 10046 트레이스를 걸고 위 병렬DML을 수행한 뒤
모든 병렬 세션들의 트레이스를 확인해봐도 direct path write는 관측되지 않음
본문 초입에서 설명한 부분에서 이런 내용이 나와있음
"direct path reads와 마찬가지로 I/O 하위 시스템이 asynchronous writes를 지원하는 경우 wait 횟수는 실행된 write 호출 횟수와 동일하지 않습니다.
세션은 PGA의 모든 버퍼를 처리한 경우 대기하며 I/O 요청이 완료될 때까지 작업을 계속할 수 없습니다."
원문
Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session waits if it has processed all buffers in the PGA and cannot continue work until an I/O request completes.
그래서 async io 파라미터(disk_asynch_io)를 false로 변경하고 filesystemio_options 파라미터도 directio로 변경해봄
|
1
2
3
4
5
6
7
|
SQL> alter system set disk_asynch_io = false scope=spfile;
System altered.
SQL> alter system set filesystemio_options = directio scope=spfile;
System altered.
|
파라미터 설명
- disk_asynch_io
true : 비동기(asynchronous) I/O 기능을 활성화함(기본값)
false : 비동기(asynchronous) I/O 기능을 비활성화함
- filesystemio_options
asynch : 가능한 경우 비동기(asynchronous) I/O를 활성화함
directio : 가능한 경우 direct I/O를 활성화함
setall : 가능한 경우 direct I/O와 비동기(asynchronous) I/O를 모두 활성화함
none : direct I/O와 비동기(asynchronous) I/O를 모두 비활성화함(기본값)
파라미터 적용을 위해 재기동
|
1
2
|
SQL> shutdown immediate
SQL> startup
|
파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> show parameter disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean FALSE
SQL> show parameter filesystemio_options
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string DIRECTIO
|
정상적으로 변경됨
6. 파라미터 변경 후 parallel dml(update) 테스트
샘플 테이블 재생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 8 1
|
현재 8번 발생한 상태임
parallel dml(update) 진행
|
1
2
3
4
|
SQL>
alter session set statistics_level = all;
alter session enable parallel dml;
update /*+ parallel(big_emp) */ big_emp set empno = 9999;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 8 1
|
동일함
direct path write 미동작한것으로 보임
실행계획 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | | 19 (100)| | | | | 8 |00:00:00.11 | 14 |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 8 |00:00:00.11 | 14 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 45430 | 576K| 19 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 3 | UPDATE | BIG_EMP | 0 | | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 45430 | 576K| 19 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL| BIG_EMP | 0 | 45430 | 576K| 19 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|
parallel 힌트가 정상적으로 잘 동작함('UPDATE'가 'PX COORDINATOR' 아래쪽에 나타남)
7. 파라미터 변경 후 parallel dml(delete) 테스트
샘플 테이블 재생성
|
1
2
3
|
SQL>
drop table big_emp purge;
create table big_emp nologging as select * from emp connect by level <= 4;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 11 3
|
현재 11번 발생한 상태임
parallel dml(delete) 진행
|
1
2
3
4
|
SQL>
alter session set statistics_level = all;
alter session enable parallel dml;
delete /*+ parallel(big_emp) */ big_emp;
|
direct path write 이벤트 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 11 3
|
동일함
direct path write 미동작한것으로 보임
실행계획 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last -alias -projection');
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | | 19 (100)| | | | | 8 |00:00:00.38 | 14 |
| 1 | PX COORDINATOR | | 1 | | | | | | | 8 |00:00:00.38 | 14 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 45430 | 19 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 3 | DELETE | BIG_EMP | 0 | | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 4 | PX BLOCK ITERATOR | | 0 | 45430 | 19 (0)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL| BIG_EMP | 0 | 45430 | 19 (0)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------
|
parallel 힌트가 정상적으로 잘 동작함('DELETE'가 'PX COORDINATOR' 아래쪽에 나타남)
파라미터를 변경했지만 결과는 동일했음
결론 :
오라클 메뉴얼 상에는 parallel dml에도 direct path io(write)가 동작한다고 되어있지만
실제 테스트 해본 결과 일반적인 방법으로 parallel dml(update, delete)을 실행했을때 direct path write 이벤트는 발생하지 않음
오직 parallel insert에서만 direct path write가 발생함
insert도 noappend 힌트와 parallel dml 을 하게되면 direct path write 이벤트가 발생하지 않았음
async io 관련 파라미터를 비활성화 한뒤에 테스트 해보았지만 결과는 동일했음
이 결과만 보고 결론을 내자면 기본적으로 parallel insert 외 parallel dml(update, delete)은 direct path io가 동작하지 않는듯함
또는 direct path write 이벤트가 아닌 다른 이벤트로 표시되거나 하는듯함(확실하지 않음)
참조 :
https://oracle-base.com/articles/misc/direct-and-asynchronous-io
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/instance-tuning-using-performance-views.html#GUID-EF32702E-D74B-49CB-B05F-CEC7A9613EF1
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DISK_ASYNCH_IO.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/FILESYSTEMIO_OPTIONS.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/IO-configuration-and-design.html#GUID-7B16A2D4-F360-4271-9F26-B4DCEC36FD89
https://cafe.naver.com/dbian/7736
https://positivemh.tistory.com/881
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 다른 세션 트레이스 설정 (0) | 2024.04.06 |
|---|---|
| 오라클 19c latch: cache buffers chains 이벤트 발생시 핫블록 찾기 (0) | 2024.03.28 |
| 오라클 19c FULL TABLE SCAN (FTS)시 direct path read 이벤트 관련 (0) | 2024.03.21 |
| 오라클 19c group by 컬럼 나열 및 집계함수 적용 성능 비교 (0) | 2024.03.19 |
| 오라클 19c Prefetch, Batch I/O, Table access by rowid batched 설명 (0) | 2024.02.29 |