병렬 쿼리 튜닝
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 병렬 쿼리 튜닝[편집]
1.1 병렬처리 핵심은?[편집]
1.1.1 그래뉼[편집]
- 병렬로 처리할때 일의 최소 단위
1.1.1.1 블록 그래뉼[편집]
- PX BLOCK ITERATOR 라고 표시
- QC는 테이블로부터 읽어야할 범위의 블록 GRANULE로서 각 병렬 서버에게 할당
1.1.1.2 파티션 그래뉼[편집]
- PX PARTITION RANGE ALL 또는 PX PARTITION RANGE ITERATOR 라고 표시
- 사용되는 시기
- Partition-Wise조인 시
- 파티션 인덱스를 병렬로 스캔할 시
- 파티션 인덱스를 병렬로 갱신할 때
- 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때
1.1.2 생산자 VS 소비자[편집]
1.1.3 DOP 많을수록 좋을까?[편집]
1.1.5 병렬 PLAN 해석하는 방법[편집]
--------------------------------------------------------------------------------------------------------------------
| 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.5.1 SQL 플랜상 튜닝 검토 사항[편집]
android # broadcast 는 소량테이블에 적합
- hash 는 대량 테이블에 적합
- P->S 는 튜닝 대상임. P->P로 바꿀수 있는 방법을 검토.sql수정도 좋다.
- round-robin 은 튜닝 대상임
- merge 나 insert ,delete문에만 주로 Parallel 힌트 사용. 하위 select 문에서는 가급적 지양 , 1:1 관계를 지향한다
- part key 플랜도 튜닝 대상. pq_disiribute(A hash hash) 힌트로 검토
1.2 병렬처리 올바른 사용법은?[편집]
1.2.1 병렬 힌트를 어디에 어떻게 써야 하나?[편집]
1.2.2 DDL, DML, DCL 병렬처리[편집]
1.3 진행중인 병렬 처리가 잘되는지 궁금한데 ?[편집]
1.3.1 관련 뷰[편집]
1.3.2 토드에서 확인 방법[편집]
1.3.3 REAL MONITOR[편집]
- 사용법
1.3.4 DML문 에서도 SELECT 절만 PARALLEL 힌트를 사용하면 될까?[편집]
- DML에서 CONVENTIONAL / DIRECT PATH 차이점? 12c 에서 처리 방법
- DML 병렬 처리
- DML 작업에서는 Paralle 힌트를 주어도 QC 가 작업 담당
- 병렬 DML 가능하도록 처리
- 12c 이전 에는 세션에 적용
SQL> alter session enable parallel dml;- 12c 부터는 힌트로 적용
/*+ ENABLE_PARALLEL_DML */- DML 병렬 처리시 주의사항
- 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의
1.3.4.1 데이터 전환시 사용하는 병렬처리[편집]
-- 세션에서 병렬 쿼리 작업 절차
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;- WAIT EVENT 확인 방법은?
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.4 병렬 처리가 왜 안되지 ?[편집]
1.4.1 병렬처리가 안되는경우[편집]
1.4.1.1 병렬 환경 파리미터 조회[편집]
SELECT *
FROM V$PARAMETER
WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 21.4.2 서버에서 프로세스를 할당 받지 못할때[편집]
1.4.3 insert ~ select 의 병렬도가 다를때[편집]
1.4.4 파티션닝 테이블에 1개파티션만 타는경우[편집]
1.4.5 LOB 컬럼 포함시[편집]
- lob 컬럼 포함시 => 오라클 19c 부터기능
1.4.6 DB 링크[편집]
1.5 병렬 힌트사용 방법 [편집]
1.5.1 PQ_DISTRIBUTE[편집]
1.5.2 PQ_REPLICATE / NO_PQ_REPLICATE(대량테이블)[편집]
1.5.3 PQ_SKEW/NO_PQ_SKEW[편집]
1.5.4 BF 블름필터(Bloom Filter)[편집]
1.5.5 PQ_DISTRIBUTE_WINDOW[편집]
1.5.6 PQ_EXPAND_TABLE / NO_PQ_EXPAND_TABLE[편집]
- PQ_EXPAND_TABLE
- 파티션테이블인 경우 한곳에 편중된 파티션이 있을때, 옵티마이저가 UNION 절로 변경하여 편중된 파티션과 다른 파티션을 나누어 병렬처리로 수행함.
- (예) 인라인뷰내 파티션테이블을 (GROUP BY하는 경우)발생함
- 튜닝 사례 1 : 한곳에 치중된 파티션을 UNION ALL 로 분리
SELECT *
FROM
(SELECT /*+ 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 -- 파티션테이블
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[편집]
1.5.8 PQ_FILTER[편집]
PQ_FILTER(SERIAL| NONE | HASH | RANDOM)- 병렬서버에서 서브쿼리를 필터링할수 있는 기능.
- 서브쿼리 필터링은 일반적으로 메인쿼리가 모두 수행된 후 수행함.
- 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)
- SERIAL
- QC에서 서브쿼리를 필터링함
FILTER
PX COORDINATOR
PX SEND QC(RANDOM)
PX BLOCK ITERATOR
TABLE ACCESS FULL T1
INDEX RANGE SCAN IX_T2
- NONE
- 메인쿼리를 분배하지 않고 병렬서버에서 서브쿼리를 필터링함.
- 병렬서버가 T1 테이블을 블록 그래뉼로 조회하고, 조회결과로 서브쿼리를 필터링하여 QC로 결과를 전송하는 방식
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
- HASH
- 메인 쿼리를 HASH 방식으로 분배 한후, 병렬서버에서 서브쿼리를 필터링함.
- HASH방식은 조인조건인 C1 컬럼의 해시값으로 분배 되기 때문에 서브쿼리 캐싱 효율을 높이는 목적으로 활용가능.
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
- RANDOM
- 메인쿼리를 RANDOM 방식으로 분배한후, 병렬서버에서 서브쿼리를 필터링함.
- RANDOM 방식은 read by other session등 의 블럭경합을 해소하는 목적으로 활용가능
PX COORDINATOR
PX SEND QC(RANDOM)
'''BUFFER SORT'''
'''FILTER'''
PX RECEIVE
'''PX SEND RQUND-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
- .오라클은 내부적 으로 어떤 힌트를 사용하고 있는지 볼까 ?
- OUTLINE
1.6 튜닝포인트를 찾아라[편집]
1.6.1 심플하게 튜닝 하는 방법은 없을까?[편집]
1.6.2 스칼라퀴리는 인라인뷰로 변경을 검토하라[편집]
1.6.3 BROADCAST 테이블을 찾아라[편집]
1.6.4 튜닝대상을 찾아라[편집]
1.6.5 조인이 효율적인지 검토 하라[편집]
1.7 입력/수정 성능저하시 검토 사항[편집]
1.7.1 INSERT 처리가 느릴때(SELECT~INSERT시 SELECT는 빠른데 INSERT가 느린경우)[편집]
1.7.2 시퀀스를 사용하는경우[편집]
1.7.3 V$SSESSION_WAIT_CLASS[편집]
1.7.4 DB링크 병렬처리 주의사항[편집]
1.7.5 병렬 처리 개수 조회[편집]
- 패러럴 최대 서버 수 = parallel_threads_per_cpu * cpu_count * concurrent_parallel_users * 5
-- 오라클 레퍼런스에서는 위 계산으로 하면 된다고 함. 하지만 안됨 11g r2
alter system set parallel_max_servers =72; --48 기본- 패러럴 실행 메시지 사이즈 32K
alter system set parallel_execution_message_size = 32768; -- 16384 -- 기본