| # 요약 | |
| ■ 병렬처리 # Serial Full Table Scan # Parallel Full Table Scan, Direct Path Read 방식 ■ IN-OUT 오퍼레이션 # order by # group by push down # no_gby_pushdown v$pq_tqstat ■ Full Partition Wise Join # pq_distribute(e,none,none) ■ Partial Partition Wise Join # pq_distribute(e, none, partition) # pq_distribute(e,partition,none) ■ 동적 파티셔닝 # pq_distribute(e,hash,hash) ■ Broadcast # pq_distribute(e,broadcast,none) |
|
■ 병렬처리
- SQL문이 수행해야할 작업 범위를 여러개의 작은 단위로 나누어 여러 서버 프로세스가 동시에 처리하는 작업을 의미한다.
[ Sample Table : hr.emp ]
HR@ora11g> drop table hr.emp purge;
Table dropped.
HR@ora11g> create table hr.emp nologging
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 <= 1000);
Table created.
--- 대용량 자료는 degree 숫자를 더 높여 통계 수집을 빨리 끝내버려도 좋다.
HR@ora11g> exec dbms_stats.gather_table_stats('hr', 'emp', degree => 2)
PL/SQL procedure successfully completed.
HR@ora11g> select num_rows, blocks, avg_row_len, logging from user_tables where table_name = 'EMP';
NUM_ROWS BLOCKS AVG_ROW_LEN LOGGING
---------- ---------- ----------- -------
107000 760 45 NO
--- Set Logging Mode
HR@ora11g> alter table hr.emp logging;
Table altered.
HR@ora11g> select num_rows, blocks, avg_row_len, logging from user_tables where table_name = 'EMP';
NUM_ROWS BLOCKS AVG_ROW_LEN LOGGING
---------- ---------- ----------- -------
107000 760 45 YES
--- 행 운반단위
HR@ora11g> show arraysize
arraysize 15
HR@ora11g> set arraysize 1000
--- 통계 정보 모두 수집
HR@ora11g> alter session set statistics_level = all;
Session altered.
# Serial Full Table Scan
HR@ora11g> select /*+ full(e) */ count(*) from emp e;
COUNT(*)
----------
107000
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 744 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 744 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 107K| 107K|00:00:00.01 | 744 |
-------------------------------------------------------------------------------------
# Parallel Full Table Scan, Direct Path Read 방식
HR@ora11g> select /*+ full(e) parallel(e 2) */ count(*) from emp e;
> parallel(e 2) = ('hr', 'emp', degree => 2)
> count(*) 그룹함수
--- allstats last parallel
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last parallel'));
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | | 1 |00:00:00.11 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:00.11 | 5 |
| 2 | PX COORDINATOR | | 1 | | | | | 2 |00:00:00.11 | 5 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 0 | 107K| Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL| EMP | 0 | 107K| Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
> +0.11 속도에 실망하지 말자. 서버에서는 빠르다.
- QC : Query Coordinator
- 병렬 SQL문을 발생한 세션
- 작업을 지시하고 일이 잘 진행되는지 관리, 감독하는 역할
- 병렬 서버 프로세스
- 실제 작업을 수행하는 개별 세션
- 병렬 서버 = 병렬 프로세스 = 병렬 슬레이브
- 병렬 서버 풀 (Parallel Execution Server Pool)
- 병렬 처리 시 서버풀에 있는 프로세스부터 사용
- 부족분은 오라클이 추가 생성
- 생성할 수 있는 최대 병렬 서버 개수 : parallel_max_servers
- 병렬 처리가 발생하지 않더라도 parallel_min_servers 파라미터에 지정된 개수만큰 병렬 프로세스를 유지한다.
- 이유 : 병렬 처리 시 서버 프로세스를 띄우는 부하를 줄이기 위해서
SYS@ora11g> show parameter Parallel_%_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 80
parallel_min_servers integer 0
- 병렬 서버 집합(server set) 할당
- 병렬도(DOP(Degree Of Parallelism))와 오퍼레이션 종류에 따라 한개 또는 두개의 병렬서버 집합 할당
- 서버풀(Parallel Execution Server Pool)로부터 필요한 만큼 서버프로스세스 확보
- 부족분은 새로 생성
> 병렬 서버 생성을 위해 작업 > 메모리 필요 > Large Pool에서 빌려쓴다.
> parallel 병렬처리는 대용량 작업을 많이해서 메모리를 거치면 더 느려진다.
> 최적화 Parallel + Join + Full + Hash
■ IN-OUT 오퍼레이션
- Blank : serial, 직렬 작업
- P->S : Parallel_To_Serial
각 병렬 서버 프로세스가 처리한 데이터를 QC에게 전송
- QC (RAND) : Order by가 없을 때에 표시되며 병렬 프로세스들이 무순위로 QC에게 데이터를 전송
- PCWP : Parallel_Combinded_With_Parent
한 서버집합이 현재 스텝과 그 부모 스텝을 모두 처리
- PCWC : Parallel_Combinded_With_Child
한 서버집합이 현재 스텝과 그 자식 스텝을 모두 처리
- S->P : Parallel_From_Serial
QC가 읽은 데이터를 데이터 큐를 통해 병렬 서버프로세스에게 전송
- P->P : Parallel_To_Parallel
데이터 정렬, 그룹핑하거나 조인을 위해 동적으로 파티셔닝할 때 사용
첫번째 병렬 서버 집합이 읽거나 가고한 데이터를 두번째 병렬 서버 집합에 전송
> PCWP, PCWC 보다 P <-> S 간의 작업이 더 중요하다.
# 병렬도(degree) = 2
# 서버 프로세스 = 병렬도 * 2 = 2 * 2 = 4 = p000 ~ p003
# 통신 채널 수 = 병렬도 ** 2 = 2 ** 2 = 4
# order by 절 사용
HR@ora11g> select /*+ full(e) parallel(e 2) */ * from emp e order by last_name;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last parallel'));
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | | 107K|00:00:00.16 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | 107K|00:00:00.16 | 5 | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 0 | 107K| Q1,01 | P->S | QC (ORDER) | 0 |00:00:00.01 | 0 | | | |
| 3 | SORT ORDER BY | | 0 | 107K| Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 7902K| 1110K| 3511K (0)|
| 4 | PX RECEIVE | | 0 | 107K| Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND RANGE | :TQ10000 | 0 | 107K| Q1,00 | P->P | RANGE | 0 |00:00:00.01 | 0 | | | |
| 6 | PX BLOCK ITERATOR | | 0 | 107K| Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 7 | TABLE ACCESS FULL| EMP | 0 | 107K| Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
# 병렬 작업 정보
- parallel + [order by|group by|join] 시 작동
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Ranger QC 182 19244 1
0 Producer P002 52504 2777440 18
0 Producer P003 54496 2884494 21
0 Consumer P000 53000 2793946 12
0 Consumer P001 54000 2867942 12
1 Producer P000 53000 2793923 0
1 Producer P001 54000 2867919 0
1 Consumer QC 107000 5661842 341
P -> P P -> S
생산자(Producer) 소비자(Consumer) 생산자(Producer) 소비자(Consumer)
P002 (52504 ROWS) P000 (A-M) P000 (A-M) QC
P003 (54496 ROWS) P001 (N-Z) P001 (N-Z)
----------------- ---------- ---------------- ---------------- --------- ---------------------
데이터 추출 테이블큐 정렬 정렬결과셋 테이블큐 생산자로부터 받은 각각의
병렬서버집합 1 : TQ10000 병렬서버집합 2 : TQ10001 정렬 결과를 Merge
- 생산자(Producer) : 작업을 생성, 데이터 추출, 데이터를 추출해서 버퍼에 저장
- 소비자(Consumer) : 작업을 처리, 버퍼에 있는 데이터를 꺼내 소비(정렬, 그룹작업수행)
- 테이블큐(TQ) : 프로세스 간 통신 즉 메시지 또는 데이터를 전송하기 위한 통신, 파이프라인(pipeline), 작업반장
# group by push down 적용
HR@ora11g> select /*+ full(e) parallel(e 2) */ department_id, count(*)
from emp e group by department_id;
12 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last parallel'));
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | | 12 |00:00:00.07 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | 12 |00:00:00.07 | 5 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 11 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | HASH GROUP BY | | 0 | 11 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 1115K| 1115K| 1643K (0)|
| 4 | PX RECEIVE | | 0 | 11 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND HASH | :TQ10000 | 0 | 11 | Q1,00 | P->P | HASH | 0 |00:00:00.01 | 0 | | | |
| 6 | HASH GROUP BY | | 0 | 11 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 4455K| 1486K| 1647K (0)|
| 7 | PX BLOCK ITERATOR | | 0 | 107K| Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 8 | TABLE ACCESS FULL| EMP | 0 | 107K| Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access(:Z>=:Z AND :Z<=:Z)
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P002 12 231 0
0 Producer P003 12 231 0
0 Consumer P000 12 232 3
0 Consumer P001 12 230 3
1 Producer P000 6 70 2
1 Producer P001 6 67 2
1 Consumer QC 12 137 2
- gby_pushdown 힌트 작동
- 원래 수행되어야하는 group by는 ID기준으로 3번이지만 id 6번 먼저 group by 가 수행되었다.
- 그 이유는 id 5번의 : TQ10000에게 데이터를 전달하기전에 데이터를 줄여서 성능을 향상시키기 위함이다.
P->P P->S
Producer Consumer Producer Consumer
P002(12) — P000(ID 10~50) — P000(6) → QC(12)
⨉ ⨉ ↗
P003(12) — P001(ID 60~) — P001(6)
group by group by
+ 추출
# no_gby_pushdown
HR@ora11g> select /*+ full(e) parallel(e 2) no_gby_pushdown */ department_id, count(*)
from emp e group by department_id;
12 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last parallel'));
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | | 12 |00:00:00.05 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | 12 |00:00:00.05 | 5 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 11 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | HASH GROUP BY | | 0 | 11 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 8417K| 3028K| 1393K (0)|
| 4 | PX RECEIVE | | 0 | 107K| Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND HASH | :TQ10000 | 0 | 107K| Q1,00 | P->P | HASH | 0 |00:00:00.01 | 0 | | | |
| 6 | PX BLOCK ITERATOR | | 0 | 107K| Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 7 | TABLE ACCESS FULL| EMP | 0 | 107K| Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P002 52411 209926 14
0 Producer P003 54589 218654 14
0 Consumer P000 97000 388500 8
0 Consumer P001 10000 40080 8
1 Producer P000 6 70 25
1 Producer P001 6 67 5
1 Consumer QC 12 137 3
P->P P->S
Producer Consumer Producer Consumer
P002(52411) — P000(97000) — P000(6) — QC(12)
⨉ ⨉ /
P003(54589) — P001(10000) — P001(6)
추출 group by
[ Sample Table : hr.emp_part, hr.dept_part ]
HR@ora11g> drop table hr.emp_part purge;
HR@ora11g> drop table hr.dept_part purge;
HR@ora11g> create table hr.emp_part
partition by list(department_id)(
partition p_dept_1 values (10,20,30,40),
partition p_dept_2 values (50),
partition p_dept_3 values (60,70,80,90,100,110),
partition p_dept_4 values (default))
as select * from hr.employees;
Table created.
HR@ora11g> create table hr.dept_part
partition by list(department_id)(
partition p_dept_1 values (10,20,30,40),
partition p_dept_2 values (50),
partition p_dept_3 values (60,70,80,90,100,110),
partition p_dept_4 values (default))
as select * from hr.departments;
HR@ora11g> exec dbms_stats.gather_table_stats('hr','emp_part', granularity => 'auto')
PL/SQL procedure successfully completed.
HR@ora11g> exec dbms_stats.gather_table_stats('hr','dept_part', granularity => 'auto')
PL/SQL procedure successfully completed.
HR@ora11g> select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- ------------------ ------------------------- --------------- ---------- ---------- -----------
P_DEPT_1 1 10, 20, 30, 40 USERS 10 19 64
P_DEPT_2 2 50 USERS 45 19 66
P_DEPT_3 3 60, 70, 80, 90, 100, 110 USERS 51 19 71
P_DEPT_4 4 default USERS 1 13 71
HR@ora11g> HR@ora11g> select * from user_part_key_columns where name = 'EMP_PART';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
EMP_PART TABLE DEPARTMENT_ID 1
HR@ora11g> select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'DEPT_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- ------------------ ------------------------- --------------- ---------- ---------- -----------
P_DEPT_1 1 10, 20, 30, 40 USERS 4 19 23
P_DEPT_2 2 50 USERS 1 19 19
P_DEPT_3 3 60, 70, 80, 90, 100, 110 USERS 6 19 19
P_DEPT_4 4 default USERS 16 19 19
HR@ora11g> select * from user_part_key_columns where name = 'DEPT_PART';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
DEPT_PART TABLE DEPARTMENT_ID 1
■ Full Partition Wise Join
- 조인하려는 두 테이블에 조인키 컬럼을 기준으로 파티션 된 경우
- 데이터 재분배가 필요없다.
- pq_distribute(Inner Table(별칭), Outer Table Distribution 방식, Inner Table Distribution 방식)
(재분배) (재분배)
# pq_distribute(e,none,none) : Full Partition Wise Join으로 유도할 때 사용
양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝되어 있을 경우 사용
= 재분배 할 필요 없다. 밤시간 작업
HR@ora11g> select /*+ leading(d,e) use_hash(e) full(d) full(e) parallel(d 2) parallel(e 2)
pq_distribute(e, none, none) */
e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
from hr.dept_part d, hr.emp_part e
where e.department_id = d.department_id;
106 rows selected.
> e.department_id = d.department_id;
> leading(d,e) outer : hr.dept_part
inner : hr.emp_part
> 부서정보가 있는 사원정보 출력
HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 106 |00:00:00.01 | 24 | | | |
| 1 | PX COORDINATOR | | 1 | | | | 106 |00:00:00.01 | 24 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 106 | | | 0 |00:00:00.01 | 0 | | | |
| 3 | PX PARTITION LIST ALL| | 0 | 106 | 1 | 4 | 0 |00:00:00.01 | 0 | | | |
|* 4 | HASH JOIN | | 0 | 106 | | | 0 |00:00:00.01 | 0 | 1135K| 1135K| 753K (0)|
| 5 | TABLE ACCESS FULL | DEPT_PART | 0 | 27 | 1 | 4 | 0 |00:00:00.01 | 0 | | | |
| 6 | TABLE ACCESS FULL | EMP_PART | 0 | 107 | 1 | 4 | 0 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P000 51 1492 2
0 Producer P001 55 1757 2
0 Consumer QC 106 3249 6
Producer Consumer
hr.emp_part(inner)
P000 P_DEPT_3 + P_DEPT_4 = 51 — QC 106
/
P001 P_DEPT_1 + P_DEPT_2 = 55
> 파티션 키컬럼끼지 내부적으로 조인, 어떻게 엮일지는 모름
[ Sample Table : hr.emp_non ]
HR@ora11g> drop table hr.emp_non purge;
Table dropped.
HR@ora11g> create table hr.emp_non as select * from hr.employees;
Table created.
■ Partial Partition Wise Join
- 조인테이블에 한쪽만 파티셔닝된 경우 > 파티션 없는 테이블에 다이나믹 파티션으로 유도
- 파티셔닝이 되어있지 않은 다른쪽 테이블을 같은 기준으로 파티셔닝하고나서 Full Partition Wise Join을 수행한다.
- 동적 파티셔닝을 위한 데이터 재분배가 필요하다.
# pq_distribute(e, none, partition) : inner 테이블은 outer 테이블 파티션 기준에 따라 파티셔닝하라는 의미
당연히 outer 테이블 조회 키 컬럼에 대해 파티셔닝되 있을때 작동된다.
HR@ora11g> select /*+ leading(d,e) use_hash(e) full(d) full(e) parallel(d 2) parallel(e 2)
pq_distribute(e, none, partition) */
e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
from hr.dept_part d, hr.emp_non e
where e.department_id = d.department_id;
106 rows selected.
> outer : dept_part
> inner : emp_non > 다이나믹 파티션 적용(힌트사용)
HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 106 |00:00:00.01 | 15 | | | |
| 1 | PX COORDINATOR | | 1 | | | | 106 |00:00:00.01 | 15 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 106 | | | 0 |00:00:00.01 | 0 | | | |
|* 3 | HASH JOIN BUFFERED | | 0 | 106 | | | 0 |00:00:00.01 | 0 | 1300K| 1300K| 2089K (0)|
| 4 | PX PARTITION LIST ALL | | 0 | 27 | 1 | 4 | 0 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS FULL | DEPT_PART | 0 | 27 | 1 | 4 | 0 |00:00:00.01 | 0 | | | |
| 6 | PX RECEIVE | | 0 | 107 | | | 0 |00:00:00.01 | 0 | | | |
| 7 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 107 | | | 0 |00:00:00.01 | 0 | | | |
| 8 | PX BLOCK ITERATOR | | 0 | 107 | | | 0 |00:00:00.01 | 0 | | | |
|* 9 | TABLE ACCESS FULL | EMP_NON | 0 | 107 | | | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
9 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=2)
- PX SEND PARTITION (KEY) : 동적으로 파티션을 해서 probe에 있는 테이블이 build 테이블과 동일하게 파티션해서 올린다는 의미.
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P002 98 2118 3
0 Producer P003 9 225 0
0 Consumer P000 46 1004 8
0 Consumer P001 61 1339 8
1 Producer P000 45 1416 0
1 Producer P001 61 1833 0
1 Consumer QC 106 3249 1
[ Sample Table : hr.dept_non ]
HR@ora11g> drop table hr.dept_non purge;
Table dropped.
HR@ora11g> create table hr.dept_non as select * from hr.departments;
Table created.
# pq_distribute(e,partition,none)
- Partial Partition Wise Join 일 때 Full Partition Wise Join으로 수행하기 위해서 사용된다.
- Outer Table을 Inner Table 파티션 기준에 따라 파티셔닝하라는 의미
HR@ora11g> select /*+ leading(d,e) use_hash(e) full(d) full(e) parallel(d 2) parallel(e 2)
pq_distribute(e,partition,none) */
e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
from hr.dept_non d, hr.emp_part e
where e.department_id = d.department_id;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 106 |00:00:00.01 | 15 | | | |
| 1 | PX COORDINATOR | | 1 | | | | 106 |00:00:00.01 | 15 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 106 | | | 0 |00:00:00.01 | 0 | | | |
|* 3 | HASH JOIN | | 0 | 106 | | | 0 |00:00:00.01 | 0 | 1061K| 1061K| 1118K (0)|
| 4 | PART JOIN FILTER CREATE | :BF0000 | 0 | 27 | | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX RECEIVE | | 0 | 27 | | | 0 |00:00:00.01 | 0 | | | |
| 6 | PX SEND PARTITION (KEY) | :TQ10000 | 0 | 27 | | | 0 |00:00:00.01 | 0 | | | |
| 7 | PX BLOCK ITERATOR | | 0 | 27 | | | 0 |00:00:00.01 | 0 | | | |
|* 8 | TABLE ACCESS FULL | DEPT_NON | 0 | 27 | | | 0 |00:00:00.01 | 0 | | | |
| 9 | PX PARTITION LIST JOIN-FILTER| | 0 | 107 |:BF0000|:BF0000| 0 |00:00:00.01 | 0 | | | |
| 10 | TABLE ACCESS FULL | EMP_PART | 0 | 107 |:BF0000|:BF0000| 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=2)
- PX SEND PARTITION (KEY) : dept_non에 맞춰 emp_part에 다이나믹 파티션 진행되었구나.
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P002 27 505 1
0 Producer P003 0 40 0
0 Consumer P000 17 347 5
0 Consumer P001 10 198 5
1 Producer P000 45 1416 0
1 Producer P001 61 1833 0
1 Consumer QC 106 3249 1
■ 동적 파티셔닝 : 둘다 파티셔닝 되지 않은 경우
- 어느 한쪽도 조인 컬럼에 대해 파티셔닝이 되지 않은 상황
- 양쪽 테이블이 모두 대용량
# pq_distribute(e,hash,hash)
: 조인 키 컬럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝 하라는 의미
> 일반적으로는 대용량 조인 시 파티션을 만들어 사용한다.
> 대용량 테이블을 동적 파티셔닝 하느니 테이블구조를 바꾸기도 한다.
HR@ora11g> select /*+ leading(d,e) use_hash(e) full(d) full(e) parallel(d 2) parallel(e 2)
pq_distribute(e, hash, hash) */
e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
from hr.dept_non d, hr.emp_non e
where e.department_id = d.department_id;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:00.41 | 8 | | | | |
| 1 | PX COORDINATOR | | 1 | | 106K|00:00:00.41 | 8 | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 106K| 0 |00:00:00.01 | 0 | | | | |
|* 3 | HASH JOIN BUFFERED | | 0 | 106K| 0 |00:00:00.01 | 0 | 1278K| 1278K| 4251K (0)| 3072 |
| 4 | PX RECEIVE | | 0 | 27 | 0 |00:00:00.01 | 0 | | | | |
| 5 | PX SEND HASH | :TQ10000 | 0 | 27 | 0 |00:00:00.01 | 0 | | | | |
| 6 | PX BLOCK ITERATOR | | 0 | 27 | 0 |00:00:00.01 | 0 | | | | |
|* 7 | TABLE ACCESS FULL| DEPT_NON | 0 | 27 | 0 |00:00:00.01 | 0 | | | | |
| 8 | PX RECEIVE | | 0 | 107K| 0 |00:00:00.01 | 0 | | | | |
| 9 | PX SEND HASH | :TQ10001 | 0 | 107K| 0 |00:00:00.01 | 0 | | | | |
| 10 | PX BLOCK ITERATOR | | 0 | 107K| 0 |00:00:00.01 | 0 | | | | |
|* 11 | TABLE ACCESS FULL| EMP_NON | 0 | 107K| 0 |00:00:00.01 | 0 | | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=2)
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P002 27 505 2
0 Producer P003 0 40 0
0 Consumer P000 13 251 137
0 Consumer P001 14 294 139
1 Producer P002 97 2100 1
1 Producer P003 9 225 1
1 Consumer P000 97 2091 138
1 Consumer P001 9 234 139
2 Producer P000 97 2923 0
2 Producer P001 9 326 0
2 Consumer QC 106 3249 1
■ Broadcast : 둘다 파티셔닝 되지 않은 경우
- 어느 한쪽도 조인 컬럼에 대해 파티셔닝이 되지 않은 상황
- 둘 중 하나의 데이터 집합이 매우 작을 때
# pq_distribute(e,broadcast,none) : 작은 테이블을 큰 테이블쪽으로 브로드캐스트한다.
- broadcast 되는 데이터 집합이 아주 작을 때 유용하다.
> 대용량 테이블을 파티션을 하면 Temp 공간을 많이 사용할 수 있으니 Full Table Scan을 하자.
HR@ora11g> select /*+ leading(d,e) use_hash(e) full(d) full(e) parallel(d 2) parallel(e 2)
pq_distribute(e, broadcast, none) */
e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
from hr.dept_non d, hr.emp_non e
where e.department_id = d.department_id;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 6 | | | |
| 1 | PX COORDINATOR | | 1 | | 106 |00:00:00.01 | 6 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 106 | 0 |00:00:00.01 | 0 | | | |
|* 3 | HASH JOIN | | 0 | 106 | 0 |00:00:00.01 | 0 | 1077K| 1077K| 1244K (0)|
| 4 | PX RECEIVE | | 0 | 27 | 0 |00:00:00.01 | 0 | | | |
| 5 | PX SEND BROADCAST | :TQ10000 | 0 | 27 | 0 |00:00:00.01 | 0 | | | |
| 6 | PX BLOCK ITERATOR | | 0 | 27 | 0 |00:00:00.01 | 0 | | | |
|* 7 | TABLE ACCESS FULL| DEPT_NON | 0 | 27 | 0 |00:00:00.01 | 0 | | | |
| 8 | PX BLOCK ITERATOR | | 0 | 107 | 0 |00:00:00.01 | 0 | | | |
|* 9 | TABLE ACCESS FULL | EMP_NON | 0 | 107 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - access(:Z>=:Z AND :Z<=:Z)
9 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=2)
> HASH JOIN > build : dept_non 총 27행
probe : emp_non 총 107행
HR@ora11g> select tq_id, server_type, process, num_rows, bytes, waits
from v$pq_tqstat
order by tq_id, decode(substr(server_type,1,4),'Rang',1,'Prod',2,'Cons',3),process;
TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS
---------- ---------------------------------------- ------------------------ ---------- ---------- ----------
0 Producer P002 54 970 1
0 Producer P003 0 40 0
0 Consumer P000 27 505 37
0 Consumer P001 27 505 39
1 Producer P000 97 2954 1
1 Producer P001 9 295 1
1 Consumer QC 106 3249 4
> P003 : 0 row -> 데이터가 작아서 P002만 일하고 있다.
> dept_non 작은 테이블을 브로드캐스트한다.
Producer Consumer Producer Consumer
dept_non hash Table JOIN emp_non TQ10001
P002(54) — P000(27) — P000(97) — QC(106)
＼ Ⅹ /
P003(0) P001(27) — P001(9)
> P002는 P000, P001에 각각 dept_non의 Hash Table 생성한 후 emp_non에서 P000(97), P001(9)를 가져와 QC에게 보낸다.
'Oracle 11g > Tuning' 카테고리의 다른 글
| 95-1 DML작업 병렬처리 (0) | 2024.02.29 |
|---|---|
| 93 Arraysize, Partition Pruning, Local/Global Partitioned Indexes (1) | 2024.02.27 |
| 92 Partition (1) | 2024.02.26 |
| 91 통계정보 (0) | 2024.02.23 |
| 90 Pushdown, Pullup, Use_concat, Optimazer (0) | 2024.02.22 |