728x90
반응형
SMALL
■ 병렬처리
- SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스가 동시에 처리하는 작업을 의미한다.
#) 테이블 삭제
<hr sess>
drop table hr.emp purge;=> Table dropped.
#) 테이블 생성
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 employees e, (select level as id from dual connect by level <= 1000);=> Table created.
#) 통계수집
- degree=>2: 대부분은 짝수로
exec dbms_stats.gather_table_stats('hr', 'emp', degree=>2)=> PL/SQL procedure successfully completed.
#) 통계정보 확인
select num_rows, blocks, avg_row_len, logging from user_tables where table_name = 'EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN LOG
---------- ---------- ----------- ---
107000 760 45 NO
tip) 대용량 data를 이관작업을 하기 위해서는 nologging 모드로 작업 한 후에 logging 모드로 변경하기.
#) logging 모드로 변경
alter table hr.emp logging;=> Table altered.
#) logging 모드인지 확인
select num_rows, blocks, avg_row_len, logging from user_tables where table_name = 'EMP'; NUM_ROWS BLOCKS AVG_ROW_LEN LOG
---------- ---------- ----------- ---
107000 760 45 YES
#) arraysize 확인
show arraysize
arraysize 15
#) 1000 변경
set arraysize 1000
show arraysize
arraysize 1000
#) 모든 통계 수집 가능
alter session set statistics_level = all;=> Session altered.
# serial full table scan
#) count, full table scan 유도
select /*+ full(e) */ count(*) from hr.emp e; COUNT(*)
----------
107000
#) 실행계획 확인
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 방식
- 대용량 테이블 이용에 용이함.(read성 테이블이 대다수)
- 더티버퍼가 있다면? 대기이벤트가 발생할 수 있다.
#) 병렬처리 힌트 사용
select /*+ full(e) parallel(e 2) */ count(*) from hr.emp e; COUNT(*)
----------
107000
#) 실행계획 확인
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)
[해석]
SORT AGGREGATE: 그룹함수 사용
QC: Query Coordinator
- 병렬 SQL문을 발생한 세션, 서버프로세스
- 작업을 지시하고 일이 잘 진행되는지 관리, 감독하는 역할
#) 병렬 서버 프로세스
- 실제 작업을 수행하는 개별 세션
- 병렬 서버 = 병렬 프로세서 = 병렬 슬레이브
#) 병렬 서버 풀(parallel execution server pool)
- 병렬 처리 시, 서버풀에 있는 프로세스 부터 사용
- 부족하면 oracle에서 부족분을 추가 생성한다.
#1) 생성할 수 있는 최대 병렬 서버 개수
<sys sess>
show parameter parallel_max_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 80
#2) 병렬처리가 발생하지 않더라도 parallel_min_servers 파라미터 지정된 개수 만큼 병렬 프로세스를 유지한다.
-> why? 병렬처리시, 서버 프로세스 띄우는 부하를 줄이기 위함.
-> value 에 값이 있다면, 미리 갖고 와서 사용하겠다는 의미.
show parameter parallel_min_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 0
#) 병렬 서버 집합(server set) 할당
- 병렬도:DOP(Degree Of Parallelism)와 오퍼레이션 종류와 따라 한 개 또는 2개의 병렬 서버 집합 할당
- 서버 풀로부터 필요한 만큼 서버 프로세스 확보
- 만약 부족하면? 부족분은 새로 생성한다.
■ IN-OUT 오퍼레이션
1. blank(공백공간): serial, 직렬(병렬처리X)
2. P→S: Parallel_To_Serial, 각 병렬 서버 프로세스가 처리한 데이터를 QC에게 전송,
QC (RAND): ORDER BY 절 없을 경우에 표시되며 병렬 프로세스들이 무순위로 QC 에게 데이터 전송
3. PCWP: Parallel_Combinded_With_Parent, 한 서버집합이 현재 스텝과 그 부모 스텝을 모두 처리.
4. PCWC: Parallel_Combinded_With_Child, 한 서버집합이 현재 스텝과 그 자식 스텝을 모두 처리.
5. S→P: Parallel_From_Serial, QC가 읽은 데이터를 테이블 큐를 통해 병렬 서버 프로세스에게 전송.
6. P→P: Parallel_To_Serial, 데이터 정렬, 그룹핑하거나 조인을 위해 동적으로 파티셔닝할 때 사용
첫번째 병렬 서버 집합이 읽거나 가공한 데이터를 두번째 병렬 서버 집합에 전송.
# 병렬도 = 2
# 서버 프로세스 = 병렬도*2 = 2*2 = 4
# 통신 채널 수 = 병렬도²(제곱) = 2² = 4
#) 대량의 테이블, order by 절 사용
select /*+ full(e) parallel(e 2) */ *
from hr.emp e
order by last_name;=> 107000 rows selected.
#) 실행 계획 확인
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.14 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | 107K|00:00:00.14 | 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| 3448K (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)
#) 병렬처리 정보 확인
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 19176 1
0 Producer P002 44018 2333810 18
0 Producer P003 62982 3329144 26
0 Consumer P000 53000 2794966 3
0 Consumer P001 54000 2867942 3
1 Producer P000 53000 2794943 0
1 Producer P001 54000 2867919 0
1 Consumer QC 107000 5662862 294
| Producer (생산자) |
P→P | Consumer (소비자) |
P→S | Producer (생산자) |
Consumer (소비자) |
| P002 | P000 (A-M) | P000 | QC | ||
| P003 | P001 (N-Z) | P001 | |||
| 데이터 추출 | 테이블큐 | 데이터 정렬 | 테이블큐 | 정렬결과셋 | 생산자로부터 받은 데이터를 각각 정렬 결과를 merge |
| 병렬 서버 집합 1 | :TQ10000 | 병렬 서버 집합 2 | :TQ10001 |
[해석]
- order by절, group by절, join 동일
Producer: 생산자, 작업을 생성, 데이터 추출, 데이터를 추출해서 버퍼에 저장
Consumer: 소비자, 작업을 처리, 버퍼에 있는 데이터를 꺼내 소비(정럴, 그룹작업수행)
테이블큐: 프로세스 간 통신. 즉, 메시지 또는 데이터를 전송하기 위한 통신, 파이프라인(pipeline)
# group by pushdown 적용
- 원래 수행되어야 하는 group by는 id 기준으로는 3번이지만 id 6번에서 먼저 group by가 수행 되어있다.
그 이유는 id 5번의 :TQ10000에게 데이터를 전달하기 전에 다음 스텝인 P000, P001에게 데이터를 줄여서 보내는 것이 성능을 향상시키기 위함이다.
#) 대량의 테이블, group by 절 사용
select /*+ full(e) parallel(e 2) */ department_id, count(*)
from hr.emp e
group by department_id;
DEPARTMENT_ID COUNT(*)
------------- ----------
1000
20 2000
90 3000
110 2000
40 1000
10 1000
100 6000
30 6000
70 1000
50 45000
80 34000
60 5000
#) 실행 계획 확인
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.02 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | 12 |00:00:00.02 | 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| 875K (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 | 4641K| 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)
#) 병렬처리 과정 정보 확인
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 -- 1차 group by
0 Producer P003 12 231 0
0 Consumer P000 12 232 11 -- 2차 group by
0 Consumer P001 12 230 11
1 Producer P000 6 70 2 -- 데이터 결과값
1 Producer P001 6 67 3 -- 데이터 결과값
1 Consumer QC 12 137 3 -- 데이터 QC 전달 -> user 전달
#) 힌트 사용: no_gby_pushdown
- no_gby_pushdown: group by 을 한번만
select /*+ full(e) parallel(e 2) no_gby_pushdown */ department_id, count(*)
from hr.emp e
group by department_id;
#) 실행 계획 확인
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.03 | 5 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | 12 |00:00:00.03 | 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)
#) 병렬처리 과정 정보 확인
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 52517 210350 13 -- 데이터 추출
0 Producer P003 54483 218230 13
0 Consumer P000 97000 388500 121 -- 데이터 group by
0 Consumer P001 10000 40080 121
1 Producer P000 6 70 22 -- 데이터 결과값
1 Producer P001 6 67 6 -- 데이터 결과값
1 Consumer QC 12 137 3 -- 데이터 QC 전달 -> user 전달728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| RAC 설치 과정 (0) | 2024.03.02 |
|---|---|
| 재분배 방식, pq_distribute (0) | 2024.02.28 |
| local partition index, global partition index, rebuild (0) | 2024.02.27 |
| 정적(static) 파티션 pruning, 동적(dynamic) 파티션 pruning (0) | 2024.02.27 |
| 부분 범위 처리, 운반 단위, arraysize 조절 (1) | 2024.02.27 |