[실험] (실행계획 ch07_03.hwp 파일 참조)

create table t

partition by range(no) (

partition p1 values less than(25000)

, partition p2 values less than(50000)

, partition p3 values less than(75000)

, partition p4 values less than(maxvalue)

)

as

select rownum no, lpad(rownum, 10, '0') no2 from dual

connect by level <= 100000;

SQL> explain plan for

2 update /*+ parallel(t 4) */ t set no2 = lpad(no,5,'0');

---------------------------------------------------------------------------------------

| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

---------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | | | |

| 1 | UPDATE | T | | | | | |

| 2 | PX COORDINATOR | | | | | | |

| 3 | PX SEND QC (RANDOM)| :TQ10000 | | | Q1,00 | P->S | QC (RAND) |

| 4 | PX BLOCK ITERATOR | | 1 | 4 | Q1,00 | PCWC | |

| 5 | TABLE ACCESS FULL| T | 1 | 4 | Q1,00 | PCWP | |

---------------------------------------------------------------------------------------

SQL> alter session enable parallel dml;

세션이 변경되었습니다.

SQL> explain plan for

2 update /*+ parallel(t 4) */ t set no2 = lpad(no,5,'0');

---------------------------------------------------------------------------------------

| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

---------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | | | |

| 1 | PX COORDINATOR | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | Q1,00 | P->S | QC (RAND) |

| 3 | UPDATE | T | | | Q1,00 | PCWP | |

| 4 | PX BLOCK ITERATOR | | 1 | 4 | Q1,00 | PCWC | |

| 5 | TABLE ACCESS FULL| T | 1 | 4 | Q1,00 | PCWP | |

---------------------------------------------------------------------------------------

- 첫 번째 SQL문은 병렬서버가 테이블을 읽고 갱신할 주소만 QC에 전달하고 update는 QC가 실행한다. 두 번째 SQL 문은 병렬서버가 update를 실행하고 갱신한 건수만 QC에 전달한다.

- 오라클 9iR1까지는 한 세그먼트를 두 개 이상의 프로세스가 동시에 갱신할 수 없었다. 따라서 파티션되지 않은 테이블이라면 병렬로 갱신할 수 없었고, 파티션 테이블일 때라도 병렬도를 파티션 개수 이하로만 지정할 수 있었다.

- 오라클 9iR2부터는 블록기반 Granule로 바뀌었다.

[실험] (실행계획 ch07_04.hwp 파일 참조)

create table emp

partition by range(sal) (

partition p1 values less than(1000)

, partition p2 values less than(2000)

, partition p3 values less than(3000)

, partition p4 values less than(MAXVALUE) )

as select * from scott.emp ;

create index emp_sal_idx on emp(sal) local;

create table dept as select * from scott.dept;

alter table dept add constraint dept_pk primary key(deptno);

SQL> select /*+ ordered use_nl(d) full(e) parallel(e 2) */ *

2 from emp e, dept d

3 where d.deptno = d.deptno and e.sal >= 1000;

---------------------------------------------------------------------------------------

| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | | |

| 1 | PX COORDINATOR | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | Q1,00 | P->S | QC (RAND) |

| 3 | NESTED LOOPS | | | | Q1,00 | PCWP | |

| 4 | PX BLOCK ITERATOR | | 2 | 4 | Q1,00 | PCWC | |

|* 5 | TABLE ACCESS FULL| EMP1 | 2 | 4 | Q1,00 | PCWP | |

| 6 | TABLE ACCESS FULL | DEPT | | | Q1,00 | PCWP | |

---------------------------------------------------------------------------------------

- Parallel Full Scan은 블록 기반 Granule이 사용되므로 병렬도는 파티션 개수보다 더 클 수 있다. 단 병렬도를 너무 크게 잡으면 아무 일도 안 하는 노는 프로세스가 생길 수 있으므로 주의해야 한다.

SQL> select /*+ ordered use_nl(d) index(e emp_sal_idx)

2 parallel_index(e emp_sal_idx 3) */ *

3 from emp1 e, dept d

4 where d.deptno = d.deptno and e.sal >= 1000;

--------------------------------------------------------------------------------------

| Id | Operation | Name | Pstart| Pstop | TQ |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | |

| 1 | PX COORDINATOR | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | Q1,00 |

| 3 | NESTED LOOPS | | | | Q1,00 |

| 4 | PX PARTITION RANGE ITERATOR | | 2 | 4 | Q1,00 |

| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP1 | 2 | 4 | Q1,00 |

|* 6 | INDEX RANGE SCAN | EMP_SAL_IDX | 2 | 4 | Q1,00 |

| 7 | TABLE ACCESS FULL | DEPT | | | Q1,00 |

--------------------------------------------------------------------------------------

- 인덱스를 드라이빙으로 병렬처리하려면 드라이빙 인덱스가 반드시 파티션인덱스여야 한다. 드라이빙 테이블(emp)과 dept 테이블은 파티션과 상관없다.

- 인덱스를 드라이빙한 병렬 NL조인에는 파티션 기반 Granule이 사용되므로 병렬도가 파티션 개수를 초과할 수 없다.

- 여기서는 세 개 파티션만 액세스하므로 병렬도를 3 이상 줄 수 없다. 만약 병렬도를 2로 지정한다면 각각 하나씩 처리하다가 먼저 일을 마친 프로세스가 나머지 하나를 더 처리한다.

- outer와 inner 테이블 모두 대용량이다.(어느 한쪽이 작으면 병렬 해시조인 사용)

- outer 테이블에 사용된 특정 조건의 선택도가 낮은데 그 컬럼의 인덱스가 없다.

- inner 쪽 조인 컬럼에 인덱스가 있다

- 수행빈도가 낮다(높으면 인덱스를 만들어 주었을 것이다)

create or replace function getDname(p_deptno number)

return varchar2

parallel_enable

is

l_dname dept.dname%type;

begin

select dname into l_dname from dept where deptno = p_deptno;

return l_dname;

end;

/

select /*+ parallel(emp 2) */ empno, ename, job, sal, getDname(deptno)

from emp;

- getName() 함수가 세션 변수를 사용하지 않았으므로 parallel_enable에 관계없이 병렬수행이 가능하다.

SQL> select /*+ parallel(t1 24) */ no, no2

2 from t1

3 order by no2;

----------------------------------------------------------------

| Id | Operation | Name |IN-OUT| PQ Distrib |

----------------------------------------------------------------

| 0 | SELECT STATEMENT | | | |

| 1 | PX COORDINATOR | | | |

| 2 | PX SEND QC (ORDER) | :TQ10001 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | PCWP | |

| 4 | PX RECEIVE | | PCWP | |

| 5 | PX SEND RANGE | :TQ10000 | P->P | RANGE |

| 6 | PX BLOCK ITERATOR | | PCWC | |

| 7 | TABLE ACCESS FULL| T1 | PCWP | |

----------------------------------------------------------------

SQL> select /*+ parallel(t1 24) */ no, no2, rownum

2 from t1

3 order by no2;

---------------------------------------------------------------

| Id | Operation | Name |IN-OUT| PQ Distrib |

---------------------------------------------------------------

| 0 | SELECT STATEMENT | | | |

| 1 | SORT ORDER BY | | | |

| 2 | COUNT | | | |

| 3 | PX COORDINATOR | | | |

| 4 | PX SEND QC (RANDOM)| :TQ10000 | P->S | QC (RAND) |

| 5 | PX BLOCK ITERATOR | | PCWC | |

| 6 | TABLE ACCESS FULL| T1 | PCWP | |

---------------------------------------------------------------

- 각 프로시져가 P->P로 동작하여 정렬부담을 나누어가져야 함에도 불구하고 QC가 정렬을 전담하는 상황이 발생함

alter session enable parallel dml;

explain plan for

update /*+ parallel(t 4) */ t set no2 = lpad(rownum, 5, '0');

(실행계획 ch07_06.hwp 파일 참조)