[SQLP 요약] 제 6장 (고급SQL튜닝, 파티션, 배치 튜닝, 병렬 처리) - (4/4)

이전 내용 :

2024.02.19 - [자격증/SQLP] - [SQLP 요약] 제 5, 6장 (이력, 소계, Top N, 고급 SQL 튜닝) - (3/4)

[SQLP 요약] 제 5, 6장 (이력, 소계, Top N, 고급 SQL 튜닝) - (3/5)

 
✔️ 사용자정의함수 > 스칼라서브쿼리 변경 시 캐싱 효과로 성능 개선됨
✔️ Intas 인데 select 절에 스칼라서브쿼리다? -> nl 조인 됨 -> 스칼라서브쿼리를 outer 조인으로 변경 후 full, hash 하면 훨씬 개선됨.
 

파티셔닝

1) Range 파티셔닝
- 파티션 키 값의 범위로 분할
2) Hash 파티셔닝
- 파티션 키 값에 해시 함수를 적용해서 매핑
- 데이터가 고르게 분산되도록 DBMS가 관리함. 내가못함.
-> 데이터가 한 곳에 쏠릴 수 있다.
- 병렬처리 시 성능 효과 극대화
- DML 경합 분산에 효과적
3. List 파티셔닝
- 불연속적인 값의 목록을 파티션에 지정.
4. Composite 파티셔닝
보통 Range(Main) + Hash(Sub)로 지정함.
Hash가 선두(Main)로 올 수는 없음.
 
✔️ 파티션 Pruning
- 옵티마이저가 불필요한 파티션을 액세스 대상에서 제외하는 기능. 알아서 해주는 것임.
- 파티션 키 컬럼 가공 및 데이터타입 묵시적 형변환 일어나도 pruning이 작동하지 않음.
 
✔️ 정적 파티션 Pruning
- 액세스 할 파티션을 컴파일 시점에 결정함. 파티션 키 컬럼을 상수 조건으로 조회할 경우
✔️ 동적 파티션 Pruning
- 액세스 할 파티션을 실행 시점에 결정. 바인드 변수로 조회하는 경우.
 

파티션 인덱스 종류. 중간의 나뉜 네모가 파티션.

1. Local 파티션 인덱스
- 테이블 파티션과 1:1 대응 관계
- 인덱스 파티션 키를 따로 지정 x. DBMS가 자동으로 1대1 관계 유지하도록 관리해줌
2. Global 파티션 인덱스
- 테이블 파티션과 독립적으로 구성된 인덱스. 거의 안쓴다고 보면 됨.
3. Non Partitioned 인덱스
- 파티션 없는 테이블의 인덱스를 생성한 것처럼 동일하게 생성됨 
 
Prefixed, Nonprefixed
-> 그냥 인덱스 선두컬럼에 파티션 키가 있냐 없냐.. 
 
✔️ 파티션 인덱스를 사용하면 Sort Order by 효과가 상실됨
따라서 논 파티션 인덱스를 사용하면 부분범위 처리가 가능함.
다만, 테이블 파티션 이동, 삭제 등 작업이 있다면 인덱스가 Unusable 됨.
 
 

배치 프로그램 튜닝

정기배치, 이벤트성 배치, On-Demand 배치가 있음.
✔️ 절차형 프로그램
- Application 커서를 열고 루프 내에서 SQL 반복 처리 형태
- 반복적으로 DBMS Call 발생함
- 병목을 일으키는 개별 SQL을 찾아 I/O 튜닝함
- 최대한 One SQL로 변환
 
✔️ One SQL 위주 프로그램
- 병목을 일으키는 오퍼레이션을 I/O 튜닝
- Index Scan 보다 Full Table Scan
- NL 조인보다 Hash 조인
- 임시 테이블, 파티션, 병렬처리 등 활용
 
 

병렬처리 활용

 ✔️ Granule 
1. 블록 기반 Granule
- 파티션 여부와 상관없이 병렬도 지정 가능(개수가 파티션 개수와 별개로 지정 가능)
- Granule 할당은 QC가 수행함
- 실행 계획에 "PX BLOCK ITERATOR" 로 나옴
2. 파티션 기반 Granule
- 하나의 파티션은 하나의 병렬프로세스가 처리
- 병렬도는 최대 파티션 개수 만큼 지정 가능.
- "PX PARTITION RANGE ALL" 로 나옴
✔️ Partition table을 Table Full Scan 또는 Index Fast Full Scan 시에는 블록기반 Granule을 사용함.
 
Single 테이블 Parallel 시 

-- P2P 는 2번 발생
select /*+ FULL(A) PARALLEL(A 2) */ *
  from emp A
  
-- P2P는 병렬도(2) x 두배 = 4번 발생
select /*+ FULL(A) PARALLEL(A 2) */ *
  from emp A
 order by 1 -- order by 나 group by가 있으면 Single 테이블 병렬도가 두배 증가함.

' 병렬처리 활용은 교재 P502 참고하자..

✔️ pq_Distribute : 프로세스 간 통신을 어떻게 할 것인지 !
Range(독립적으로 구간 나눔), Broadcast(양쪽에 다 던짐), key, Hash, Round-Robin(한번은 얘, 한번은 쟤한테 주기)
 
➡️병렬 조인
1. Full Partition Wise Join
- 두 테이블 모두 Join 칼럼 기준으로 Partitioning 되어 있는 경우
2. Partial Partition Wise Join
- 하나의 테이블만 Join 컬럼 기준으로 Partitioning 되어 있는 경우
3. Dynamic Partition Wise Join
- 두 테이블 모두 Join 컬럼 기준으로 Partitioning 되어 있지 않은 경우
-> 동적 파티셔닝, Broadcasting 방식 사용

Full Partition Wise Join
- 왼쪽 그림. 양쪽 다 조인 키가 파티셔닝 되어있으므로 각 파티셔닝 끼리만 나눠서 조인하면 됨. 따라서 P2P가 발생할 이유가 없음. 프로세스도 병렬도 만큼만 생성됨
Partial Partition Wise Join
- 오른쪽 그림. 한쪽만 파티셔닝 되어있으므로 Full 처럼 나눠서 파티셔닝부분만 조인하면 조인하지 못한 부분이 무조건 발생함. 따라서 비파티션 테이블 부분에서 파티셔닝 테이블 쪽으로 BroadCast(양쪽 다에 보내기) 방식으로 PQ_Distribute 함. (요약. 비파티션 -> 파티션)

-- 비파티션 부분을 먼저 읽기. PQ_Distribute는 조인 순서나 방식을 제어하지 않으니 직접 기술 해야함. 
select /*+ leading(d) full(e) full(d) use_hash(e) 
      parallel(e 2) parallel(d 2) qg_distribute(e partition none) */

힌트 사용 법 : pg_distribute("inner table" "outer table의 방식" "inner table 방식")
오른쪽 그림에선 dept(outer)가 파티션 되야하는 대상이었으니까 두번째 인자에 partition을 주었음. inner는 이미 partition 되어있으니 none.

pg_distribute(e hash hash), pg_distribute(e broadcast none)

 
✅ Parallel 힌트를 사용할 땐 반드시 Full 힌트도 사용 할 것. index 스캔을 선택하면 parallel은 무시됨.
🔹QC : 병렬 SQL 문을 발행한 세션
🔹서버 프로세스 : 실제 작업을 수행하는 개별 세션들

✅ Intra : 독립적, Inter : 프로세스간 통신 발생

힌트 사용법!!

 
❗병렬 DML 수행 시 Exclusive Lock이 발생함.(Direct Path I/O!)
❗parallel 할 때 한쪽이 작고 한쪽이 크면 작은 쪽을  Broadcast 해라!!
 
 

쿼리 변환

✅ Unnesting
- 서브쿼리를 메인쿼리 레벨로 풀어내면 다양한 액세스 경로 및 조인 메소드를 사용 가능함.
힌트 : 
unnest : unnesting으로 유도
no_unnest : 서브쿼리를 unnesting 하지 않음. filter 방식 그대로 처리
 
✅서브쿼리를 unnesting 하는데(필터에서 조인으로 변경) 서브쿼리가 1:M에서 M쪽 집합이라면?
1. 서브 쿼리 쪽이 먼저 드라이빙 된다면
-> 먼저 Sort Unique(distinct) 오퍼레이션을 수행하여 1쪽 집합으로 변경 후 조인함

2. 메인 쿼리쪽이 드라이빙 된다면
-> 세미 조인!

 
✅ 실행계획 순서가 일반적이지 않은 세가지
1. Hash 조인
2. Scalar 서브쿼리
3. push_subq
 

  push_subq

 
✅push_subq은 조인 중간에 먼저 읽음으로써 "필터링" 하여 조인 모수를 줄일 때 많이 씀
위의 경우 C 테이블을 R.A하기전에 주문쪽 서브쿼리와 먼저 필터링 하면서 모수를 줄이고 고객 인덱스를 읽었다.
원래 조인 컬럼(위에서보면 C.고객번호) 컬럼은 Driving 테이블 기준에서 인덱스에 추가 하지 않아도 되는데, Push_subq를 활용하면 인덱스끼리 필터링 한 후에 R.A하기 때문에 인덱스끼리의 필터링 후 돌아올때 C.고객번호가 있다면 R.A에 훨씬 유리하게됨. 따라서 서브쿼리 조인 컬럼을 인덱스에 추가하면 성능이 훨씬 유리함.
-> 교재 p225 참고!

징검다리 조인때 생각하면 된다.

 

뷰 Merging

❗ 집합연산자, connect_by, Rownum, 집계함수, 분석함수는 Merging이 안된다.
✅ group by와 distinct는 가능하다
힌트 : /*+ push_pred */