병렬 쿼리 튜닝
DB CAFE
- 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' -- 2
1.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[편집]
1.5.7 PQ_CONCURRENT_UNION[편집]
1.5.8 PQ_FILTER[편집]
병렬서버에서 서브쿼리를 필터링할수 있는 기능. 서브쿼리 필터링은 메인쿼리가 모두 수행된 후 수행하는것이 일반적임. HASH방식과 RANDOM방식은 추가적인 버퍼링이 필요하므로 특별한 경우가 아니면 NONE방식으로 사용하는것이 일반적일것으로 판단함.
- 다수의 서브쿼리 수행시 2개의 서브쿼리 모두 병렬서버에서 필터링됨. ORDER_ SUBQ 힌트로 서브쿼리의 수행순서 조정가능함.
ORDER_SUBQ(@"SEL$1" "SEL$2" "SEL$3") SELECT /* + PARALLEL(2) PQ_FILTER(NONE) ORDER_SUBQ(@MAIN SUB2 SUB1) QB_NAME(MAIN) */ P_FILTER(SERIAL | NONE | HASH | RANDOM) SELECT /*+ PARALLEL(2) PQ_FILTER(HASH) */ FROM T1 A WHERE EXISTS (SELECT /*+ NO_UNNEST */ - FROM T2 B WHERE B.C1 = A.C1) FROM T1 A WHERE EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */ - FROM T2 B WHERE B.C1 = A.C1) AND EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB2) */ FROM T3 C WHERE C.C1 = A.C1) SERIAL QC에서 서브쿼리를 필터링함 NONE 메인쿼리를 분배하지 않고 병렬서버에서 서 브쿼리를 필터링함. 병렬서버가 T1 테이블 을 블럭그래뉼로 조회하고, 조회결과로 서 브쿼리를 필터링하여 QC로 결과를 전송하 는 방식 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 eX 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 (X SEND RQUND-ROBIN, PX BLOCK ITERATOR TABLE ACCESS FULL T1 INDEX RANGE SCAN IX_T2 RDER_SUBQ 서브쿼리의 순서를 지정하는 힌트. Query Block Name으로 지정함 SELECT /*+ ORDER_SUBQ(@MAIN SUB2 SUB1) FILTER OB_NAME(MAIN) */ TABLE ACCESS FULL TI INDEX RANGE SCAN IX_13 INDEX RANGE SCAN IX_T2 ORDER_SUBQ (@MAIN SUB1 SUB2) FROM T1 A WHERE EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */ - FROM T2 B WHERE B.C1 = A.C1) AND EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB2) */ FROM T3 C WHERE C.C1 = A.C1)
•EXPAND_TABLE
파티션테이블인 경우 편중된 파티션이 있을때, 옵티마이저가 UNION 절로 변경하여 편중된 파티션과 다른 파티션을 나누어 병렬처리로 수행함.
(예) 인라인뷰내 파티션테이블을 (GROUP BY하는 경우)발생함
PQ_EXPAND_TABLE(4|0|=%)
INO PQ EXPAND_TABLE(테이블 명)
FROM
구리시'
• Saf)
(SELECT /*+ PQEXPAND_TABLE(A2) */
AZ.SRV_INSTLID
,A2.DCRY_TIMPL_ID
.A2.DCRY_WRT_YMD
,A2.DCRY_WRT_DGR
, MIN(A2.DCRY_LNNO) AS DCRY_LNNO FROM CSP_DCRY A2
WHERE SUBSTR(A2.DCRY_TMPL_ID, 0, 2) = 'am'
GROUP BYJA2.SRV_INST_ID
, A2.DCRY_TMPLID (A2DCRY WRI-YMO
,A2.DCRY_WRT_DGR
7 그 uskt
,CSP-BYINDSPRVB 7 )72
FROM
( SELECT /*+ NO_PQ_EXPAND_TABLE(A2) */
A2.SRV_INST_ID
.A2.DCRY_TMPL_ID
, A2.DCRY_WRT_YMD ,A2.DCRY_WRT_DGR
, MIN(A2.DCRY_LNNO) AS DCRY_LNNO FROM CSP_DCRY A2
WHERE SUBSTR(A2.DCRY_TMPL_ID, 0, 2) = 'am'
GROUP BY A2.SRV_INST_ID
,A2.DCRY_TMPL_ID
, AZ.DCRY_WRT_YMD
, A2.DCRY_WRT_DGR
) A
, CSP_BYIND_SPRV B
•EXPAND_TABLE 파티션테이블인 경우 편중된 파티션이 있을때, 옵티마이저가 UNION 절로 변경하여 편중된 파티션과 다른 파티션을 나누어 병렬처리로 수행함. (예) 인라인뷰내 파티션테이블을 (GROUP BY하는 경우)발생함 PQ_EXPAND_TABLE(4|0|=%) INO PQ EXPAND_TABLE(테이블 명) FROM 구리시' • Saf) (SELECT /*+ PQEXPAND_TABLE(A2) */ AZ.SRV_INSTLID ,A2.DCRY_TIMPL_ID .A2.DCRY_WRT_YMD ,A2.DCRY_WRT_DGR , MIN(A2.DCRY_LNNO) AS DCRY_LNNO FROM CSP_DCRY A2 WHERE SUBSTR(A2.DCRY_TMPL_ID, 0, 2) = 'am' GROUP BYJA2.SRV_INST_ID , A2.DCRY_TMPLID (A2DCRY WRI-YMO ,A2.DCRY_WRT_DGR 7 그 uskt ,CSP-BYINDSPRVB 7 )72 FROM ( SELECT /*+ NO_PQ_EXPAND_TABLE(A2) */ A2.SRV_INST_ID .A2.DCRY_TMPL_ID , A2.DCRY_WRT_YMD ,A2.DCRY_WRT_DGR , MIN(A2.DCRY_LNNO) AS DCRY_LNNO FROM CSP_DCRY A2 WHERE SUBSTR(A2.DCRY_TMPL_ID, 0, 2) = 'am' GROUP BY A2.SRV_INST_ID ,A2.DCRY_TMPL_ID , AZ.DCRY_WRT_YMD , A2.DCRY_WRT_DGR ) A , CSP_BYIND_SPRV B
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 -- 기본