초보 DBA의 일상

6.3 파티션을 활용한 DML 튜닝

6.3.1 테이블 파티션

- 파티셔닝은 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세크먼트에 나눠서 저장하는 것

<파티션이 필요한 이유>

(1) 관리적측면 : 파티션 단위 백업, 추가, 삭제, 변경 --> 가용성 향상 

(2) 성능 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산.

1) Range 파티션  (오라클 8i 이후)

- 주로 날짜 컬럼을 기준으로 파티셔닝함.

* 파티션 테이블에 대한 SQL 성능 향상 원리는 파티션 Pruning에 있음. --> 파티션 프루닝이란 SQL 하드파싱이나 실행 시점에 조건절을 분석해서, 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능.

* 파티션도 클러스터, IOT와 마찬가지로 관련있는 데이터가 물리적으로 인접하도록 저장하는 클러스터링 기술에 속함.

-> 클러스터는 데이터를 블록단위로 모아 저장하지만, 파티셔닝은 세그먼트 단위로 모아서 저장한다. 그리고 IOT는 데이터를 정렬된 순서로 저장하는 구조. (추가 공부 필요.)

2) 해시파티션 (오라클 8i 이후)

- 파티션 키 값을 해시 함수에 입력해서 반환 받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식. 

- 사용자는 파티션의 개수만 결정하고, 오라클 내부 해시함수가 데이터 분산 함. 

- 해시 파티션의 파티션 기준 선정은 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 선정해야 효과적. 

- 조건절 비교값에 똑같은 해시 함수를 적용함으로써 읽을 파티션을 결정.  -- 테스트 필요

- 해시 알고리즘 특성상 등치 조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning 작동

3) 리스트 파티션 (9i이후)

- 사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

6.3.2 인덱스 파티션

- 테이블 파티션과 인덱스 파티션은 구분되어야 함. 

<인덱스 종류>

(1) 로컬 파티션 인덱스 - 각 테이블 파티션과 인덱스 파티션이 1:1 대응 관계가 되도록 오라클이 자동으로 관리. (예를들어, 옷장서랍을 계절별로 나눠고 계절별로 별도 색인을 만드는 것)

(2) 글로벌 파티션 인덱스 - 로컬이 아닌 파티션 인덱스는 '모두' 글로벌 파티션 인덱스이며, 테이블 파티션과는 독립적인 구성을 갖는다

(3) 비파티션 인덱스 

1) 로컬 파티션 인덱스 

- 오라클이 자동으로 관리해주므로 , 테이블 파티션 구성을 변경 하더라도 인덱스를 재생성할 필요가 없음. 

- 피크 시간대가 아니면 서비스 중단없이 작업 가능 

2) 글로벌 파티션 인덱스

- 파티션이 테이블과 다르게 구성된 인덱스. 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른경우. 

- 비파티션 테이블이어도 인덱스는 파티셔닝 가능 - 인덱스만 파티션 하는 것은 어떠한 경우에 사용하는 것이지..?! 

- 글로벌 파티션 인덱스는 테이블 파티션 구성을 변경하는 순간 UNUSABLE상태로 변경되므로 바로 인덱스 재생성이 필요하며 그동안 해당 테이블을 사용하는 서비스 중단해야 함. 

3) 비파티션 인덱스 

- 파티셔닝하지 않은 인덱스

- 비파티션 인덱스는 테이블 파티션 구성을 변경하는 순간 Unusable상태로 변경되므로, 곧바로 인덱스 재생성 필요. 그동안 해당 테이블을 사용하는 서비스 중단

Prefixed VS. Nonprefixed 

- 인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두컬럼에 위치하는지에 따른 구분.

[Comment]

진도는 나가고 있지만 이게 정말 체득이 되고 있는지 잘 모르겠다.. ㅠ

읽을 땐 정말 이해하고 있는 것 같은 데 막상 적용하려고 하면 아무런 생각이 안나는 걸 보면 ..  더 깊이 있는 공부가 필요할 것 같다..

보고 또 보고.. 반복 하다보면 언젠가 자유롭게 실무에 적용할 수 있겠지..?

[스터디 서적]

[목차]

5장 소트 튜닝

5.1 소트 연산에 대한 이해 

5.2 소트가 발생하지 않도록 SQL 작성

5.3 인덱스를 이용한 소트 연산 생략

5.4 Sort Area를 적게 사용하도록 SQL 작성

5.1 소트연산에 대한 이해 

- SQL 수행도중 가공된 데이터 집합이 필요할 때 오라클은 PGA와 TEMP 테이블 스페이스를 활요하며, 소트머지 조인, 해시 조인, 데이터 소트와 그룹핑 등이 대표적이다. 

5.1.1 소트 수행 과정

- 기본적으로 PGA에 할당된 Sort Area에서 이루어지고, 메모리공간인 Sort Area가 다 차면 디스크 Temp 테이블 스페이스를 활용함. 

(1) 메모리소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말함. Internal Sort 라고도 함. 

(2) 디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것을 말함. External Sort 라고도 함.

- 소트 연산은 메모리 집약적일 뿐만 아니라 CPU집약적이므로, 디스크 소트가 발생하는 순간 SQL 수행 성능은 나빠질 수 밖에 없다. 많은 서버 리소스를 사용하고 디스크I/O 발생하는 것도 문제지만, 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주 원인이 되기도 한다. 

* 소트가 발생하지 않도록 SQL 작성해야하며, 소트가 불가피하다면 메모리내에서 수행을 완료할 수 있도록 작성해야 함어떤 것

5.1.2 소트 오퍼레이션 

(1) Sort Aggregate 

참고서적 : 친절한SQL튜닝 (조시형 저)

4.4.1 서브쿼리 변환이 필요한 이유

- 최근 옵티마이저는 비용을 평가하고 실행계획은 생성하기에 앞서 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업, 즉 쿼리 변환부터 진행

- 쿼리 변환 : 옵티마이저가 같은 결과집합을 생성하면서도 더 나은 성능이 기대되는 형태로 쿼리를 재작성하는 것. 

4.4.4 스칼라 서브쿼리 조인 

(1) 스칼라 서브쿼리 특징

- 스칼라 서브쿼리는 메인쿼리의 건수만큼 테이블을 반복해서 읽는다는 측면에서, 함수와 비슷해보이지만 함수는 재귀적으로 반복실행된다는 점에서 차이가 있다. 

- Outer Join문처럼 이해하면 된다 -> NL 조인방식으로 실행, 다른점은 처리하는캐싱으 과정에서 캐싱작용이 일어남. 

(2) 스칼라 서브쿼리의 캐싱 

- 오라클 내부적으로 입력값과 출력값을 내부캐시에 저장해 둠. (수행횟수 최소화)

- 캐싱은 쿼리 단위로 이뤄지고 쿼리 시작할때 PGA메모리 공간을 할당하고, 쿼리를 수항하며 공간을 채워나가다가 쿼리 마치면 공간 반환

- 튜닝예제 : 재귀적으로 반복되는 함수를 SELECT에서 호출할 때 , 스칼라 서브쿼리를 덧씌운다면 캐시 효과 볼 수 있음.

(3) 스칼라 서브쿼리 캐싱 부작용

- 캐시 공간은 늘 부족함.

- 스칼라 서브쿼리 캐싱 효과는 입력값의 종류가 소수여서, 해시충돌 가능성이 작을 때  효과적

<스칼라 서브쿼리 캐싱이 성능에 도움을 주지 못하는 경우 >

1) 입력값의 종류가 다수여서 캐시를 매번 확인하는 비용이 더 클 때 오히려 성능저하

2) 메인쿼리 집합이 매우 작은 경우 

   - 스칼라 서브쿼리 캐싱은 쿼리 단위로 이루어지므로 메인 쿠리 집합이 클수록 재사용성이 높아 효과가 크다

(4) 두 개 이상의 값 변환

- 스칼라 서브쿼리는 두 개 이상의 값을 반환할 수 없음 -> 두번의 서브쿼리를 작성한다면 서브쿼리내 같은 데이터를 반복해서 읽는 비효율이 발생함 

1) 스칼라 버브쿼리를 이용할 경우, 전통적으로 원하는 값을 문자열로 합쳐서 가져온 후 분리해서 사용함

2) 인라인뷰를 사용 

(5) 스칼라 서브쿼리 Unnesting (12c부터 Unnesting가능)

- NL방식으로 조인하므로 캐싱효과가 크지 않으면 랜덤 I/O부하 발생

- Unnesting을 이용하여 다른 조인방식 사용가능

 12c 관련 파라미터값 : 옵티마이저가 사용자를 대신하여 쿼리 변환

                               _optimizer_unnest_scalar_sq = false (사용자가 unnest힌트로 변경가능)참고서적 : 친절한SQL튜닝 (조시형 저)

4.4.1 서브쿼리 변환이 필요한 이유

- 최근 옵티마이저는 비용을 평가하고 실행계획은 생성하기에 앞서 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업, 즉 쿼리 변환부터 진행

- 쿼리 변환 : 옵티마이저가 같은 결과집합을 생성하면서도 더 나은 성능이 기대되는 형태로 쿼리를 재작성하는 것. 

4.4.4 스칼라 서브쿼리 조인 

(1) 스칼라 서브쿼리 특징

- 스칼라 서브쿼리는 메인쿼리의 건수만큼 테이블을 반복해서 읽는다는 측면에서, 함수와 비슷해보이지만 함수는 재귀적으로 반복실행된다는 점에서 차이가 있다. 

- Outer Join문처럼 이해하면 된다 -> NL 조인방식으로 실행, 다른점은 처리하는캐싱으 과정에서 캐싱작용이 일어남. 

(2) 스칼라 서브쿼리의 캐싱 

- 오라클 내부적으로 입력값과 출력값을 내부캐시에 저장해 둠. (수행횟수 최소화)

- 캐싱은 쿼리 단위로 이뤄지고 쿼리 시작할때 PGA메모리 공간을 할당하고, 쿼리를 수항하며 공간을 채워나가다가 쿼리 마치면 공간 반환

- 튜닝예제 : 재귀적으로 반복되는 함수를 SELECT에서 호출할 때 , 스칼라 서브쿼리를 덧씌운다면 캐시 효과 볼 수 있음.

(3) 스칼라 서브쿼리 캐싱 부작용

- 캐시 공간은 늘 부족함.

- 스칼라 서브쿼리 캐싱 효과는 입력값의 종류가 소수여서, 해시충돌 가능성이 작을 때  효과적

<스칼라 서브쿼리 캐싱이 성능에 도움을 주지 못하는 경우 >

1) 입력값의 종류가 다수여서 캐시를 매번 확인하는 비용이 더 클 때 오히려 성능저하

2) 메인쿼리 집합이 매우 작은 경우 

   - 스칼라 서브쿼리 캐싱은 쿼리 단위로 이루어지므로 메인 쿠리 집합이 클수록 재사용성이 높아 효과가 크다

(4) 두 개 이상의 값 변환

- 스칼라 서브쿼리는 두 개 이상의 값을 반환할 수 없음 -> 두번의 서브쿼리를 작성한다면 서브쿼리내 같은 데이터를 반복해서 읽는 비효율이 발생함 

1) 스칼라 버브쿼리를 이용할 경우, 전통적으로 원하는 값을 문자열로 합쳐서 가져온 후 분리해서 사용함

2) 인라인뷰를 사용 

(5) 스칼라 서브쿼리 Unnesting (12c부터 Unnesting가능)

- NL방식으로 조인하므로 캐싱효과가 크지 않으면 랜덤 I/O부하 발생

- Unnesting을 이용하여 다른 조인방식 사용가능

 12c 관련 파라미터값 : 옵티마이저가 사용자를 대신하여 쿼리 변환

                               _optimizer_unnest_scalar_sq = false (사용자가 unnest힌트로 변경가능)