"병렬 쿼리 튜닝"의 두 판 사이의 차이
DB CAFE
(→병렬 환경 파리미터 조회) |
(→병렬 작업에 대한 리소스 제한을 설정하는 매개변수) |
||
224번째 줄: | 224번째 줄: | ||
WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 2 | WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 2 | ||
</source> | </source> | ||
− | + | # 병렬 작업에 대한 리소스 제한을 설정하는 매개변수 | |
# PARALLEL_FORCE_LOCAL | # PARALLEL_FORCE_LOCAL | ||
# PARALLEL_MAX_SERVERS | # PARALLEL_MAX_SERVERS |
2023년 1월 13일 (금) 01:12 판
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.
- 병렬로 처리할때 일의 최소 단위
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조인 시
- 파티션 인덱스를 병렬로 스캔할 시
- 파티션 인덱스를 병렬로 갱신할 때
- 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때
---------------------------------------------------------------------------------------------------
|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 생산자 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 병렬처리[편집]
- DML
- SELECT
- INSERT
- UPDATE
- DELETE
- DDL
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
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 병렬 처리시 주의사항
android #DML 병렬 처리시 주의사항
- 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의
- 커밋/롤백을 해야 SELECT 가능 함.
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 병렬 환경 파리미터 조회[편집]
SELECT *
FROM V$PARAMETER
WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 2
- 병렬 작업에 대한 리소스 제한을 설정하는 매개변수
- PARALLEL_FORCE_LOCAL
- PARALLEL_MAX_SERVERS
- PARALLEL_MIN_PERCENT
- PARALLEL_MIN_SERVERS
- PARALLEL_MIN_TIME_THRESHOLD
- PARALLEL_SERVERS_TARGET
- SHARED_POOL_SIZE
1.4.2 병렬처리가 안되는경우[편집]
1.4.3 서버에서 프로세스를 할당 받지 못할때[편집]
1.4.4 insert ~ select 의 병렬도가 다를때[편집]
1.4.5 파티션닝 테이블에 1개파티션만 타는경우[편집]
1.4.6 LOB 컬럼 포함시[편집]
- lob 컬럼 포함시 => 오라클 19c 부터기능
1.4.7 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) */ -- 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
- .오라클은 내부적 으로 어떤 힌트를 사용하고 있는지 볼까 ?
- 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 -- 기본