728x90
반응형
SMALL
#) 파티션 테이블 삭제
drop table hr.emp_part purge;
drop table hr.dept_part purge;
#) 리스트 파티션 테이블 생성: emp_part
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.
#) 리스트 파티션 테이블 생성: dept_part
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;=> Table created.
#) 통계 수집, granularity 작업
exec dbms_stats.gather_table_stats('hr', 'emp_part', granularity=>'auto')
exec dbms_stats.gather_table_stats('hr', 'dept_part', granularity=>'auto')=> PL/SQL procedure successfully completed.
#) partition 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_PART';PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- ------------------------- ---------------- ---------- ---------- -----------
P_DEPT_1 10, 20, 30, 40 USERS 10 19 64
P_DEPT_2 50 USERS 45 19 66
P_DEPT_3 60, 70, 80, 90, 100, 110 USERS 51 19 71
P_DEPT_4 default USERS 1 19 71
#) key 확인
select * from user_part_key_columns where name = 'EMP_PART';NAME OBJEC COLUMN_NAME COLUMN_POSITION
--------------- ----- ------------------------------ ---------------
EMP_PART TABLE DEPARTMENT_ID 1
#) partition 확인 dept_part
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'DEPT_PART';PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- ------------------------- ---------------- ---------- ---------- -----------
P_DEPT_1 10, 20, 30, 40 USERS 4 19 23
P_DEPT_2 50 USERS 1 19 19
P_DEPT_3 60, 70, 80, 90, 100, 110 USERS 6 19 19
P_DEPT_4 default USERS 16 19 19
#) key 확인
select * from user_part_key_columns where name = 'DEPT_PART';NAME OBJEC COLUMN_NAME COLUMN_POSITION
--------------- ----- ------------------------------ ---------------
DEPT_PART TABLE DEPARTMENT_ID 1
■ full partition wise join
- 조인하려는 두 테이블에 조인키 컬럼을 기준으로 파티션된 경우
- 데이터 재분배가 필요 없다.
- 힌트: pq_distribute(e,none,none)
# pq_distribute('inner table or 별칭', 'outer table distribute 방식', 'inner table distribute 방식')
- pq_distribute(): full partition wise join 조인으로 유도할때 사용.
- 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티션 되어 있을 경우 사용.
#) join 처리
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 dept_part d, emp_part e
where e.department_id = d.department_id;
=> 106 rows selected.
#) 실행계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.02 | 24 | | | |
| 1 | PX COORDINATOR | | 1 | | 106 |00:00:00.02 | 24 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 106 | 0 |00:00:00.01 | 0 | | | |
| 3 | PX PARTITION LIST ALL| | 0 | 106 | 0 |00:00:00.01 | 0 | | | |
|* 4 | HASH JOIN | | 0 | 106 | 0 |00:00:00.01 | 0 | 1135K| 1135K| 1123K (0)|
| 5 | TABLE ACCESS FULL | DEPT_PART | 0 | 27 | 0 |00:00:00.01 | 0 | | | |
| 6 | TABLE ACCESS FULL | EMP_PART | 0 | 107 | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
#) 병렬처리 과정 정보 확인
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 45 1416 1
0 Producer P001 61 1834 2
0 Consumer QC 106 3250 3
■ partial partition wise join
- 조인 테이블에 한쪽만 파티션된 경우
- 파티션이 되어 있지 않은 다른 쪽 테이블을 같은 기준으로 파티션하고 나서 partial partition wise join 을 수행한다.
(파티션이 되어 있지 않은 테이블 과 파티션이 되어 있는 테이블과의 partial partition wise join 을 수행.)
- 동적인 파티션을 위한 데이터 재분배가 필요하다.
#) 테이블 삭제
drop table hr.emp_non purge;=> Table dropped.
#) 파티션 되어 있지 않는 테이블 생성
create table hr.emp_non as select * from hr.employees;=> Table created.
#) join 처리, pq_distribute(inner 테이블, outer table 재분배 방식, inner table 재분배 방식)
[해석] pq_distribute(e,none,paratition)
: inner 테이블은 outer 테이블 파티션 기준에 따라 파티션하라.
- outer 테이블 조인 키 컬럼에 대해 파티션 되어 있을 때 작동된다.
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 dept_part d, emp_non e
where e.department_id = d.department_id;=> 106 rows selected.
#) 실행계획 확인
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.03 | 15 | | | |
| 1 | PX COORDINATOR | | 1 | | | | 106 |00:00:00.03 | 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 | 1238K| 1238K| 2586K (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)-> PX SEND PARTITION (KEY): 동적 파티션으로 돌아간다는 의미
#) 병렬처리 과정 정보 확인
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 2119 2
0 Producer P003 9 225 2
0 Consumer P000 46 1004 3
0 Consumer P001 61 1340 3
1 Producer P000 45 1416 0
1 Producer P001 61 1834 0
1 Consumer QC 106 3250 1
#) dept_non 생성
create table hr.dept_non as select * from hr.departments;=> Table created.
#) join, 힌트 사용
[해석] pq_distribute(e,partition, none)
: partial patition wise join 일 때, full partition wise join 으로 수행하기 위해서 사용된다. outer table을 inner table 파티션 기준에 따라 파티션 하라.
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 dept_non d, emp_part e
where e.department_id = d.department_id;=>106 rows selected.
#) 실행 계획 확인
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 | 1114K| 1114K| 877K (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)
#) 병렬처리 과정 정보 확인
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 33
0 Consumer P001 10 198 33
1 Producer P000 45 1416 0
1 Producer P001 61 1834 0
1 Consumer QC 106 3250 1
■ 둘다 파티셔닝 되지 않은 경우: 동적 파티션닝
- 어느 한쪽도 조인 컬럼에 대해 파티셔닝이 되지 않은 상황
- (전제조건) 양쪽 테이블이 모두 대용량일 경우
#) 파티션이 되지 않는 빅테이블을 join한다면?
[해석] pq_distribute(e,hash,hash)
: 조인 키 컬럼을 해시 함수에 적용하고 거시서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝 하라.
- 나눠서 하는 작업이 best!, temp 공간 많이 잡는다.
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 dept_non d, emp_non e
where e.department_id = d.department_id;
#) 실행 계획 확인
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.03 | 6 | | | |
| 1 | PX COORDINATOR | | 1 | | 106 |00:00:00.03 | 6 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 106 | 0 |00:00:00.01 | 0 | | | |
|* 3 | HASH JOIN BUFFERED | | 0 | 106 | 0 |00:00:00.01 | 0 | 1238K| 1238K| 2019K (0)|
| 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 | 107 | 0 |00:00:00.01 | 0 | | | |
| 9 | PX SEND HASH | :TQ10001 | 0 | 107 | 0 |00:00:00.01 | 0 | | | |
| 10 | PX BLOCK ITERATOR | | 0 | 107 | 0 |00:00:00.01 | 0 | | | |
|* 11 | 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)
11 - access(:Z>=:Z AND :Z<=:Z)
#) 병렬처리 과정 정보 확인
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 13 251 6
0 Consumer P001 14 294 6
1 Producer P002 97 2101 1
1 Producer P003 9 225 1
1 Consumer P000 97 2091 6
1 Consumer P001 9 235 7
2 Producer P000 97 2923 0
2 Producer P001 9 327 0
2 Consumer QC 106 3250 1
■ 둘다 파티셔닝 되지 않은 경우: broadcast
- 어느 한쪽도 조인 컬럼에 대해 파티셔닝이 되지 않은 상황
- (전제조건) 둘 중 하나의 데이터 집합이 매우 작을 경우
- 데이터가 작은 테이블을 파티션 하면? 괜한 temp 공간만 사용할 수 있다.
- broadcast는 데이터가 작은 테이블이여야 유용하다.
[해석] pq_distribute(e,broadcast,none)
: 작은 테이블을 큰 테이블로 브로드캐스드 하라.
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 dept_non d, emp_non e
where e.department_id = d.department_id;
#) 실행 계획 확인
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.03 | 6 | | | |
| 1 | PX COORDINATOR | | 1 | | 106 |00:00:00.03 | 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)
#) 병렬처리 과정 정보 확인
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 2
0 Producer P003 0 40 0
0 Consumer P000 27 505 3
0 Consumer P001 27 505 3
1 Producer P000 97 2955 1
1 Producer P001 9 295 1
1 Consumer QC 106 3250 4
728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| RAC 설치과정 2 (0) | 2024.03.02 |
|---|---|
| RAC 설치 과정 (0) | 2024.03.02 |
| Parallel, 병렬 처리 (0) | 2024.02.28 |
| local partition index, global partition index, rebuild (0) | 2024.02.27 |
| 정적(static) 파티션 pruning, 동적(dynamic) 파티션 pruning (0) | 2024.02.27 |