트러블슈팅 오라클 퍼포먼스 2판 (2017년)
병렬 처리
병렬 쿼리
병렬 쿼리
SELECT /*+ leading(t1) use_hash(t2)
index(t1) parallel_index(t1 2)
full(t2) parallel(t2 2)
pq_distribute(t2 hash, hash) */ *
FROM t1, t2
WHERE t1.id > 9000
AND t1.id = t2.id + 1
;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart | Pstop | TQ | IN-OUT | PQ Distri |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | | | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | | | Q1,00 | P->P | HASH |
| 6 | PX PARTITION HASH ALL | | 1 | 4 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS BY INDEX ROW| T1 | | | Q1,00 | PCWP | |
|* 8 | INDEX RANGE SCAN | T1 | 1 | 4 | Q1,00 | PCWP | |
| 9 | PX RECEIVE | | | | Q1,02 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | | | Q1,01 | P->P | HASH |
| 11 | PX BLOCK ITERATOR | | | | Q1,01 | PCWC | |
|* 12 | TABLE ACCESS FULL | T2 | | | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------
병렬 DML
병렬 DML
ALTER SESSION DISABLE PARELLEL QUERY;
ALTER SESSION DISABLE PARELLEL DML;
ALTER TABLE t PARELLEL 2;
UPDATE t SET id = id + 1;
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | |
| 2 | TABLE ACCESS FULL | T |
------------------------------------
병렬 DML
ALTER SESSION ENABLE PARELLEL QUERY;
ALTER SESSION DISABLE PARELLEL DML;
ALTER TABLE t PARELLEL 2;
UPDATE t SET id = id + 1;
------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distri |
------------------------------------------------------------------------
| 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 | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T | Q1,00 | PCWP | |
------------------------------------------------------------------------
병렬 DML
ALTER SESSION ENABLE PARELLEL QUERY;
ALTER SESSION ENABLE PARELLEL DML;
ALTER TABLE t PARELLEL 2;
UPDATE t SET id = id + 1;
------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distri |
------------------------------------------------------------------------
| 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 | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T | Q1,00 | PCWP | |
------------------------------------------------------------------------
CTAS
- CREATE TABLE AS SELECT
삽입의 병렬화
CREATE TABLE t2 PARALLEL 2
AS
SELECT /*+ no_parallel(t1) */ * FROM t1
;
--------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distri |
--------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T2 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | | S->P | |
| 6 | TABLE ACCESS FULL | T1 | | | |
--------------------------------------------------------------------------
쿼리의 병렬화
CREATE TABLE t2 NOPARALLEL
AS
SELECT /*+ parallel(t1) */ * FROM t1
;
--------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distri |
--------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | LOAD AS SELECT | T2 | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
--------------------------------------------------------------------------
둘다 병렬화
CREATE TABLE t2 PARALLEL 2
AS
SELECT /*+ parallel(t1) */ * FROM t1
;
--------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distri |
--------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T2 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
--------------------------------------------------------------------------
-- 12C --
CREATE /*+ parallel(2) */ TABLE t2 AS SELECT * FROM t1;
인덱스 생성과 재생성
인덱스 생성
CREATE INDEX i1 ON t1(id) PARALLEL 4;
-------------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distrib |
-------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE | T1 | Q1,01 | PCWP | |
| 4 | SORT CREATE INDEX | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------
인덱스 재생성
ALTER INDEX i1 REBUILD PARALLEL 4;
-------------------------------------------------------------------------------
| Id | Operation | Name | TQ | IN-OUT | PQ Distrib |
-------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE | T1 | Q1,01 | PCWP | |
| 4 | SORT CREATE INDEX | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | INDEX FAST FULL SCAN | T1 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------
제약조건의 생성과 검증
- 제약조건 생성시 검증용 재귀 호출 쿼리 병렬 수행
- 기본 키 정의시 인덱스 생성 병렬 수행 못함
- 인덱스를 미리 병렬로 생성 한 뒤 기본키 생성
사용시기
- 가용한 리소스가 충분한 경우
- 순차적 실행시 10초 이상 소요되는 SQL
함정과 착오
parallel 힌트는 강제성 없음
-- 병렬도에 따른 코스트 확인 --
EXPLAIN PLAN SET STATEMENT_ID 'dop1' FOR SELECT /*+ full(t) palallel(t 1) */ * FROM t WHERE id > 93000;
EXPLAIN PLAN SET STATEMENT_ID 'dop2' FOR SELECT /*+ full(t) palallel(t 2) */ * FROM t WHERE id > 93000;
EXPLAIN PLAN SET STATEMENT_ID 'dop3' FOR SELECT /*+ full(t) palallel(t 3) */ * FROM t WHERE id > 93000;
EXPLAIN PLAN SET STATEMENT_ID 'dop4' FOR SELECT /*+ full(t) palallel(t 4) */ * FROM t WHERE id > 93000;
SELECT statement_id, cost
FROM plan_table
WHERE statement_id LIKE 'dop%'
AND id = 0
;
STATEMENT_ID COST
------------ ----
dop1 296
dop2 164
dop3 110
dop4 82
-- 힌트 없이 쿼리 수행 --
SELECT * FROM t WHERE id > 93000;
----------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | T | 125 (0)|
|* 2 | INDEX RANGE SCAN | I | 17 (0)|
----------------------------------------------------------
-- 병렬도를 바꿔 가며 쿼리 수행 --
SELECT /*+ palallel(t 2) */ * FROM t WHERE id > 93000;
----------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | T | 125 (0)|
|* 2 | INDEX RANGE SCAN | I | 17 (0)|
----------------------------------------------------------
SELECT /*+ palallel(t 3) */ * FROM t WHERE id > 93000;
----------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELETE STATEMENT | | 110 (1)|
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 110 (1)|
| 3 | PX BLOCK ITERATOR | | 110 (1)|
|* 4 | TABLE ACCESS FULL | T | 110 (1)|
----------------------------------------------------------
-- 정리하면
-- parallel 힌트는 병렬처리를 고려하도록 할 뿐 강제하지는 않는다.
병렬처리 함정과 착오
- parallel 힌트는 병렬처리를 고려하도록 할 뿐 강제하지는 않는다.
- 효율적인 병렬처리를 위해서는 전체 슬레이브 프로세스 사이의 작업량이 균등하게 분배되는 것이 중요하다.
- 슬레이브 프로세스는 각각 세션을 맺는다.
- 병렬DML 구문 수행 후에 커밋/롤백 하기 전에 동일 세션에서 Select 불가.
- ora-12838: cannot read/modify an object after modifying it in parallel
- 다른 세션에서는 커밋 전 데이터 조차 볼 수 없다.
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition)
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4405
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.