[SQLP 막간정리 2] [2] 고급 SQL 튜닝(2)

(0) 실기

24번 ( 96 page )

SELECT a.*
	(SELECT NVL (MAX('Y'), 'N')
	FROM 자료전송이력
	WHERE 상담원ID = a. 상담원ID
	AND 상담일시 = a. 상담일시
    AND 등록일시 >= 상담일시
	AND ROWNUM = 1) 자료전송여부
FROM (SELECT 상담원ID, 상담일시, 상담접촉구분코드
		, 연락전화번호, 통화자명, 호식별번호, 상담처리상태코드, 조직ID
      FROM 고객상담
      WHERE 고객번호 = :cust_num
      ORDER BY 고객번호 DESC, 상담일시 DESC) a
WHERE ROWNUM <= 10;

⭐️ 25번 ( 97 page ) : 잘 모르겠음..

SQL > SELECT *
FROM (
  SELECT 고객번호 , MAX(변경일자) 변경일자 , MAX(변경구분코드)
  , KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경일련번호) 변경구분코드
  FROM 고객변경이력 CH
  WHERE EXISTS (
      SELECT 'X'
      FROM 고객 C
      WHERE 고객번호 = CH.고객번호
      AND 상태변경일자 〈= CH.변경일자
      AND 고객상태코드 = 'AI' )
  AND CH.변경일자 >= :CHG_DT
  GROUP BY 고객번호
)
WHERE 변경일자 =: CHG_DT
AND 변경구분코드 = 'D1';

• 왜 CH.변경일자 >= :CHG_DT 지?
• 인라인뷰 안에서 CH.변경일자 < :CHG_DT 인 데이터들은 바깥에서 다 버려지므로 !!

51번 ( 110 page )

CREATE TABLE T
PARALLEL 4
AS
  SELECT /*+ PARALLEL(주문 4) */ 고객번호, 주문일자, 상품번호, 주문량, 주문금액
  , ROW_NUMBER() OVER ( ORDER BY 고객번호, 주문일자, 주문순번) AS 주문일련번호
  FROM 주문;

52번 ( 111 page )

SQL > MERGE INTO 주문 T1
USING ( SELECT 고객번호 , 주문순번
		,ROW_NUMBER() OVER ( ORDER BY 고객번호 , 주문순번 ) AS 주문일련번호
		FROM 주문
		WHERE 주문일자 = TO_CHAR(SYSDATE, 'YYYYMMDD') ) T2
ON ( T1.주문일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
	AND T1.고객번호 = T2.고객번호
    AND T1.주문순번 = T2.주문순번 )
WHEN MATCHED THEN UPDATE
	SET T1.주문일련번호 = T2.주문일련번호;

53번 ( 112 page )

SQL > SELECT /*+ leading (c o d)
		full(c) full(o) full(d)
        parallel(c 4) parallel(o 4) parallel(d 4)
        use_hash(o) use_hash(d) no_swap_join_inputs
        pq_distribute(o broadcast none) 
        pq_distribute(d hash hash) */ *
	  FROM 고객 C, 주문 0, 배송 D
	  WHERE 0. 고객번호 = C.고객번호
	  AND D. 고객번호 = 0. 고객번호
	  AND D. 주문일자 = 0. 주문일자
	  AND D. 주문순번 = 0. 주문순번 ;

(1) 파티셔닝

(2) 대용량 배치 프로그램 튜닝

34번

• ⭐️Index Range Scan을 병렬로 처리할 때는 파티션 Granule이다.⭐️
• 파티션 개수보다 많은 병렬도를 지정하면 서버 리소스를 낭비하게 된다.

병렬처리에서의 Granule

• 데이터를 병렬로 처리할 때 일의 최소 단위를 'Granule'이라고 한다.
• 병렬 서버는 한 번에 하나의 Granule씩만 처리한다.

1. 블록 기반 Granule(=블록 범위 Granule)
   QC가 테이블로부터 읽어야 할 일정 범위(Range)의 블록을 각 병렬 프로세스에게 할당한다.
   병렬 프로세스가 한 Granule에 대한 일을 끝마치면 이어서 다른 Granule을 할당한다.
   따라서 프로세스 간 처리량에 편차가 거의 발생하지 않는다.

• ⭐️파티션 여부, 파티션 개수와 무관하게 병렬도를 지정할 수 있다.⭐️
• ⭐️병렬 Full Table Scan은 블록 기반 Granule이므로 파티션 개수보다 큰 병렬도를 지정해도 상관없다.⭐️

2. 파티션 기반 Granule(파티션 Granule)

• 한 파티션에 대한 작업을 한 프로세스가 모두 처리한다.
• Partition-Wise 조인 시
• 파티션 인덱스를 병렬로 스캔할 때(Index Range Scan, Index Full Scan)
• 파티션 인덱스를 병렬로 갱신할 때
• 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때
• 한 파티션을 두 개 프로세스가 함께 처리할 수 없다.
• 병렬도를 파티션 개수보다 크게 지정하면 서버 리소스를 낭비하게 된다.
• 병렬도를 파티션 개수 이하로 지정할 때는 블록 기반 Granule과 마찬가지로 먼저 일을 마친 프로세스에게 다음 파티션을 할당한다.

37번 : 병렬 쿼리 튜닝

• ⭐️병렬 Full Table Scan은 블록 기반 Granule이므로 파티션 개수보다 큰 병렬도를 지정해도 상관없다.⭐️
• 병렬로 처리하는 중간 단계에 PARALLEL_TO_SERIAL 오퍼레이션이 나타나면 병목 구간으로 작용한다.
• 병렬로 처리하는 과정에 큰 테이블을 단일 프로세스로 읽으면 병목 구간이 될 수 있다.

38번 : 병렬 조인 종류

38번

41번 : 병렬처리 힌트

• 쿼리 작동 순서
  • 상품 테이블 Full Scan
    • 블록 단위 Granule 2개
    • 브로드캐스트 방식으로 데이터 재분배
    • LEADING(A) FULL(A) PARALLEL(A 2)
  • 상품 테이블의 데이터를 브로드 캐스트 방식으로 두번째 서버 집합이 받음
    • PQ_DISTRIBUTE(B,BROADCAST,NONE)
  • 주문상품 테이블 Full Scan
    • 블록 단위 Granule 2개
    • FULL(B) PARALLEL(B 2)
  • 각 서버집합에서 상품과 주문 테이블 해시 조인
    • USE_HASH(B)
  • 조인된 결과를 QC로 보냄

답

/*+ LEADING(A) USE HASH(B)
FULL(A) FULL(B) PARALLEL(A 2) PARALLEL(B 2)
PO_DISTRIBUTE(B, BROADCAST, NONE) */

🍒 문제 해설

✅ pq_distribute 사용법

• pq_distribute(inner, none, none)
  • Full-Partition Wise Join으로 유도할 때 사용
  • 당연히, 양쪽 테이블 모두 조인 칼럼에 대해 같은 기준으로 파티셔닝(equi-partitioning) 돼 있을 때만 작동한다.
• pq_distribute(inner, partition, none)
  • Partial-Partition Wise Join으로 유도할 때 사용
  • outer 테이블을 inner 테이블 파티션 기준에 따라 파티셔닝하라는 뜻이다.
  • 당연히, inner 테이블이 조인 키 칼럼에 대해 파티셔닝 돼 있을 때만 작동한다.
• pq_distribute(inner, none, partition)
  • Partial-Partition Wise Join으로 유도할 때 사용
  • inner 테이블을 outer 테이블 파티션 기준에 따라 파티셔닝하라는 뜻이다.
  • 당연히, outer 테이블이 조인 키 칼럼에 대해 파티셔닝 돼 있을 때만 작동한다.
• pq_distribute(inner, hash, hash)
  • 조인 키 칼럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는 뜻이다.
  • 조인되는 테이블을 둘 다 파티셔닝해서 파티션 짝(Partition Pair)을 구성하고서 Partition Wise Join을 수행한다.
• pq_distribute(inner, broadcast, none)
  • outer 테이블을 Broadcast 하라는 뜻이다.
• pq_distribute(inner, none, broadcast)
  • inner 테이블을 Broadcast 하라는 뜻이다.

63,64번 : With 구문

✅ with 절을 처리하는 DBMS 내부 실행방식 2가지

• Materialize 방식
  • 내부적으로 임시 테이블을 만든다.
  • 만들어진 임시 테이블은 반복 재사용이 가능하다.
  • 실행계획에 'TEMP TABLE TRANSFORMATION' 라고 뜬다.
  • 'materialize' 힌트를 사용한다.
• Inline 방식
  • 참조된 횟수만큼 런타임시 반복 수행된다.
  • 임시테이블이 생성되지 않는다.
  • 실행계획에 'TEMP TABLE TRANSFORMATION' 라고 뜨지 않는다.
  • 'Inline' 힌트를 사용한다.