행위

병렬 쿼리 튜닝

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 1월 12일 (목) 00:01 판 (→‎DML문 에서도 SELECT 절만 PARALLEL 힌트를 사용하면 될까?)
thumb_up 추천메뉴 바로가기

목차

1 병렬 쿼리 튜닝[편집]

1.1 병렬처리 핵심은?[편집]

1.1.1 그래뉼[편집]

  1. 병렬로 처리할때 일의 최소 단위
1.1.1.1 블록 GRANULE[편집]
  1. PX BLOCK ITERATOR 라고 표시
  2. QC는 테이블로부터 읽어야할 범위의 블록 GRANULE로서 각 병렬 서버에게 할당
1.1.1.2 파티션 GRANULE[편집]
  1. PX PARTITION RANGE ALL 또는 PX PARTITION RANGE ITERATOR 라고 표시
1.1.1.3 사용되는 시기[편집]
  1. Partition-Wise조인 시
  2. 파티션 인덱스를 병렬로 스캔할 시
  3. 파티션 인덱스를 병렬로 갱신할 때
  4. 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때

1.1.2 생산자 VS 소비자[편집]

1.1.3 DOP 많을수록 좋을까?[편집]

1.1.4  병렬퀴리튜닝의 관점은? 병렬의 갯수인가? 분산의 정도인가?[편집]

1.1.5 병렬 PLAN 해석하는 방법[편집]

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. 사용법

1.3.4 DML문 에서도 SELECT 절만 PARALLEL 힌트를 사용하면 될까?[편집]

  1. DML에서 CONVENTIONAL / DIRECT PATH 차이점? 12c 에서 처리 방법
  2. DML 병렬 처리
  3. DML 병렬 처리시 주의사항
  4. WAIT EVENT 확인 방법은?
섬네일을 만드는 중 오류 발생: convert: no decode delegate for this image format `PNG' @ error/constitute.c/ReadImage/562. convert: no images defined `/tmp/transform_06aac9b8f5e0.png' @ error/convert.c/ConvertImageCommand/3282. Error code: 1
    1. 병렬쿼리가 대기 하고 있는경우?

1.4 병렬 처리가 왜 안되지 ?[편집]

1.4.1 병렬처리가 안되는경우[편집]

1.4.2 서버에서 프로세스를 할당 받지 못할때[편집]

1.4.3 파티션닝 테이블에 1개파티션만 타는경우[편집]

1.4.4 LOB 컬럼 포함시[편집]

1.4.5 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[편집]

1.5.9 파티션[편집]

  1. USE PARTITION_WISE_DISTINCT #
  2. USE PARTITION_WISE_GBY #
  3. 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.8 병렬 환경 조회[편집]

1.8.1 병렬 환경 파리미터 조회[편집]

SELECT *
  FROM V$PARAMETER
 WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 2

1.8.2 병렬 처리 개수 조회[편집]

  • 패러럴 최대 서버 수 = 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 -- 기본

1.9 병렬 쿼리 안되는 경우[편집]

  1. rownum 사용시 => 해결방법 : ROW_NUMBER 원도우 함수로 변경 ex) row_number() over(order by 유니크한컬럼) as rn
  2. lob 컬럼 포함시 => 오라클 12c 이후 19c 부터기능
  3. insert ~ select 의 병렬도가 다를때
  4. 서버에서 프로세스를 할당 받지 못할때

1.10 세션에서 병렬 쿼리 작업 절차[편집]

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;

-- 1) 실행계획에서 P ->P가 나타날 때면 지정한 병렬도의 2배수만큼 병렬 프로세스 필요
-- 2) 쿼리 블록마다 병렬도를 다르게 지정한 경우, 여러 가지 우선 순위와 규칙에 따라 
--    최종 병렬도가 결정됨, 병렬도를 같게 지정하는 것이 바람직 함.
-- 3) parallel_index 힌트를 사용할 때는 반드시 index 또는 index_ffs 힌트를 사용
--    옵티마이저에 의해 Full table Scan이 선택될 경우 parallel_index 힌트는 무시 됨 
-- 4) 병렬 DML 수행시 Exculsive 모드 테이블 Lock이 걸리므로 업무 트랜잭션이 발생하는 주간에는 삼가 
-- 5) 테이블이나 인덱스를 빠르게 생성하려고 parallel 옵션 을 사용했다면 작업을 완료 후 즉시 noparallel로 변경 할것 
-- 6) 부분범위처리 방식으로 조회하면서 병렬 쿼리를 사용한 때에는 필요한 만큼 데이터를 Fetch  하고 나서 곧바로 커서를 닫아 주어야 함 
--    Toad나 Orange 처럼 부분범위처리를 지원하는 쿼리 툴에서는 EOF에 도달하기 전까지 
--    커서를 오픈한 채로 유지하기 때문에 오라클은 병렬 서버들을 해제하지 못하고 대기 상태에 
--    머물도록 한다. 이는 불필요한 리소스를 낭비하는 결과를 초래하므로 조회가 끝나자마자 
--     select * from dual  같은 문장을 수행해 병렬 쿼리의 커서를 닫아 주어야 한다.

1.11 SQL 플랜상 튜닝 검토 사항[편집]

  1. broadcast 는 소량테이블에 적합 pq_distribute(A none broadcast)
  2. hash 는 대량 테이블에 적합 pq_distribute(A hash hash)
  3. P->S 는 튜닝 대상임. P->P로 바꿀수 있는 방법을 검토.sql수정도 좋다. rownum 은 윈도우 함수로 변경
  4. round-robin 은 튜닝 대상임
  5. merge 나 insert ,delete문에만 주로 Parallel 힌트 사용. 하위 select 문에서는 가급적 지양 , 1:1 관계를 지향한다
  6. part key 플랜도 튜닝 대상. pq_disiribute(A hash hash) 힌트로 검토
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=병렬_쿼리_튜닝&oldid=6899"