"병렬 쿼리 튜닝"의 두 판 사이의 차이

1.1 병렬 쿼리가 안되는 조건[편집]

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

1.2 병렬 환경 정보 조회[편집]

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

1.3 병렬 환경 세팅[편집]

• 패러럴 최대 서버 수 = 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.4 병렬 쿼리 작업 절차[편집]

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.5 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) 힌트로 조치 한다.