95-1 DML작업 병렬처리

[ Sample Table : hr.emp_dw, hr.emp_dw_copy ]

HR@ora11g> create table hr.emp_dw
           as select rownum as employee_id, last_name, first_name, hire_date, job_id, salary, manager_id, department_id
           from hr.employees e, (select level as id from dual connect by level <= 10000);

Table created.

HR@ora11g> create table hr.emp_dw_copy as select * from hr.emp_dw where 1 = 2;

Table created.


HR@ora11g> select blocks, bytes/1024/1024 mb from user_segments where segment_name = 'EMP_DW';

    BLOCKS         MB
---------- ----------
      7552         59


> 수행 전 플랜 확인

HR@ora11g> explain plan for
           insert /*+ parallel(e1 2) */ into hr.emp_dw_copy e1
           select /*+ parallel(e2 2) */ * from hr.emp_dw d2;

Explained.

HR@ora11g> select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |  1104K|    99M|  2055   (1)| 00:00:25 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP_DW_COPY |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | EMP_DW      |  1104K|    99M|  2055   (1)| 00:00:25 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

> LOAD TABLE CONVENTIONAL : 데이터 버퍼 캐시 사용

>>>>>>>>>>>>>>> 다름?????
병렬처리모습이 나와야 함
추출 시 병렬처리(select) > QC 전달 > copy에 로드 (insert) 시 시리얼하게 처리 : 직렬처리 > 성능 저하
                                                ㄴ conventional = 데이터버퍼캐시 사용한다    

↓ 해결하자

# DML작업 병렬처리 활성화
HR@ora11g> alter session enable parallel dml;

Session altered.


HR@ora11g> explain plan for
           insert /*+ parallel(e1 2) */ into hr.emp_dw_copy e1
           select /*+ parallel(e2 2) */ * from hr.emp_dw d2;

Explained.

HR@ora11g> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |             |  1104K|    99M|  2055   (1)| 00:00:25 |        |      |            |
|   1 |  PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001    |  1104K|    99M|  2055   (1)| 00:00:25 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       | EMP_DW_COPY |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |             |  1104K|    99M|  2055   (1)| 00:00:25 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000    |  1104K|    99M|  2055   (1)| 00:00:25 |        | S->P | RND-ROBIN  |
|   6 |       TABLE ACCESS FULL | EMP_DW      |  1104K|    99M|  2055   (1)| 00:00:25 |        |      |            |
--------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

- LOAD AS SELECT : 병렬처리 ( 또는 LOAD AS DIRECT)
- PX COORDINATOR : 처리 건수 메세지만 전달



# DML작업 병렬처리 비활성화
HR@ora11g> alter session disable parallel dml;

Session altered.

--- 해당 에러 나오면 세션 재접속하기
ORA-12841: Cannot alter the session parallel DML state within a transaction



# Direct Path Read 발생할 때
- Temp Segment 블록을 읽을 때
- 병렬 쿼리로 Full Table Scan
- Serial Direct Read


# Direct Path Write 발생할 때
- Export를 수행할 때 Direct 옵션 설정
- Parallel DML을 수행할 때
- Direct Path Insert
    - insert /*+ append */ ... select 
    - SQL*Loader(sqlldr) direct 옵션을 지정해서 데이터 로드 작업
    - Ctas(Create Table ... As Select ...)

> append 시 HWM 뒤에 입력되니 공간활용 잘하기