병렬 쿼리 튜닝
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 병렬 쿼리 튜닝[편집]
1.1 병렬 환경 조회[편집]
1.1.1 병렬 환경 파리미터 조회[편집]
SELECT *
FROM V$PARAMETER
WHERE NAME LIKE '%parallel%' -- 'parallel_threads_per_cpu' -- 2
1.1.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.2 병렬 쿼리 안되는 경우[편집]
- rownum 사용시 => 해결방법 : ROW_NUMBER 원도우 함수로 변경 ex) row_number() over(order by 유니크한컬럼) as rn
- lob 컬럼 포함시 => 오라클 12c 이후 19c 부터기능
- insert ~ select 의 병렬도가 다를때
- 서버에서 프로세스를 할당 받지 못할때
1.3 세션에서 병렬 쿼리 작업 절차[편집]
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.4 SQL 플랜상 튜닝 검토 사항[편집]
- broadcast 는 소량테이블에 적합 pq_distribute(A none broadcast)
- hash 는 대량 테이블에 적합 pq_distribute(A hash hash)
- P->S 는 튜닝 대상임. P->P로 바꿀수 있는 방법을 검토.sql수정도 좋다. rownum 은 윈도우 함수로 변경
- round-robin 은 튜닝 대상임
- merge 나 insert ,delete문에만 주로 Parallel 힌트 사용. 하위 select 문에서는 가급적 지양 , 1:1 관계를 지향한다
- part key 플랜도 튜닝 대상. pq_disiribute(A hash hash) 힌트로 검토