[SQLP복기모음] 핵심노트 2권

Yu River·2022년 9월 1일

SQLP

SQLP필기연습

목록 보기

27/35

파티셔닝

[1] 오라클 RANGE 파티셔닝 구문

[SQLP필기풀이]6장 고급SQL튜닝(1-4) 파티셔닝

1,2,3번

✅ RANGE 파티셔닝 구문

    PARTITION BY RANGE (컬럼명) (
        PARTITION P1 VALUES LESS THAN (컬럼 범위1) ,
        PARTITION P2 VALUES LESS THAN (컬럼 범위2) ,
        PARTITION P3 VALUES LESS THAN (컬럼 범위3) ,
        PARTITION P4 VALUES LESS THAN (컬럼 범위4) , ....
        PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
    );

[1] 파티셔닝 종류

[SQLP필기풀이]6장 고급SQL튜닝(1-4) 파티셔닝

4번, 5번, 6번, 7번, 8번, 9번, 10번, 11번, 12번, 13번,
14번, 15번, 16번, 17번, 18번, 19번, 20번, 21번, 22번, 23번

✅ 정적(Static) 파티션 Pruning
파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동한다.
액세스할 파티션이 쿼리 최적화 시점에 미리 결정된다.
실행계획의 Pstart(partition start)와 Pstop(partition stop) 컬럼에는 액세스할 파티션 번호가 출력된다.

✅ 동적(Dynamic) 파티션 Pruning

파티션 키 컬럼을 바인드 변수로 조회하며 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다.

실행 시점이 돼서야 사용자가 입력한 값에 따라 결정되며, 실행계획의 Pstart와 Pstop 컬럼에는 "KEY'라고 표시된다.

⭐️NL 조인할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.⭐️

[2] Local Partition 인덱스

⭐️테이블 파티션을 재구성(ADD/DROP/SPLIT/EXCHANGE 등)할 때 비파티션 인덱스와 Global 파티션 인덱스는 Unusable 상태로 변한다.⭐️
⭐️반면, Local 파티션 인덱스는 Unusable 상태로 변하지 않으며, 파티션도 자동으로 재구성되므로 관리의 편의성이 좋다.⭐️

✅ Local 파티션 인덱스

Local 파티션 인덱스는 테이블 파티션 속성을 그대로 상속받는다.

따라서 테이블 파티션 키가 주문일시면 인덱스 파티션 키도 주문일시가 된다.

Local 파티션 인덱스를 'Local 인덱스'라고 줄여서 부르기도 한다.

⭐️Local 파티션 인덱스는 테이블과 정확히 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해 준다.⭐️

⭐️테이블 파티션 구성을 변경(add, drop, exchange 등)하더라도 인덱스를 재생성할 필요가 없다.⭐️

변경작업이 순식간에 끝나므로 피크(peak) 시간대만 피하면 서비스를 중단하지 않고도 작업할 수 있다.

Local 파티션 인덱스의 장점은 이처럼 관리 편의성에 있다.

[3] Global Partition 인덱스

✅ Global 파티션 인덱스

Global 파티션 인덱스는 파티션을 테이블과 다르게 구성한 인덱스다.

구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우다.

비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다.

⭐️글로벌 파티션 인덱스는 테이블 파티션 구성을 변경(DROP, EXCHANGE, SPLIT 등)하는 순간 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성해 줘야 한다.⭐️

그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

[1] 배치 프로그램의 정의

[SQLP필기풀이]6장 고급SQL튜닝(5) 파티셔닝

26번 , 27 , 28 , 29 , 30 , 31 , 32
33 , 34 ,35 ,36 , 37 , 38 , 39 , 40
41 , 42번

✅ 배치 프로그램
일련의 작업들을 하나의 작업 단위로 묶어 연속적으로 일괄 처리하는 것
온라인 프로그램에서도 여러 작업을 묶어 처리하는 경우가 있으므로 이와 구분하려면 '사용자와의 상호작용(Interaction) 여부' 특징을 더 추가해야한다.
사용자와의 상호작용 없이 대량의 데이터를 처리하는 일련의 작업들을 묶어 정기적으로 반복 수행하거나
사용자와의 상호작용 없이 대량의 데이터를 처리하는 일련의 작업들을 묶어 정해진 규칙에 따라 자동으로 수행한다.
기업마다 업무 요건이 워낙 복잡 다양하므로 이 외에도 여러 가지 형태가 존재할 수 있다.
정기 배치 형태가 가장 일반적이다.

[2] 배치 프로그램의 종류

✅ 배치 프로그램의 종류 (수행주기 기준)

On-Demand 배치 :: 배치 프로그램이 자동으로 수행되는 주기는 월단위, 주단위, 일단위가 보통이지만, 요즘은 주기가 점점 짧아져 종종 실시간이 요구되기도 한다. 사용자가 요청한 시점에 바로 작업을 시작한다

정기 배치 : 정해진 시점(주로 야간)에 실행된다.

이벤트성 배치 : 사전에 정의해 둔 조건이 충족되면 자동으로 실행

✅ 배치 프로그램의 종류 (작성 형태 기준)

절차형으로 작성된 프로그램

SQL 결과집합을 루프 내에서 한건씩 Fetch한다.

SQL을 반복해서 수행하는 형태

인덱스 구성이 중요하다.

One SQL 위주로 작성된 프로그램

[3] 배치 프로그램의 특징

배치 프로그램은 DW/OLAP 시스템뿐만 아니라 OLTP(온라인 트랜잭션 처리) 시스템에서도 많이 활용한다.
실시간에 가까운(Near Real Time) 정보 서비스 요구가 늘면서 On-Demand 배치가 늘고, 트랜잭션을 일정량 모았다가 지연(deferred) 처리하는 배치 프로그램의 활용도 느는 추세다.
⭐️야간 배치 프로그램을 튜닝할 때는 개별 프로그램 수행시간을 단축하기보다 전체 프로그램 수행시간을 단축하는 데 더 큰 목표를 두어야 한다⭐️
배치 프로그램은 항상 전체 처리속도 최적화에 목표를 두고 튜닝해야 한다.

[4] 병렬 처리에서의 데이터 재분배 방식

✅ 데이터 재분배 방식의 종류

데이터 재분배는 병렬 서버 프로세스 간에 데이터를 재분배하는 방식이다.

RANGE 방식

order by 또는 sort group by를 병렬로 처리할 때 사용된다.

첫 번째 서버 집합은 데이터를 읽어 각 프로세스마다 처리 범위(예를 들어, A~G, H~M, N~S, T~Z)를 지정하여 그 두 번째 서버 집합의 정해진 프로세스에게 “정렬 키 값에 따라 분배한다.

두 번째 서버 집합은 정렬 작업을 맡는다.

QC는 각 서버 프로세스에게 작업 범위를 할당하고 정렬 작업에는 직접 참여하지 않는다.
정렬이 완료되고 나면 순서대로 결과를 받아서 사용자에게 전송한다.

BROADCAST 방식

QC 또는 첫 번째 서버 집합에 속한 프로세스들이 각각 읽은 데이터를 두 번째 서버 집합에 속한 “모든” 병렬 프로세스에게 전송하는 방식이다.

병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용한다.

KEY 방식

특정 칼럼(들)을 기준으로 테이블 또는 인덱스를 파티셔닝할 때 사용하는 분배 방식이다.

ROUND-ROBIN 방식

파티션 키, 정렬 키, 해시 함수 등에 의존하지 않고 반대편 병렬 서버에 무작위로 데이터를 분배할 때 사용한다.

HASH 방식

조인이나 hash group by를 병렬로 처리할 때 사용된다.

조인 키나 group by 키 값을 해시 함수에 적용하고 리턴된 값에 따라 데이터를 분배하는 방식이다.

[5] 병렬 처리에서의 Granule

✅ 병렬처리에서의 Granule

데이터를 병렬로 처리할 때 일의 최소 단위를 'Granule'이라고 한다.

병렬 서버는 한 번에 하나의 Granule씩만 처리한다.

블록 기반 Granule(=블록 범위 Granule)

QC가 테이블로부터 읽어야 할 일정 범위(Range)의 블록을 각 병렬 프로세스에게 할당한다.

병렬 프로세스가 한 Granule에 대한 일을 끝마치면 이어서 다른 Granule을 할당한다.

따라서 프로세스 간 처리량에 편차가 거의 발생하지 않는다.

⭐️파티션 여부, 파티션 개수와 무관하게 병렬도를 지정할 수 있다.⭐️

⭐️병렬 Full Table Scan은 블록 기반 Granule이므로 파티션 개수보다 큰 병렬도를 지정해도 상관없다.⭐️

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

한 파티션에 대한 작업을 한 프로세스가 모두 처리한다.

Partition-Wise 조인 시

파티션 인덱스를 병렬로 스캔할 때(Index Range Scan, Index Full Scan)

파티션 인덱스를 병렬로 갱신할 때

파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때

한 파티션을 두 개 프로세스가 함께 처리할 수 없다.

병렬도를 파티션 개수보다 크게 지정하면 서버 리소스를 낭비하게 된다.

병렬도를 파티션 개수 이하로 지정할 때는 블록 기반 Granule과 마찬가지로 먼저 일을 마친 프로세스에게 다음 파티션을 할당한다.

처리할 파티션이 없을 때 먼저 일을 끝마친 프로세스는 다른 프로세스가 일을 마칠 때까
지 기다리게 되므로 파티션간 데이터양에 편차가 심할 때 리소스를 낭비하게 된다.

[6] 병렬 조인 종류

참고링크

✅ Full Partition Wise 조인

같은 기준으로 파티션된 두 테이블을 조인할 때 사용하는 병렬 조인 방식

양쪽 테이블을 같은 기준으로 파티션하기 위해 데이터를 재분배할 필요가 없다.

병렬 프로세스를 2배수로 할당하지 않는다. 즉 , 하나의 서버집합만 필요하다.

파티션 기반 병렬처리이므로 파티션 갯수 이하로 병렬도를 제한한다.

✅ Partial Partition Wise 조인

첫 서버 집합이 비 파티션 테이블을 파티션 테이블 기준으로 동적 파티셔닝한다.

두 개의 서버집합이 필요하다.(Partition degree * 2 개 Process)

각 서버집합 간 데이터 통신이 발생한다.

[7] 병렬 처리시 파티셔닝 힌트

✅ 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 하라는 뜻이다.

[1] 병렬 해시조인에서의 FULL PARTITION WISE JOIN

[SQLP필기풀이]6장 고급SQL튜닝(5) 파티셔닝

45 , 46 , 47 , 48 , 49, 50번
⭐️HASH JOIN 오퍼레이션 위쪽에 "PX PARTITION RANGE ALL' 오퍼레이션이 나타난다면 PLL
PARTITION WISE JOIN 할 때의 실행계획이다.⭐️

[1] UNNEST / NO_UNNEST 힌트 사용법

⭐️LEADING 힌트는 조인할 때 쓰는것이다. 따라서 서브쿼리 안에 NO_UNNEST가 붙으면 LEADING 힌트도 쓰지 않는다.⭐️
UNNEST 관련 힌트는 서브쿼리 안에다가 쓰도록 한다.
UNNEST 와 NL_SJ은 짝꿍이다.

[2] 대량 데이터 DML 튜닝 힌트 정리

인덱스 해제 / 재등록 힌트

	ALTER TABLE TARGET_T MODIFY CONSTRAINT TARGET_T_PK DISABLE DROP INDEX;
    ALTER TABLE TARGET_T MODIFY CONSTRAINT TARGET_T_PK ENABLE NOVALIDATE;

NOLOGGING / LOGGING 힌트

	ALTER TABLE TARGET_T NOLOGGING;
    ALTER TABLE TARGET_T LOGGING;

병렬 힌트
- INSERT문에 APPEND 힌트 안 붙여도 된다.

	ALTER SESSION ENABLE PARALLEL DML;
    ALTER SESSION DISABLE PARALLEL DML;

TRUNCATE 문법

	TRUNCATE TABLE TARGET_T ;

[1] Index 스캔보다 Full 스캔이 유리할 때

-조회 대상 데이터가 전체 데이터의 약 10%에 육박할 때

[2] 조건절 내 사용자 정의 함수 실행 횟수

필터 조건일 경우 필터링 횟수만큼 실행되고 수직 액세스 조건인 경우 단 1회 수행된다.

[1] Decode 사용법

[SQLP필실기풀이]6장 고급SQL튜닝(2)

63 , 64 ,65
A = B 이면 X를 출력, A ≠ B 이면 Y를 출력

DECODE(A, B, X, Y)

A = B이면 X 출력,
A = C이면 Y 출력,
A ≠ B 이고 A ≠ C이면 Z 출력

DECODE(A, B, X, C, Y, Z)

[2] 오라클 누적 합계 구하기

필터 조건일 경우 필터링 횟수만큼 실행되고 수직 액세스 조건인 경우 단 1회 수행된다.

SUM(매출금액) OVER ( PARTITION BY 지점코드 ORDER BY 판매월 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS 누적매출

[3] with 구문

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

Materialize 방식

내부적으로 임시 테이블을 만든다.

만들어진 임시 테이블은 반복 재사용이 가능하다.

실행계획에 'TEMP TABLE TRANSFORMATION' 라고 뜬다.

'materialize' 힌트를 사용한다.

Inline 방식

참조된 횟수만큼 런타임시 반복 수행된다.

임시테이블이 생성되지 않는다.

실행계획에 'TEMP TABLE TRANSFORMATION' 라고 뜨지 않는다.

[4] merge/no_merge 실행계획

참고 링크 : https://velog.io/@yooha9621/SQLP필기풀이5장-SQL옵티마이저3-쿼리-변환
실행계획에 'VIEW' 오퍼레이션이 나타난다는 것은 Merging 하지 않았다는 뜻이다.

[5] Materialize 방식

✅ Materialize 방식

중간 집합을 만드는 과정에 원본 집합을 읽어 Temp 공간에 기록하는 과정을 수반하면서 DISK I/O가 발생한다.

따라서 원본집학이 작고 동시 수행 빈도가 높을 때에 사용하면 오히려 성능이 저하된다.

매우 많은 데이터를 읽어 group by , 조인 등을 통해 집합 크기를 많이 줄일 수 있을 때 수행하면 성능 개선에 도움이 된다.

해당 뷰를 읽을 때에도 버퍼 캐시를 경유하여 읽는다.

[1] TX Lock

[SQLP필기풀이]7장 LOCK과 트랜잭션 동시성 제어 (1) LOCK

1 ~ 15번
링크
트랜잭션이 첫 번째 변경을 시작할 때 얻고, 커밋 또는 롤백할 때 해제된다.
변경 중인 레코드(또는 기타 리소스)를 동시에 변경하려는 트랜잭션이 있으면 트랜잭션 Lock(TX Lock)을 사용하여 액세스를 직렬화한다.

오라클에서는 로우 Lock을 로우 단위 Lock과 TX Lock을 조합해서 구현한다.

로우를 갱신하려면 Undo 세그먼트에서 트랜젝션 슬롯을 할당받고
Enqueue 리소스를 통해 TX Lock을 획득한다.
- ⭐️TX Lock은 트랜잭션을 시작할 때 한 번만 획득한다.⭐️
insert, update, delete, merge 문장을 통해 갱신하는 각 로우마다 Exclusive 모드로 로우 단위 Lock을 획득한다.

[2] DML문 LOCK 호환성

SQL Server는 SELECT 문으로 데이터를 읽을 때 공유 Lock을 사용한다. 공유 Lock끼리는 호환되므로 서로 블로킹 하지 않지만, 공유 Lock은 배타적 Lock과 호환되지 않으므로 서로 불로킹 한다.
테이블에 Unique 인덱스나 제약이 없으면, INSERT 끼리는 서로 블로킹 하지 않는다.
테이블에 Unique 인덱스나 제약이 설정돼 있으면, 같은 값을 동시에 INSERT 하지 못한다.
후행 트랜잭션은 기다렸다가 선행 트랜잭션이 커밋하면 Unique 제약 위반 에러가 발생하고,롤백하면 INSERT를 진행한다.
INSERT 중인 데이터를 다른 트랜잭션이 읽거나 변경하거나 삭제하는 작업은 인덱스나 제약 유무에 상관없이 불가능하다.
MVCC 모델을 사용하는 오라클은 SELECT문 수행 시 어떤 Lock도 사용하지 않는다.
따라서 DML이 수행 중인 데이터를 어떤 간섭도 받지 않고 읽을 수 있다.
물론, 다른 트랜잭션이 읽고 있는 데이터를 변경할 때도 블로킹은 발생하지 않는다.

[3] 오라클에서의 Serializable 격리성

⭐️오라클에서 SELECT 문으로 데이터를 읽을 때는 Serializable 수준에서도 Lock을 전혀 사용하지 않는다.⭐️
⭐️따라서 트랜잭션 격리성 수준을 상향 조정했다고 해서 Lock 경합이 증가하거나 조회 성능이 느려지지는 않는다.⭐️

[1] NO LOGGIING 옵션

NO LOGGIING 옵션은 INSERT와 CTAS 문에서만 적용이 가능하다.

alter table t NOLOGGING;

CTAS를 쓰면 알아서 DIRECT_PATH_INSERT가 적용되므로 NOLOGGING만 추가로 설정해x튜닝한다.

 CREATE TABLE T1 NOLOGGING
 AS SELECT ~

[2] Direct Path Insert 힌트

NOLOGGING 모드는 힌트가 아니라 따로 모드를 지정해주는 명령어가 필요하다.
```
 ALTER TABLE AA NOLOGGING;
```

append 힌트와 NOLOGGING 모드는 보통 붙어다닌다.

 ALTER TABLE AA NOLOGGING;
 
  INSERT /*+ append */ INTO AA
  SELECT * FROM BB;

parallel 힌트와 병렬 DML 활성화는 붙어다녀야 한다.

 ALTER SESSION ENABLE PARALLEL DML;
 
  INSERT /*+ parallel(AA, 4) */ INTO AA
  SELECT * FROM BB;

[3] DB 저장형 사용자 정의 함수/프로시저의 Recursive Call 발생 횟수

SQL 프로그램에 내장된 SQL에 대해서는 오라클이 자동으로 커서를 캐싱하므로 Parse Call은 최초 1회만 발생한다.

[1] ⭐️ 부분범위 처리시 인덱스 정렬 순서 유의!

실전 모의고사 1회 19번
- 인덱스 구성이 '게시판유형코드 + 등록일시 + 질문유형코드 + 글번호' 이고
- 조건절은 게시판유형코드 = 'ABCD' 이고
- 부분 범위 처리에서 요청한 정렬 순서는 등록일시 DESC , 질문유형코드 , 글번호

다음과 같은 조건일 때 인덱스를 바로 사용하지 못한다. 왜냐하면 인덱스의 정렬이 등록일시 DESC , 질문유형코드 , 글번호 이런 구성이 아니기 때문이다.
따라서 제대로된 부분범위 처리를 하려면 '게시판유형코드 + 등록일시 DESC+ 질문유형코드 + 글번호' 구성의 인덱스를 만들어줘야한다.

또한 부분범위처리를 실행했더라면 모든 조인 , 함수 실행을 맨 바깥으로 빼는 게 그닥 성능에 도움을 주진 않는다.

[2] 실행계획에서 계산하기

버퍼캐시 히트율

봐야할 항목 : DISK 항목 , query 항목 , current 항목
- query 항목 = disk 항목 + current 항목

인덱스 클러스터링

인덱스를 통해 액세스했는지 먼저 판단
인덱스를 통해 몇개의 로우를 탐색했는지 판단 : Rows 항목
그 과정에서 총 몇 개의 블록에 액세스했는지 판단 : cr 항목

[1] group by 절 이외의 컬럼을 추출하기

[SQLP실기문제]6장 고급SQL튜닝(6) 고급 SQL 활용 57번

group by 절 이외의 컬럼을 추출하려는 경우 MIN(컬럼)을 이용한다.
(단 집계함수를 써도 영향받지 않는 데이터여야한다.)

SELECT 고객번호 , MIN(A.고객명) 고객명
, MIN(DECODE(B.연락처구분코드 , 'HOM', B.연락처번호 )) AS 집 
, MIN(DECODE(B.연락처구분코드 , 'OFC', B.연락처번호 )) AS 사무실
, MIN(DECODE(B.연락처구분코드 , 'MBL', B.연락처번호 )) AS 휴대폰 
FROM 고객 A , 고객연락처 B
WHERE A.고객구분코드 = 'VIP'
AND A.고객번호 = B.고객번호(+)
GROUP BY 고객번호;

[1] 무조건 부분범위 처리가 I/O량이 가장 적게 발생한다고 생각하면 안된다.

(1) [SQLP필기풀이]6장 고급SQL튜닝(1)-소트튜닝

[SQLP필기문제]6장 고급SQL튜닝(1)-소트튜닝 10번

무조건 부분범위 처리가 I/O량이 가장 적게 발생한다고 생각하면 안된다.
부분 범위 처리를 하는것 보다 선택도가 가장 작은 컬럼의 인덱스를 액세스하는 게 I/O량이 더 작다.
https://velog.io/@yooha9621/SQLP실기풀이-소트튜닝12번-rj0zat6k

[2] exists문 + no_unnest문

exists문 + no_unnest문도 nl_sj의 역할을 한다.

-- NL_SJ 조인시
SELECT /*+ LEADING(P) */
P. 상품번호, P. 상품명, P.상품가격, P.상품분류코드
FROM 상품 P
WHERE
P.상품유형코드 = :PCLSCD
AND EXISTS ( SELECT /*+ UNNEST NL_SJ */ 'X'
            FROM 계약 C
            WHERE C.상품번호 = P.상품번호
            AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12))
            );

-- 조인시도 안하고 EXSISTS 문으로 실행시
SELECT /*+ LEADING(P) */
P. 상품번호, P. 상품명, P.상품가격, P.상품분류코드
FROM 상품 P
WHERE
P.상품유형코드 = :PCLSCD
AND EXISTS ( SELECT /*+ NO_UNNEST */ 'X'
            FROM 계약 C
            WHERE C.상품번호 = P.상품번호
            AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12))
            );

[3] hash_sj 힌트는 없다.

13번

hash 조인을 할 때는 exists문도 쓰지 않는다.
hash 조인시 스캔하는 테이블의의 인덱스 분기가 10% 넘으면 풀스캔으로 진행한다.

(2) [SQLP필기풀이]6장 고급SQL튜닝(5) 대용량 배치 프로그램 튜닝

[1] 데이터 복제 + group by + rollup구현(decode)

55번

꼭 그룹바이절에 있는 컬럼명만을 select로 가져와야 하는 건 아니다.
- 그룹핑해서 하나의 행으로 담아내서 출력할수만 있다면 다 되는듯 ?

  SELECT DEPTNO , DECODE(LVL, 1, '부서계' ,2 ,EMPNO) AS 사원번호
  , SUM(SAL) 급여합 , ROUND(AVG(SAL)) 급여평균
  FROM EMP , (
      SELECT LEVEL AS LVL
    FROM DUAL
    WHERE CONNECT BY LEVEL <=2
      ) T1
  GROUP BY DEPNO , LVL , DECODE(LVL, 2, EMPNO)
  ORDER BY 1, 2;

[2] 아우터조인 누락 + group by로 한 행으로 만들기

57번

아우터 조인을 누락하지 말자
여러 행을 한 행으로 만들기 위해서는 group by와 case when (또는 DECODE) 문을 적절히 활용하자

  SELECT 고객번호 , MIN(A.고객명) 고객명
  , MIN(DECODE(B.연락처구분코드 , 'HOM', B.연락처번호 )) AS 집 
  , MIN(DECODE(B.연락처구분코드 , 'OFC', B.연락처번호 )) AS 사무실
  , MIN(DECODE(B.연락처구분코드 , 'MBL', B.연락처번호 )) AS 휴대폰 
  FROM 고객 A , 고객연락처 B
  WHERE A.고객구분코드 = 'VIP'
  AND A.고객번호 = B.고객번호(+)
  GROUP BY 고객번호;

[3] 59번

59번

트랜잭션 고립성 설정하는 명령어

	set transaction isolation level serializable;

[4] 배치 프로그램 특징

배치 프로그램 특징

27번 : 배치 프로그램은 항상 전체 처리속도 최적화에 목표를 두고 튜닝해야 한다.

[5] 병렬처리 특징

병렬 처리 특징

29번 : 병렬 처리에서 QC는 다른 병렬 서버들이 작업 완료하기를 기다리면서 대기 중인 병렬 프로세스를 찾아 자원을 OS에 반환하지는 않는다.
29번 : 병렬 처리에 사용한 병렬 프로세스는 모든 처리를 종료(SELECT 문장은 커서를 닫거나 결과 집합을 모두 Fetch)한 후에 일괄 해제하고 자원을 OS(또는 서버 풀)에 반환한다.
37번 : 병렬로 처리하는 중간 단계에 PARALLEL_TO_SERIAL 오퍼레이션이 나타나면 병목 구간으로 작용한다.
37번 : 병렬로 처리하는 과정에 큰 테이블을 단일 프로세스로 읽으면 병목 구간이 될 수 있다.(큰 테이블을 PARALLEL_FROM_SERIAL 방식으로 읽는지 확인)
49번 : 파티션 인덱스가 아니면, Index Range Scan, Index Full Scan은 병렬 처리가 불가능하다.
- ⭐️Index Range Scan, Index Full Scan은 파티션 Granule로 스캔되므로 병렬처리할 때 파티션 인덱스이어야 병렬 처리가 가능하고 파티션 인덱스가 아니면 병렬 처리가 불가능하다.⭐️
51번 : Unique 처리를 하는데 이 과정에서 병목현상이 생긴다.
👉 ROWNUM이 아닌 ROW_NUMBER()을 사용해 데이터 크기로 정렬해 일련번호를 부여하면 QC가 Unique 처리를 하는 과정을 생략할 수 있다.(다만 데이터 재분배는 필요하다.)

[6] 데이터 재분배 특징

데이터 재분배 특징

33번

P - P(PARALLELTO_PARALLEL) 오퍼레이션은 데이터를 재분배(redistribution)하는 오퍼레이션이다.
데이터 재분배 과정에 테이블 큐(Queue)를 사용한다.
⭐️병렬 프로세스 간 통신이 발생하므로 Inter-Operation Parallelism에 속
한다.⭐️
서버 프로세스가 병렬도(DOP)의 2배수로 생성된다.

38번

병렬 Order By 및 병렬 Group By
Broadcast 방식의 데이터 분배
Partial Partition Wise 조인

[7] FULL PARTITION WISE JOIN 특징

FULL PARTITION WISE JOIN 특징

38번

⭐️Full Partition Wise 조인은 병렬 프로세스를 2배수로 할당하지 않는다.
같은 기준으로 파티션된 두 테이블을 조인할 때 사용하는 병렬 조인 방식이다.
양쪽 테이블을 같은 기준으로 파티션하기 위해 데이터를 재분배할 필요가 없다.
즉 , 하나의 서버집합만 필요하다.⭐️

45번

⭐️HASH JOIN 실행계획 위에 PX PARTITION RANGE ALL이 있는 경우에는 이미 조인 후에 파티셔닝을 한 것으로 FULL PARTITION WISE JOIN이 일어난것이다.⭐️
FULL PARTITION WISE JOIN의 힌트는 PQ_DISTRIBUTE(T1, NONE , NONE)이다.
FULL PARTITION WISE JOIN은 파티션 개수보다 작거나 같은 개수의 DOP를 지정해야 한다.

[8] PARTIAL PARTITION WISE JOIN 특징

PARTIAL PARTITION WISE JOIN 특징

47번

PARTIAL PARTITION WISE JOIN의 힌트는 PQ_DISTRIBUTE(T1, PARTITION , NONE) 형태 이다.

[8] 파티션 Granule

파티션 Granule

34 , 35번

파티션 되어있는 인덱스를 병렬로 스캔하는 것은 파티션 기반 Granule이다.
파티션 기반 Granule에서는 파티션 개수보다 많은 병렬도를 지정하면 서버 리소스를 낭비하게 된다.
⭐️블록 기반 Granule은 파티션 여부, 파티션 개수와 무관하게 병렬도를 지정할 수 있다.⭐️
⭐️병렬 Full Table Scan은 블록 기반 Granule이므로 파티션 개수보다 큰 병렬도를 지정해도 상관없다.⭐️

[9] 병렬처리 힌트

병렬처리 힌트

41번 , 43번

작동 순서
- 상품 테이블 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) */

52번

update 구문 : update Table1 set COl1 = 1 WHERE COL2='b';
update 병렬 구문 : 52번

ALTER SESSION ENABLE PARALLEL DML;

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.주문일련번호;

(2) [SQLP필기풀이]7장 LOCK과 트랜잭션 동시성 제어 (1) LOCK
(3) [SQLP필기풀이]7장 LOCK과 트랜잭션 동시성 제어 (2) 트랜잭션

[1] LOCK

2번

온라인 트랜잭션을 처리하는 프로그램에 SELECT FOR UPDATE 문을 사용해선 안 된다. 👉 ❌
- 한 트랜잭션 내에서 나중에 변경할 목적으로 데이터를 읽을 때는 SELECT FOR UPDATE 문을 사용해야 한다.

[2] Lock 호환성 (SQL SERVER)

문제

5번

update 락을 select 할 수 없다.
- SQL Server에서 공유 Lock끼리는 호환되므로 서로 블로킹 하지 않지만, 공유 Lock은 배타적 Lock과 호환되지 않으므로 서로 불로킹 한다.

9번

테이블에 Unique 인덱스나 제약이 없으면, INSERT 끼리는 서로 블로킹 하지 않는다.
테이블에 Unique 인덱스나 제약이 설정돼 있으면, 같은 값을 동시에 INSERT 하지 못한다.
후행 트랜잭션은 기다렸다가 선행 트랜잭션이 커밋하면 Unique 제약 위반 에러가 발생하고,롤백하면 INSERT를 진행한다.
INSERT 중인 데이터를 다른 트랜잭션이 읽거나 변경하거나 삭제하는 작업은 인덱스나 제약 유무에 상관없이 불가능하다.

⭐️ 10번 ⭐️

인덱스 여부를 잘 보아야한다!!!(풀스캔하는지 원하는 범위만 스캔하는지)
INSERT 중인 데이터를 포함하지 않지만, 인덱스가 없으므로 ⭐️Full Scan⭐️으로 처리된다.따라서 INSERT 중인(= Lock이 걸린) 데이터를 읽고 지나가야 하므로 그 과정
에 블로킹 된다.

[3] Lock 호환성 (ORACLE)

5번

MVCC 모델을 사용하는 오라클은 SELECT 문 수행 시 어떤 Lock도 사용하지 않는다.따라서 DML이 수행 중인 데이터를 어떤 간섭도 받지 않고 읽을 수 있다.

12번

SQL Server에서는 SELECT와 INSERT가 서로 방해할 수 있지만, MWCC 모델을 사용하는 오라클에서는 서로 방해하는 일이 없다.

[4] TM Lock과 TX Lock

14번

오라클에서 ⭐️TX Lock은 트랜잭션별로 단 하나씩 설정⭐️하고, TM Lock은 DML을 수행하는 테이블 별로 하나씩 설정한다.

[5] 공유 Lock의 지속시간 (SQL SERVER)

문제

21번

Read Committed 격리성 수준에서는 레코드를 읽기 직전에 공유 Lock을 획득하고, 다음 레코드로 이동하는 순간에 Lock을 해제한다.
Repeatable Read 격리성 수준에서는 레코드를 읽기 직전에 공유 Lock을 획득하고, 최종 커밋 또는 롤백 하는 순간에 Lock을 해제한다.

[6] Lock의 지속시간 (ORACLE)

문제

21번

오라클에서 SELECT 문으로 데이터를 읽을 때는 Serializable 수준에서도 Lock을 전혀 사용하지 않는다.

[7] ⭐️ 트랜잭션 컬럼 최종값 문제

문제

중요 !!

오라클처럼 MVCC(Multi-Version Concurrency Control) 모델을 사용하는 DBMS는 UPDATE 문이 시작된 시점을 기준으로 갱신 대상을 식별한다. 만약 대상으로 식별된 레코드 중 UPDATE문 시작 이후에 조건절 값이 변경된 레코드가 발견되면, 일관성 확보를 위해 UPDATE 문을 재시작한다.조건절 값이 변경된 레코드가 발견되지 않으면 그대로 UPDATE를 진행한다.
SQL Server처럼 MVCC 모델을 사용하지 않는 DBMS는 (UPDATE 문 시작 시점이 아니라) 레코드에 도달한 시점을 기준으로 갱신 대상을 식별한다.

26번

오라클에서는 ㉡UPDATE를 시작하는 시점에서의 값을 본다.즉 , 커밋되지 않은 값으 볼 수 없음. C1 = 2인 레코드는 없으므로 어떤 변경도 일어나지 않는다.
C1 = 2인 레코드를 UPDATE 하려는 TX1은 블록킹 됐다가 TX2가 COMMIT을 수행하고 나면 Lock을 획득하고 갱신을 시작한다.
UPDATE를 시작한 시점에는 조건절 컬럼 C1의 값이 2여서 기다렸다가 갱신하려고 보니 4로 변경된 사실을 발견한 TX1은 UPDATE 문을 ⭐️다시 실행한다.⭐️
다시 실행한 시점에는 C1 = 2인 레코드가 없으므로 어떤 처리도 일어나지 않는다.

29번

SQL Server에서 TX2 트랜잭션은 TX1 트랜잭션이 완료될 때까지 기다린다. TX1이 끝났을 때 7788 사원의 SAL 값은 4000이므로(⭐️커밋전이라도!!⭐️) TX2 트랜잭션이 정상적으로 진행해 값을 3000으로 바꾼다.
오라클은 Update 문이 시작되는 시점을 기준으로 갱신 대상 레코드를 식별(⭐️커밋전 데이터는 무시 !!⭐️)하므로 TX2 트랜잭션의 Update는 기다리지도 않고 바로 실행을 종료한다. 따라서 TX1 트랜잭션에 의해 4000으로 변경된 값이 7788 사원의 최종 SAL 값이 된다.

[1] 부분범위 처리 안 order by 처리 제대로

50번

order by를 바깥에도 쓰도록 해야한다 꼭 !!

[2] 부분범위 처리에 INDEX_DESC 힌트 사용

51번

[3] Index_FFS + 해시조인으로 범위 줄이기

52번

⭐️ MIN(B.등록일시) 등록일시 추가 해야하는지 ?

[4] ⭐️ 부분범위처리 + nl조인 동시에 처리하기

⭐️ 꼭 부분범위 처리 후 바깥에서 조인할 필요는 없다.

53번

⭐️ 그냥 안에다가 같이 조인하고 밖에 rownum을 설정해도 된다고 !!!

[5] 비관적 동시성제어와 낙관적 동시성 제어

31번

✅ 비관적 동시성 제어 vs. 낙관적 동시성 제어

비관적 동시성 제어

비관적인 시각으로 동시성을 제어할 때는 현재의 트랜잭션이 데이터를 읽어서 연산하는 과
정에 다른 트랜잭션이 해당 데이터를 수정할 가능성이 매우 높다고 보고 데이터를 읽는 시
점부터 아예 Lock을 설정한다.

낙관적 동시성 제어

낙관적인 시각으로 동시성을 제어할 때는 현재의 트랜잭션이 데이터를 읽어서 연산하는 과
정에 다른 트랜잭션이 해당 데이터를 수정할 가능성이 매우 낮다고 보고 데이터를 읽는 시점에는 일단 Lock을 설정하지 않는다.

하지만, 그 데이터를 다른 트랜잭션이 변경했을 가능성이 있으므로 이후에 같은 데이터를 다시 읽거나 변경할 때는 반드시 변경 여부를 확인해야 한다.

LOCK을 설정하지 않으므로 LOCK에 의한 성능저하를 예방한다.
34번

낙관적 동시성 제어

데이터를 다른 트랜잭션이 변경했을 가능성이 있으므로 ⭐️이후에⭐️ 같은 데이터를 다시 읽거나 변경할 때는 반드시 변경 여부를 확인해야 한다.

[6] WAIT 또는 NOWAIT 옵션

33번

update에는 wait, nowait 옵션이 없다.
FOR UPDATE 절에 WAIT 3 옵션을 지정하면, Lock이 걸린 레코드를 만났을 때 최대 3초를 기다린다. 중간에 Lock이 해제되면 Lock을 설정하고 데이터를 읽겠지만, 3초를 기다렸는데도 해제되지 않으면 SELECT 문 전체를 종료한다.

Yu River

도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’