병렬 쿼리 튜닝
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 병렬 쿼리 튜닝[편집]
1.1 병렬처리 핵심은?[편집]
1.1.1 그래뉼[편집]
android The basic unit of work in parallelism is a called a granule.
Oracle Database divides the operation executed in parallel (for example, a table scan, table update, or index creation) into granules.
- 병렬로 처리할때 일의 최소 단위
- 병렬 서버는 한번에 하나의 그래뉼씩 처리 함
- 그래뉼 갯수와 크기는 병렬도와 관련되고 분산처리에 영향을 미침
- 블록 그래뉼 과 파티션 그래뉼로 나뉨
- 병렬 쿼리 granule
1.1.1.1 블록 그래뉼[편집]
- PX BLOCK ITERATOR 라고 표시
- QC는 테이블로부터 읽어야할 범위의 블록 GRANULE로서 각 병렬 서버에게 할당
- 파티션 여부,파티션 갯수 와 무관하게 병렬도 지정이 가능
-------------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost%CPU| Time |Pst|Pst| TQ |INOUT|PQDistri|
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 17| 153 |565(100)|00:00:07| | | | | |
| 1| PX COORDINATOR | | | | | | | | | | |
| 2| PX SEND QC(RANDOM) |:TQ10001| 17| 153 |565(100)|00:00:07| | |Q1,01|P->S |QC(RAND)|
| 3| HASH GROUP BY | | 17| 153 |565(100)|00:00:07| | |Q1,01|PCWP | |
| 4| PX RECEIVE | | 17| 153 |565(100)|00:00:07| | |Q1,01|PCWP | |
| 5| PX SEND HASH |:TQ10000| 17| 153 |565(100)|00:00:07| | |Q1,00|P->P | HASH |
| 6| HASH GROUP BY | | 17| 153 |565(100)|00:00:07| | |Q1,00|PCWP | |
==========> 블록 이터레이터로 표시됨
| 7| PX BLOCK ITERATOR | | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWC | |
|*8| TABLE ACCESS FULL| SALES | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWP | |
-------------------------------------------------------------------------------------------------
1.1.1.2 파티션 그래뉼[편집]
- PX PARTITION RANGE ALL
- 전체 파티션을 읽을때 표시
- PX PARTITION RANGE ITERATOR 라고 표시
- 일부 파티션만 읽을 때 표시
- 사용되는 시기
- Partition-Wise 조인 시
- 파티션 인덱스를 병렬로 스캔할 시
- 파티션 인덱스를 병렬로 갱신할 때
- 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때
- 병렬도는 파티션 갯수 이하로만 지정 할수 있음(튜닝 요소)
- 1개 파티션을 2개의 프로세스가 함께 처리 할수 없음
- 예시) WHERE 조건에 파티션 컬럼이 1개만 타도록 제한된 경우 아래 예시 참조
- 병렬 서버가 한 파티션 처리를 끝마치면 다른 파티션을 할당 받아서 진행 함(병렬도가 파티션 갯수 보다 적을때)
---------------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Byte|Cost%CPU| Time |Ps|Ps| TQ |INOU|PQDistri|
---------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 17| 153| 2(50)|00:00:01| | | | | |
| 1| PX COORDINATOR | | | | | | | | | | |
| 2| PX SEND QC(RANDOM) |:TQ10001| 17| 153| 2(50)|00:00:01| | |Q1,01|P->S|QC(RAND)|
| 3| HASH GROUP BY | | 17| 153| 2(50)|00:00:01| | |Q1,01|PCWP| |
| 4| PX RECEIVE | | 26| 234| 1(0)|00:00:01| | |Q1,01|PCWP| |
| 5| PX SEND HASH |:TQ10000| 26| 234| 1(0)|00:00:01| | |Q1,00|P->P| HASH |
==========> 파티션 RANGE ... 로 표시됨
| 6| PX PARTITION RANGE ALL | | 26| 234| 1(0)|00:00:01| | |Q1,00|PCWP| |
| 7| TABLEACCESSLOCAL INDEX ROWID|SALES| 26| 234| 1(0)|00:00:01| 1|16|Q1,00|PCWC| |
|*8| INDEX RANGE SCAN |SALES_CUST|26| | 1(0)|00:00:01| 1|16|Q1,00|PCWP| |
---------------------------------------------------------------------------------------------------
1.1.2 파티션 와이즈 조인[편집]
- 기본적인 원리는 커다란 하나의 조인을 분할하여 여러개의 작은 조각으로 나누는것
- 머지조인, 해시조인시 적용하는 최적화 기법
- 파티션 와이즈 조인은 파티션 테이블이 필수 임
- 조인처리에 사용되는 cpu,memory,네트워크 리소스를 줄이는 방법임
- 조인 과 병렬도 성능 비교
1.1.2.1 풀 파티션 와이즈 조인[편집]
- Full Partition Wise Join (완전 동등하게 조인)
- 동등하게 파티션된 2개 테이블을 조인 함
- 대규모 조인이 예상되는 테이블은 동등한 파티션으로 설계할것을 모델링 단계에서 고려할것
- (주의사항) 리스트 파티션은 동일 갯수,동일 순서가 맞도록 파티션 되어야 함
- 서브파티션과도 조인 가능
1.1.2.2 부분 파티션 와이즈 조인[편집]
- Patial Patition Wise Join (부분)
- 병렬로만 수행이 가능함
- 한쪽 테이블만 조인키 기준으로 파티션 된 경우임
- 부분 파티션 조인시 일반 조인보다는 빠르지만 큰 성능 향상을 기대하기는 어렵다.(오히려 성능이 나빠질수도 있다)
1.1.3 생산자 VS 소비자[편집]
- intra vs. inter
- intra : 내부의(within)
- inter : ~와 연관된(among,between)
- 진행순서
- [1] S -> P : QC가 읽은 데이터를 테이블큐를 통해서 병렬 프로세스로 전송 하는것, 직렬(serial) 오퍼레이션
- : Paralell_From_Serial
- [2] P -> P : 데이터 재분배, 병렬도가 2배가 됨. Inter Operation Parallelism , 병렬 오퍼레이션
- : Parallel_To_Parallel
- [3] P -> S : 각 병렬서버가 처리한 데이터를 QC에게 전송 하는 것, 병렬 오퍼레이션
- : Parallel_To_Serial
1.1.4 DOP 많을수록 좋을까?[편집]
1.1.4.1 DOP 32 =>> DOP 16 으로 줄여서 성능개선[편집]
- 튜닝 전
MERGE /*+ENABLE_PARALLEL_DML PARALLEL(32) FULL(T) USE_HASH(T)*/
INTO TB_TEST1 T
USING (
SELECT PLAN_ID
, GRFNLID
, IDMB_ID
, SBWDC_ID
.... 생략 ....
- 튜닝 후
- - 분배 방식 및 ACCESS 방식 변경을 위한 힌트 추가.
- 약 1천 9백만건 이상 MERGE 되는 대량 DML 문으로 원본도 병렬 처리가 잘되고 있어 크게 개선 될 포인트는 없음.
- parallel degree는 16으로 내렸으며 약 583초(8분23초) 수행.(변경전 700초)
- SQL 변경
MERGE /*+ ENABLE_PARALLEL_DML PARALLEL(T 16) LEADING(S) FULL(T) USE_HASH(T) PQ_DISTRIBUTE(T HASH HASH) */
INTO TB_TEST1 T
USING (
SELECT PLAN_ID
, GRFM_ID
, IDM8_ID
.... 생략 ....
1.1.6 병렬 PLAN 해석하는 방법[편집]
-- 병렬 쿼리 확인
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST ADAPTIVE PARTITION PARALLEL OUTLINE'));
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1638 | 7 (29)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 14 | 1638 | 7 (29)| 00:00:01 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 14 | 1638 | 7 (29)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 14 | 1638 | 6 (17)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 14 | 1638 | 6 (17)| 00:00:01 | Q1,01 | P->P | RANGE |
|* 6 | HASH JOIN | | 14 | 1638 | 6 (17)| 00:00:01 | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 8 | PX RECEIVE | | 4 | 120 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 4 | 120 | 3 (0)| 00:00:01 | | S->P | BROADCAST |
| 10 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | | |
| 11 | PX BLOCK ITERATOR | | 14 | 1218 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL | EMP | 14 | 1218 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
1.1.6.1 SQL 플랜상 튜닝 검토 사항[편집]
android # broadcast 는 소량테이블에 적합(임시로 생성한 테이블에 통계정보가 있는지 확인필요)
- hash 는 대량 테이블에 적합
- S->P 는 튜닝 대상임. P->P로 바꿀수 있는 방법을 검토.sql수정도 좋다.
- round-robin 은 튜닝 대상임
- merge 나 insert ,delete문에만 주로 Parallel 힌트 사용. 하위 select 문에서는 가급적 지양 , 1:1 관계를 지향한다
- part key 플랜도 튜닝 대상. pq_disiribute(A hash hash) 힌트로 변경 검토
- 오라클은 내부적 으로 어떤 힌트를 사용하고 있는지 볼까 ?
- OUTLINE
1.2 병렬처리 올바른 사용법은?[편집]
- 병렬도를 같게 지정하는 것이 바람직 함.
- 테이블별 개별 힌트, PARALLEL(A 8) 보다 글로벌 힌트, PARALLEL(8)로 적용
1.2.1 병렬 힌트를 어디에 어떻게 써야 하나?[편집]
1.2.2 DDL, DML 병렬처리[편집]
- DML
- SELECT
SELECT
-- 튜닝 전
/* FULL(BA) FULL(BB) FULL(BC) FULL(BD)
PARALLEL(BA 16)
PARALLEL(BB 16)
PARALLEL(BC 16)
PARALLEL(BD 16) */
-- 튜닝 후
/*+ PARALLEL(16)
LEADING(BB) USE HASH(BA BB BC BD )
FULL(BA) PO_DISTRIBUTE(BA HASH HASH) NO_SWAP_JOIN INPUTS(BA)
FULL(BB) PO_DISTRIBUTE(BB HASH HASH) SWAP_ JOIN_INPUTS(BB)
FULL(BC) PQ_DISTRIBUTE(BC HASH HASH) NO_SWAP_JOIN_INPUTS(BC)
FULL(BD) PQ_DISTRIBUTE(BD HASH HASH) NO_SWAP_JOIN_INPUTS (BD)
*/
....
FROM TB_MBR_MANT -- 20G
, TB_FM. BB -- 5G
, TB_FMBR BC -- 8G
, TB_IDM BD -- 13G
WHERE 1 = 1
AND BA.Q_ID IN (
SELECT DISTINCT O_ID
......
- INSERT
- 병렬 힌트 적용 되는 경우
- INSERT
INSERT /*+ PARALLEL (4) ENABLE_PARALLEL_DML */ -- PQ_DISTRIBUTE(T NONE)
INTO TB_WM_DTLS T
- 병렬 힌트 적용 안되는 경우
-- insert 앞에 힌트
/*+ PARALLEL (4) ENABLE_PARALLEL_DML*/ -- PQ_DISTRIBUTE(T NONE)
INSERT INTO
TB_XM_DTLS T
....
-- into 뒤에 힌트
INSERT INTO /*+ PARALLEL (4) ENABLE_PARALLEL_DML */ -- PQ_DISTRIBUTE(T NONE)
TB_XM_DTLS T
....
- UPDATE
- DELETE
- DDL
- : CREATE TABLE [테이블명] (.......) PARALLEL ( DEGREE [병렬범위] ) ;
- : CREATE TABLE [인덱스명] PARALLEL ( DEGREE [병렬범위] ) ;
- : ALTER TABLE [테이블명] PARALLEL ( 병렬범위 ) ;
- : ALTER INDEX[테이블명] PARALLEL ( 병렬범위 ) ;
- CREATE 예시
-- 테이블 생성
CREATE TABLE name ( column1 [data-type], ... )
Parallel 8;
-- 인덱스 생성 DOP 32
CREATE INDEX CYKIM.IX_TBL_X01
ON (MY_TABLE)
TABLESPACE TS_CY PARALLEL 32 NOLOGGING;
ALTER INDEX CYKIM.IX_TBL_X01 NOPARALLEL LOGGING;
- ALTER 예시
ALTER TABLE name Parallel 8 ;
1.2.3 DML에서 CONVENTIONAL PATH , DIRECT PATH 차이점[편집]
- Conventional Path
- :버퍼캐시에 적재, Default 방식
- :Storage -> SGA -> PGA
- 처리 속도 느림
- 로우레벨 락(Row-level Lock) 발생
- Direct Path
- :병렬방식으로 Full Scan 할 때는 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path Read 방식을 사용
- : Storage -> PGA
- 테이블 레벨 락(Table-level lock) 발생
- /*+ append */
- 12c 에서 처리 방법
- DML 병렬 처리
- DML 작업에서는 Paralle 힌트를 주어도 QC 가 작업 담당
- 병렬 DML 가능하도록 처리
- 12c 이전 에는 세션에 적용
SQL> alter session enable parallel dml;
- 12c 부터는 힌트로 적용
/*+ ENABLE_PARALLEL_DML */
1.2.3.1 DML 병렬 처리시 주의사항[편집]
android *DML 병렬 처리시 주의사항
- 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의
- 커밋/롤백을 해야 SELECT 가능 함.
- DML 처리시 플랜에서 항상 QC 아래에 INSERT/UPDATE/DELETE 가 존재 해야 한다. (QC가 아닌 병렬서버에서 처리 토록 해야 한다.)
1.2.4 동일 테이블에 병렬로 입력 하는 방법[편집]
- Oracle에서 파티션 테이블을 이용하여 데이터를 병렬로 입력하는 방법
- 파티션 테이블은 데이터를 물리적 또는 논리적 파티션으로 분할한 테이블입니다.
- 아래 예시에서 INSERT INTO ... SELECT 문을 사용하여 병렬로 데이터를 입력하는 방법을 보여줍니다.
-- 파티션 테이블 생성
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
product_id NUMBER,
quantity NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION sales_202301 VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD')),
PARTITION sales_202302 VALUES LESS THAN (TO_DATE('2023-03-01', 'YYYY-MM-DD'))
);
-- 병렬로 데이터 입력
-- 세션 1에서 데이터 입력
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(sales, 4) */ INTO sales
SELECT sale_id, TO_DATE('2023-01-15', 'YYYY-MM-DD'), product_id, quantity FROM sales_data_january;
commit; -- 커밋을 해야 다른세션에서도 커밋이 가능하다
-- 세션 2에서 데이터 입력
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(sales, 4) */ INTO sales
SELECT sale_id, TO_DATE('2023-02-15', 'YYYY-MM-DD'), product_id, quantity FROM sales_data_february;
commit;
- ORA-12838 : 병렬로 수정한 후 객체를 읽거나 수정할 수 없습니다. 오류 발생시 커밋 여부를 확인할것. 같은 세션에서도 커밋하지 않으면 조회도 불가능하다.
1.3 진행중인 병렬 처리가 잘되는지 궁금한데 ?[편집]
1.3.2 토드에서 모니터링 하는 방법[편집]
- Database - Session Browser
- IO 탭
- Waits 탭
- Current Statsment 탭
- Long Ops 탭
1.3.3 REAL MONITOR[편집]
- 사용법
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') FROM DUAL;
- html 포멧으로 출력
select dbms_sqltune.report_sql_monitor(sql_id=>''5yfzxpu5593jw'',type=>'html', report_level=>'ALL') from dual;
1.3.4 병렬_쿼리_모니터링[편집]
1.3.5 병렬 처리 WAIT EVENT 확인 방법은?[편집]
1.3.5.1 병렬 처리 대기이벤트 종류[편집]
- PX Deq: Parse Reply
- - PEC 가 PES 에게 파싱 요청을 한 후 응답이 올 때까지 대기하는 이벤트
- - 10G 에서 도입된 PSC(Parallel Single Cursor) 모델에서는 PEC가 생성한 커서를 공유하기 때문에 이러한 과정은 생략된다.
- - 단. RAC 에서는 여전히 PEC 와 다른 노드에 존재하는 PES는 PEC가 생성한 SQL문을 파싱하는 역할을 수행
- PX Deq: Execute Reply
- - PEC가 가장 보편적으로 대기하는 이벤트, PES의 실제 작업이 끝나기를 기다리는 대기이벤트이다.
- - 즉 PEC가 PES가 작업을 끝낸 후 데이터를 보내주기를 기다리는 동안 이 이벤트를 대기 한다.
- PX Deq Credit : need buffer
- - PEC / PES 간, PES / PES 간의 통신은, 프로세스 간 존재하는 테이블 큐(Table Q)를 통해 이루어진다.
- - 가령 PES 가 테이블 큐에 데이터를 집어넣으면, PEC 가 테이블 큐에서 그 데이터를 빼가는 형식이다.
- - 오라클은 두 프로세스 중 한 순간에 오직 하나의 프로세스만이 테이블 큐에 데이터를 집어넣을 수 있도록 보장한다.
- - 테이블 큐에 데이터를 집어넣을 수 있는 자격을 확보할 때까지 기다리는 이벤트다.
- PX Deq: Execution Msg
- - PES 에게 가장 보편적인 대기 이벤트, PES 가 어떤 작업을 수행하기 위한 메시지를 기다리는 이벤트
- - 병렬 실행에 관계 하는 각 PES들은 특정 작업이 자신에게 할당될 때까지 기다려야 하며,
- 그 동안 PX Deq: Execution Msg 이벤트를 대기한다.
- PX Deq: Table Q Normal
- - PES 가 테이블 큐에 데이터가 들어오기를 기다리는 이벤트
- - PES 가 다른 PES 로부터 데이터를 받아서 작업을 수행해야 하는 경우에 보편적으로 발생하는 이벤트
- - 생산자/소비자(Producer/Consumer)
- - SELECT /*+ PARALLEL ... */ FROM TABLE A ORDER BY NAME 과 같은 형태(정렬작업 필요한)의 병렬작업을
- 수행하면 테이블로부터 데이터를 패치 하는 생산자 PES 와 패치된 데이터를 받아서 소비(ORDER BY) 하는 소비자 PES 가 협력하는 방식으로 작동
- direct path read
- - 버퍼 캐시를 경유하지 않고 데이터 파일로부터 직접 데이터를 읽는 과정에서 발생하는 이벤트
- - PES 가 테이블로부터 데이터를 페치하는 작업은 대부분 데이터 파일에서 직접 데이터를 읽는 방식을 사용한다.
- enq: TC Contention
- - PES 가 Direct Path I/O를 수행하려면, 해당 테이블에 대한 체크 포인트(Checkpoint)작업이 선행 되어야 한다.
- - 버퍼 캐시의 더티 버퍼가 모두 데이타 파일에 기록되어야 버퍼 캐시를 경유하지 않고 데이터 파일에서 직접 데이터를 읽을 수 있기 때문이다.
- - PEC는 PES 에게 작업을 지시하기 전에 체크포인트 요청을 하고 작업이 끝날 때 까지 기다려야 하며 그 동안 enq: TC Contention 이벤트 대기
1.3.5.2 병렬 세션 대기,대기 이벤트,대기 클래스 조회[편집]
- V$PX_SESSION + V$SESSION
SELECT DECODE(A.QCSERIAL#, NULL, 'PAREMT', 'CHILD') ST_LVL,
A.SERVER_SET "SET",
A.SID,
A.SERIAL#,
STATUS,
EVENT,
WAIT_CLASS
FROM V$PX_SESSION A,
V$SESSION B
WHERE A.SID = B.SID
AND A.SERIAL# = B.SERIAL#
ORDER BY A.QCSID,
ST_LVL DESC,
A.SERVER_GROUP,
A.SERVER_SET
;
1.3.5.3 WAIT EVENT - 상세 대기시간 확인[편집]
- V$SESSION + V$SESSSION_EVENT
SELECT S.INST_ID, S.SID, S.SQL_ID
, S.MACHINE, S.PROGRAM, S.EVENT
, S.BLOCKING_SESSION_STATUS
, SE.EVENT AS EVENT_DTL
, SE.WAIT_CLASS , SE.TIME_WAITED
, SE.MAX WAIT , SE.TOTAL_TIMEOUTS
FROM GV$SESSION S
, GV$SESSION_EVENT SE
WHERE S.SID = SE.SID
AND S.INST ID = 1
AND S SID=8281
-- AND SE.EVENT = 'gc current grant busy'
-- AND S.SQL ID = ''
AND SOL_ID IS NOT NULL
ORDER BY SE.TIME_WAITED DESC
1.3.5.4 대기항목별 WAIT CLASS 확인[편집]
- V$SESSION_WAIT + V$SESSSION_WAIT_CLASS
SELECT A.SID, A.SEQ#, A.EVENT,P1TEXT
, A.STATE , A.WAIT_TIME_MICRO
, A.TIME_REMAINING_MICRO
, B.WAIT_CLASS#, B.WAIT_CLASS
, B.TOTAL_WAITS, B. TIME_WAITED
FROM GV$SESSION WAIT A
, GV$SESSION_WAIT_CLASS B
WHERE A.INST_ID = B.INST_ID
AND A.INST_ID =1
AND A.SID = B.SID
AND A.SID = :SID -- 1510
AND A.SERIAL# = B.SERIAL#
1.4 병렬 처리가 왜 안되지 ?[편집]
1.4.1 병렬 처리 갯수 확인[편집]
1.4.1.1 병렬처리 제약 조건[편집]
SELECT * FROM V$PARAMETER
WHERE NAME IN ('parallel_degree_limit') ; -- CPU
- CPU
- 최대 병렬 처리 갯수가 시스템의 CPU 수에 따라 제한(기본값)
- 제한을 계산하는 데 사용되는 공식은 PARALLEL_THREADS_PER_CPU * CPU_COUNT * 사용 가능한 인스턴스 수입니다(기본적으로 클러스터에서 열려 있는 모든 인스턴스이지만 PARALLEL_INSTANCE_GROUP 또는 서비스 사양을 사용하여 제한할 수 있음).
- AUTO
- CPU 값과 동일
- IO
- 옵티마이져가 사용할 수 있는 최대 병렬 처리 갯수은 시스템의 I/O 용량에 따라 제한
- 이 값은 전체 시스템 처리량을 프로세스당 최대 I/O 대역폭으로 나누어 계산
- IO 설정을 사용하려면 시스템에서 DBMS_RESOURCE_MANAGER.CALIBRATE_IO 프로시저를 실행해야 하고 이 절차에서는 전체 시스템 처리량과 프로세스당 최대 I/O 대역폭을 계산함
- 숫자
- 이 파라메터의 숫자값은 자동 병렬 처리 수준이 활성화된 경우 최적화 프로그램이 SQL 문에 대해 선택할 수 있는 최대 병렬 처리 수준을 지정 함
- 자동 병렬 처리 갯수는 PARALLEL_DEGREE_POLICY가 ADAPTIVE, AUTO 또는 LIMITED로 설정된 경우에만 활성화 됨
- DOP 최대 갯수 = parallel_threads_per_cpu * cpu_count
SELECT *
FROM V$PARAMETER
WHERE NAME in ('parallel_threads_per_cpu','cpu_count')
1.4.2 병렬 환경 파리미터 상세 조회[편집]
SELECT *
FROM V$PARAMETER
WHERE NAME LIKE '%parallel%'
1.4.3 병렬처리가 안되는경우[편집]
1.4.3.1 서버에서 프로세스를 할당 받지 못할때[편집]
1.4.3.2 insert ~ select 의 병렬도가 다를때[편집]
1.4.3.3 파티션닝 테이블에 1개파티션만 타는경우[편집]
- 튜닝 전
- LINK_ID 가 파티션키임.
- 플랜
- 튜닝 후
- 튜닝 후 플랜
1.4.3.4 LOB 컬럼 포함시[편집]
- lob 컬럼 포함시 => 오라클 19c 부터기능
1.4.3.5 DB 링크[편집]
1.4.3.6 기타 요소[편집]
- 할당된 process 갯수가 작을때 (오라클 파라미터 확인)
- OS상 디스크 I/O가 너무 느릴때
1.5 병렬 힌트사용 방법[편집]
1.5.1 PQ_DISTRIBUTE[편집]
1.5.1.1 가능한 조합[편집]
- HASH - HASH : OUTER, INNER 크기가 비슷할 때
- BROADCAST - NONE : OUTER 테이블이 작을때
- NONE - BROADCAST : INNER 테이블이 작을때
- PARTITION - NONE : INNER 테이블 파티션 기준으로 OUTER 테이블을 파티션 하여 분배
- NONE - PARTITION : OUTER 테이블 파티션 기준으로 INNER 테이블을 파티션 하여 분배
- NONE - NONE : 두 테이블이 조인컬럼 기준으로 파티션 되어 있을때
1.5.2 PQ_REPLICATE / NO_PQ_REPLICATE(대량테이블)[편집]
- 각각 병렬서버에서 테이블 전체를 읽음
- BROADCAST 분산 하여 읽지 않음
- 로컬 캐시(SGA) 에서 빠르게 읽는 방식
- 복제라기보다는 조인처럼 생각
- 매우 작은 테이블 처리시 유리
- 튜닝 예시
1.5.3 PQ_SKEW/NO_PQ_SKEW[편집]
- 다수의 로우가 같은 조인키값을 가지고 있어서 조인키의 분산값이 한쪽으로 치우친 경우
- 오라클에서 히스토그램을 생성해야 하지만 자동으로 병렬조인시 SKEW를 핸들링함
- 제약사항
- INNER 조인시
- 단일 컬럼 조인시만 가능, 여러개 컬럼은 안됨
- 병렬 HASH JOIN만 가능
- MERGE JOIN 은 안됨
- SKEW테이블은 일반 테이블만 (뷰, 결과셋은 기능제한됨)
- PQ_SKEW 힌트는 옵티마이저에게 병렬 조인에 대한 조인 키 값의 분포가 매우 왜곡되어 있다고 조언합니다.
- 즉, 높은 비율의 행이 동일한 조인 키 값을 가지고 있습니다.
- Tablespec에 지정된 테이블은 해시 조인의 프로브 테이블이다.
1.5.4 BF 블름필터(Bloom Filter)[편집]
- 어떤값이 어떤 집합에 속해 있는가를 검사하는 필터
- 패러럴 조인시 소비자간의 커뮤니케이션 데이터량 과 해시조인시 부하를 감소하기 위해 사용됨
- JOIN FILTER PRUNNING , RESULT CACHE
- 플랜에서 JOIN FILTER CREATE / USE
- :BF0000
1.5.4.1 PX_JOIN_FILTER / NO_PX_JOIN_FILTER[편집]
- 사용 조건
- 해시/머지 조인시
- 파티션 조인시
- PARALLEL 쿼리시
- 파티션/PARALLEL 둘다 아닌경우, 인라인뷰의 GROUP BY
- 선행 테이블의 상수조건이 없는 경우 오라클은 블름필터를 사용하지 않는다.
- 블름 필터를 만들때 선행 테이블은 필터집합을 만들때 사용.
1.5.5 PQ_DISTRIBUTE_WINDOW[편집]
PQ_DISTRIBUTE_WINDOW(@Query_block N) => N=1 for hash, N=2 for range, N=3 for list(예전 방식 9i)
rem ##################################
rem # Objects #
rem ##################################
alter session set optimizer_adaptive_plans = false;
alter system flush shared_pool;
drop table asc_dmy1;
drop table asc_dmy3;
create table asc_dmy1
parallel 8
as
select 'AAA' f001
from xmltable('1 to 300');
--note: this table has no parallel degree
create table asc_dmy3
as
select 'AAA' f001, 1 acc206
from dual;
rem #############################################
rem # SORT then distribute by HASH (Bug) #
rem #############################################
/*
leads to a HASH JOIN in Line 7, which imo must be a HASH JOIN BUFFERED (due to 2 active PX SENDs at 9 and 13)
This SQL hangs and never finishes
https://oracle-randolf.blogspot.com/2012/12/hash-join-buffered.html
"At most one data distribution can be active at the same time"
"Since it doesn't seem to be supported to have two PX SEND operations active at the same time,
some artificial blocking operation needs to be introduced, in this case the HASH JOIN BUFFERED,
that first consumes the second row source completely before starting the actual probe phase"
*/
select /*+ pq_distribute_window(@"SEL$1" 2) */
max(v.acc206) over (partition by v.f001) max_bew
from asc_dmy3 v,
asc_dmy1 e
where e.f001 = v.f001
and v.f001 = e.f001;
/*
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 419 | 6 (17)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
| 3 | WINDOW CONSOLIDATOR BUFFER| | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 1 | 419 | 6 (17)| 00:00:01 | Q1,02 | P->P | HASH |
| 6 | WINDOW SORT | | 1 | 419 | 6 (17)| 00:00:01 | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 1 | 419 | 5 (0)| 00:00:01 | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 1 | 415 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH |
| 10 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
| 11 | TABLE ACCESS FULL | ASC_DMY3 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
| 12 | PX RECEIVE | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 14 | PX BLOCK ITERATOR | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 15 | TABLE ACCESS FULL | ASC_DMY1 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
*/
rem #############################################
rem # distribute by HASH then SORT (Success) #
rem #############################################
/*
leads to a HASH JOIN *BUFFERED* in Line 6, which is inevitably necessary imo
This SQL finishes immediately
*/
select /*+ pq_distribute_window(@"SEL$1" 1) */
max(v.acc206) over (partition by v.f001) max_bew
from asc_dmy3 v,
asc_dmy1 e
where e.f001 = v.f001
and v.f001 = e.f001;
/*
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | P->S | QC (RAND) | | | |
| 3 | WINDOW SORT | | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | PCWP | | 20480 | 20480 | 8/0/0|
| 4 | PX RECEIVE | | 1 | 419 | 5 (0)| 00:00:01 | Q1,03 | PCWP | | | | |
| 5 | PX SEND HASH | :TQ10002 | 1 | 419 | 5 (0)| 00:00:01 | Q1,02 | P->P | HASH | | | |
|* 6 | HASH JOIN BUFFERED | | 1 | 419 | 5 (0)| 00:00:01 | Q1,02 | PCWP | | 3400K| 3091K| 8/0/0|
| 7 | PX RECEIVE | | 1 | 415 | 3 (0)| 00:00:01 | Q1,02 | PCWP | | | | |
| 8 | PX SEND HASH | :TQ10000 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH | | | |
| 9 | PX SELECTOR | | | | | | Q1,00 | SCWC | | | | |
| 10 | TABLE ACCESS FULL| ASC_DMY3 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | SCWP | | | | |
| 11 | PX RECEIVE | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | | |
| 12 | PX SEND HASH | :TQ10001 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | | |
| 13 | PX BLOCK ITERATOR | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | | |
|* 14 | TABLE ACCESS FULL| ASC_DMY1 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
*/
1.5.6 PQ_EXPAND_TABLE / NO_PQ_EXPAND_TABLE[편집]
- PQ_EXPAND_TABLE
- 파티션테이블인 경우 한곳에 편중된 파티션이 있을때, 옵티마이저가 UNION 절로 변경하여 편중된 파티션과 다른 파티션을 나누어 병렬처리로 수행함.
- (예) 인라인뷰내 파티션테이블을 (GROUP BY하는 경우)발생함
- 튜닝 사례 1 : 한곳에 치중된 파티션을 UNION ALL 로 분리
SELECT *
FROM
(SELECT /*+ PQ_EXPAND_TABLE(A2) */
AZ.SRV_INSTLID
, A2.CYKIM_TIMPL_ID
, A2.CYKIM_WRT_YMD
, A2.CYKIM_WRT_DGR
, MIN(A2.DCRY_LNNO) AS DCRY_LNNO
FROM TB_BIG_PART A2 -- 파티션테이블
WHERE SUBSTR(A2.CYKIM_TMPL_ID, 0, 2) = 'am'
GROUP BY A2.SRV_INST_ID
, A2.CYKIM_TMP_ID
, A2.CYKIM_WRT_YMD
, A2.CYKIM_WRT_DGR
) A
, TB_CYKIM_SPRV B
.....
- 튜닝 사례 2 : 한곳에 치중된 파티션을 UNION ALL 로 분리 하지 않음.
SELECT *
FROM
(SELECT /*+ NO_PQEXPAND_TABLE(A2) */
AZ.SRV_INSTLID
, A2.CYKIM_TIMPL_ID
, A2.CYKIM_WRT_YMD
, A2.CYKIM_WRT_DGR
, MIN(A2.DCRY_LNNO) AS DCRY_LNNO
FROM TB_BIG_PART A2 -- 파티션테이블의 한곳의 파티션에 치중됨, 예를 들어 2000년 이전 데이터는 PT_2001파티션에 1억건 존재 , 나머지는 1백만건
WHERE SUBSTR(A2.CYKIM_TMPL_ID, 0, 2) = 'am'
GROUP BY A2.SRV_INST_ID
, A2.CYKIM_TMP_ID
, A2.CYKIM_WRT_YMD
, A2.CYKIM_WRT_DGR
) A
, TB_CYKIM_SPRV B
.....
1.5.7 PQ_CONCURRENT_UNION[편집]
- UNION ALL 성능향상
- UNION은 각각 SQL을 1개씩 SERIAL 하게 처리하는게 기본방식임
- 12C 부터 병렬쿼리 실행시 동시(CONCURRENT)에 처리토록 함
- 전체데이터 처리시 유리
- 부분범위 처리시 비추
- DEFAULT
1.5.8 PQ_FILTER[편집]
/*+ PQ_FILTER(SERIAL | NONE | HASH | RANDOM) */ -- 4개중 택1
- 병렬서버에서 서브쿼리를 필터링할수 있는 기능.
- 서브쿼리 필터링은 일반적으로 메인쿼리가 모두 수행된 후 수행함.
- HASH방식과 RANDOM방식은 추가적인 버퍼링이 필요하므로 특별한 경우가 아니면 NONE 방식으로 사용하는것이 일반적일것으로 판단함.
- 다수의 서브쿼리 수행시 2개의 서브쿼리 모두 병렬서버에서 필터링됨.
- => ORDER_SUBQ 힌트(12c 이후) 로 서브쿼리의 수행순서 조정가능함.
- PQ_FILTER 사용예시 (with NO_UNNEST 힌트)
SELECT /* + PARALLEL(2) PQ_FILTER(HASH) */
*
FROM T1 A
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM T2 B
WHERE B.C1 = A.C1)
- 서브 쿼리가 2개일 때 순서 조정 예시
SELECT /*+ PARALLEL(2) PQ_FILTER(NONE)
ORDER_SUBQ(@MAIN @SUB2 @SUB1) QB_NAME(MAIN) */
*
FROM T1 A
WHERE EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */
1
FROM T2 B
WHERE B.C1 = A.C1)
AND EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB2) */
1
FROM T3 C
WHERE C.C1 = A.C1)
FILTER
PX COORDINATOR
PX SEND QC(RANDOM)
PX BLOCK ITERATOR
TABLE ACCESS FULL T1
INDEX RANGE SCAN IX_T2
PX COORDINATOR
PX SEND QC(RANDOM)
FILTER
PX BLOCK ITERATOR.
TABLE ACCESS FULL 11
INDEX RANGE SCAN IX_12
PX COORDINATOR
PX SEND QC(RANDOM)
FILTER
PX BLOCK ITERATOR
TABLE ACCESS FULL T1
INDEX RANGE SCAN X_13
INDEX RANGE SCAN IX_12
PX COORDINATOR
PX SEND QC(RANDOM)
BUFFER SORT
FILTER
PX RECEIVE
PX SEND HASH
PX BLOCK ITERATOR
TABLE ACCESS FULL T1
INDEX RANGE SCAN IX_T2
PX COORDINATOR
PX SEND QC(RANDOM)
BUFFER SORT
FILTER
PX RECEIVE
PX SEND ROUND-ROBIN
PX BLOCK ITERATOR
TABLE ACCESS FULL T1
INDEX RANGE SCAN IX_T2
1.5.9 파티션[편집]
- USE PARTITION_WISE_DISTINCT
- USE PARTITION_WISE_GBY
- USE PARTITION_WISE_WIF
1.6 병렬 쿼리 튜닝 포인트[편집]
1.6.1 테이블 정보를 수집 하라[편집]
- 테이블 사이즈 조사
- 테이블 파티셔닝 여부
- 파티션 키
- 병렬처리가 가능한 인덱스(index_ffs , 파티션 로컬 인덱스)
1.6.2 스칼라퀴리는 인라인뷰로 변경을 검토하라[편집]
- 쿼리 결과를 전송하는 단계에서 수행되는 스칼라 서브쿼리는 QC가 담당
- 스칼라 쿼리를 인라인뷰로 변경 => lateral 쿼리 활용법
- 스칼라쿼리
- 인라인뷰로 변경
- 스칼라쿼리
1.6.3 플랜에서 QC(Query Cordinator) 위치를 확인 하라[편집]
1.6.3.1 DELETE 구문[편집]
- 튜닝전
- 튜닝 후
1.6.3.2 MERGE 구문[편집]
- 튜닝 전
- 튜닝 후
1.6.4 BROADCAST 테이블을 찾아라[편집]
- 대용량 임시/템프 테이블에 주의하라
- - 데이터 전환 이나 인터페이스를 위한 중간(임시) 테이블 생성시 반드시 중간 테이블에 대한 통계정보를 생성토록 한다.
- - 임시테이블은 주로 통계 정보가 생성되어 있지 않아. 통계정보 오류로 인해 브로드캐스트로 처리되는 경우가 많음.
1.6.5 SQL/PLAN에서 튜닝 대상을 찾아라[편집]
- rownum => row_number() 윈도우 함수로 변경
- S->P 분산 프로세스
- round - robin
- 플랜
- - NL조인으로 round - robin 으로 처리중
- 튜닝 조치
- - HASH 조인 으로 변경
- 튜닝 결과 플랜 정보
1.6.6 조인이 효율적인지 검토 하라[편집]
1.6.7 심플하게 튜닝 하는 방법은 없을까?[편집]
- opt_param('_parallel_broadcast_enabled','false')
- pq_distribute(A hash hash)
1.7 입력/수정 성능저하시 검토 사항[편집]
1.7.1 INSERT 처리가 느릴때(SELECT~INSERT시 SELECT는 빠른데 INSERT가 느린경우)[편집]
1.7.2 시퀀스를 사용하는경우[편집]
- sequence cache size 증가 검토
- default 20 => 2000이상
1.7.3 DB링크 병렬처리 주의사항[편집]
- 소스디비의 프로세스 최대갯수 확인 필요
- 옵티마이져가 쿼리 변형을 수행함
- dblink로 가져올때는 병렬처리 불가
1.8 데이터 전환시 사용하는 병렬처리[편집]
-- 세션에서 병렬 쿼리 작업 절차
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET HASH_AREA_SIZE = 1024000000;
ALTER SESSION SET SORT_AREA_SIZE = 2147483647;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE = 2147483647;
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
-- 사용자가 지정한 sort_area_size가 모든 병렬 서버에게 적용.
-- sort_area_size를 크게 설정한 상태에서 지나치게 큰 병렬도를 지정하면
-- OS 레벨에서 페이징이 발생하고 심할 경우 시스템을 마비시킬 수 있음.
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256; -- AIX는 최대 512
ALTER SESSION SET "_sort_multiblock_read_count" = 256;
ALTER SESSION SET "_db_file_optimizer_read_count" = 256;
ALTER SESSION SET "_db_file_exec_read_count" = 256;
ALTER SESSION SET "_serial_direct_read" = TRUE;
-- 병렬 실행 메시지 사이즈 32K
alter system set parallel_execution_message_size = 32768; -- 16384 -- 기본