[SQLD/SQLP] 전문가가이드 3과목 5장 고급SQL튜닝 암기 요약 정리 (+오라클성능고도화)

3과목 5장 고급SQL튜닝 요약정리

5장에서는 소트튜닝관련 내용과

여러가지 튜닝기법 및 병렬/분산 처리에 대해 설명하고 있습니다.

시험을 위해서는 어느정도까지..공부하면 되겠지만

실제 업무에 활용하려면 책 내용보다 훨씬 더 많은 내용을 공부해야하는것 같습니다

---

Sort Area

- DML문장은 하나의 Execute Call내에서 모든 데이터 처리를 완료하므로

Sort Area가 CGA할당, Select문장은 수행중간 단계 필요한 Sort Area는 CGA 최종결과집합을

출력하기 직전단계에 필요한 Sort Area는 UGA에 할당

소트생략 오퍼레이션

create index t_idx on t( a, b, c, d );

select * from t where a = 1 order by a, b, c; --소트 생략

select * from t where a = 1 and b = 1 order by  c, d; --소트 생략

 

select * from t where a = 1 and c = 1 order by  b, d; --소트 생략

 

select * from t where a = 1 and b = 1 order by  a, c, b, d;  --소트 생략

--소트 생략

select * from t

where a between 1 and 2

and b not in ( 1, 2 )

and c between 2 and 3

order by a, b, c, d;

--소트 생략

select * from t

where a between 1 and 2

and c between 2 and 3

order by a, b, c;

--소트 생략

select * from t

where a between 1 and 2

and b <> 3

order by a, b, c;

--소트 생략 : IFS

select /*+ index( t ) */ * from t

where b between 2 and 3

and b <> 3

order by a, b, c, d;

--소트 발생

select * from t where a = 1 order by c;

--소트 발생

select * from t 

 where a = 1 

   and b between 1 and 2   

order by c, d;

--소트 발생

select * from t 

 where a = 1 

   and b between 1 and 2   

order by a, c, d;

Sort Group by / Hash Group by

- 정렬된 group by 결과를 얻고자 한다면, 실행계획에 설령 'sort group by'라고 표시되더라도

반드시 order by명시

소트를 발생시키는 오퍼레이션

1. Sort Aggregate

2. Sort Order By

3. Sort Group By(Hash Group By - order by를 생략하면 Hash로 풀림)

4. Sort Unique(Unnesting된 서브쿼리가 M쪽집합이거나 Unique 인덱스가 없다면, 그리고 세미조인으로 수행되지도 않는다면 메인쿼리와 조인되기 전에 sort unique오퍼레이션 먼저 수행 더해서 union, minus, intersect도 sort발생)

5. Sort Join

6. Window Sort

인덱스가 소트연산을 대체하지 못하는 경우

1. 인덱스를 사용하지 않고 Full table scan했을 때

2. 인덱스 지정컬럼이 not null컬럼이 아니라 옵티마이저가 full table scan으로 했을 때

 

병렬 DML활용

- alter session enable parallel dml;

페이징처리 12C추가

- offset 10 rows fetch next 10 rows only

클러스터/파티션 차이

- 클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 저장

 파티션은 세그먼트 단위로 저장

- with구문 활용

Materialize 방식 : 내부적으로 임시테이블을 생성함으로써 반복재사용 (with절 사용된 sql에서만 유지)

Inline 방식 : 물리적으로 임시테이블을 생성하지 않으며, 참조된 횟수만큼 런타임시 반복수행

단순히 코딩간단하게 하려하기위함

-테이블큐

병렬도 * 2 = 서버프로세스

병렬도제곱 = 파이프라인필요수

- 병렬프로세스간 데이터 재분배

Range : order by, sort group by를 병렬로 처리할 때

Hash : 조인이나 hash group by를 병렬로 처리할 때

BroadCast : 두번째 서버집합에 읽은데이터를 모두 

Key : 특정 칼럼을 기준으로 테이블 또는 인덱스파티션할때

Round-Robin : 파티션키,정렬키, 해시함수에 의지하지 않고 반대편정렬서버에 무작위

- pq_distribute 힌트사용법

pq_distribute(inner table명, outer distribute방식, inner distribute방식)

pq_distribute(inner, none, none) : Full-partition wise join, 양쪽테이블 모두 조인칼럼에 대해 같은기준으로 파티셔닝돼 있을때만 작동

pq_distribute(inner, partition, none) : outer 테이블을 inner 파티션 기준에 따라 파티셔닝하라는뜻

pq_distribute(inner, none, partition ) : inner 테이블을 ouiter 파티션 기준에 따라 파티셔닝하라는뜻

pq_distribute(inner, hash, hash ) : 조인키 칼럼을 해시 함수에 적용하고 반환된값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는뜻

pq_distribute(inner, broadcast, none ) : outer 테이블을 broadcast하라는뜻

pq_distribute(inner, none, broadcast ) : inner 테이블을 broadcast하라는뜻

- distinct, minus 같은 연산자를 포함한 sql은 부분범위처리가 불가능하다.

-인덱스파티셔닝 적용가이드

Not Null조건여부에 따른 실행계획 차이

인덱스 Access Predicate 와 Filter Predicate 가 다른 조건절

좌변 컬럼을 가공

왼쪽 '%' 혹은 양쪽 '%' LIKE

같은 컬럼에 대한 조건절이 두개 이상

위와 같은 케이스를 제외하고 유효 인덱스 선택도와 유효 테이블 선택도는 항상 같다.

인덱스가 Sort 연산을 대체하지 못하는 경우

1.옵티마이져모드가 all_rows 인 경우 풀스캔 가능성이 더 커진다. 풀스캔시 Sort 연산 수행

2.옵티마이져모드가 first_rows 인 경우 인덱스스캔 가능성이 더 커진다. 인덱스 스캔시 Sort 연산 대체

3.인덱스 항목이 NOT NULL 이 아닌 경우 : 인덱스만으로 모든 행을 가져오지 못하므로 인덱스 못탐(널 행 제외)

4.결합인덱스의 경우엔 널값도 저장된다(순서상 맨 아래쪽에 저장) 따라서 다음의 경우 Sort 연산 대체 안됨

5.ORDER BY 컬럼 NULLS FIRST

6.ORDER BY 컬럼 DESC NULLS LAST

인덱스 파티션 키가 인덱스 선두컬럼이어야 한다는 제약은 Global파티션 인덱스

Direct Path Insert

- Undo Log 불필요

- no logging모드로 insert할 시 redo log도 불필요

- Exclusive 모드 Table Lock이 걸림

대량의 데이터 일반 update문으로 갱신하면 오래걸리는 이유

1. 테이블 데이터를 갱신하는 본연의 작업

2. 인덱스 데이터까지 갱신

3. 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신

4. 내부적으로 redo와 undo생성

5. 블록에 빈 공간이 없으면 새 블록 할당(row migration 발생)

해결법

-> 1. 복사한 임시테이블 생성 (삭제인경우 조건에 맞는 임시테이블만 생성하도록)

     2. 기존테이블 제약조건 전부 삭제 및 truncate table

     3. 복사한 임시테이블에서 조건에 맞게 필요한 조건절을 걸어 기존테이블로 이동

     4. 기존테이블 제약조건 다시 생성

파티션

일반 Table과 세그먼트 1:1

파티션 Table과 세그먼트 1:M, 인덱스도 동일

병렬처리

full(o) parallel(o, 4)

index_ffs(o, 주문idx) parallel_index(o, 주문_idx, 4)

병렬 DML수행 시 Exclusive Lock 조심

Sort Area 할당위치

dml문장은 하나의 execute call내에서 모든 데이터 처리를 완료하므로 CGA에 할당

Select문에서의 데이터 정렬은 상황에 따라 다르다.

select 문장이 수행되는 가장 마지막단계에서 정렬된 데이터는 계속 이어지는 Fetch Call에서 사용되어야 한다.

마지막 소트를 위한 Sort Area는 UGA에 할당, 반면 마지막보다 앞선 단계에서 정렬된 데이터는 첫 번째 Fetch Call내에서만

사용되므로 CGA에 할당

*처음 데이터를 Fetch하기 전 단계에서의 데이터 가공은 Execute Call내에서 처리될 것이라는 믿음과 달리

실제로는 첫 번째 Fetch Call에서 이루어진다.