13. 데이터 엑세스 최적화

13.1 비효육적인 엑세스 패스 식별

13.1.1 식별

엑세스 최적화

가장 효육적인 액세스 패스는 최소의 자원을 사용하여 데이터를 처리하는 것
하지만, 이부분에 대함 검증이 필요하며 검증에 많은 시간을 소비해서는 안된다.
또한 가장 효율적인 액세스 패스가 항상 가장 빠른 것은 아니라는 것을 명심해야 한다.
초기 접근 방법은 액세스 패스가 사용하는 자원의 양이 반환되는 건수에 비례하다면 수용가능하다.

DB의 경우 논리적 읽기 횟수로 측정가능
1. 5가지 이유
2. 1) 논리적 읽기는 CPU사용을 반영한다.
3. 2) 논리적 읽기는 물리적 읽기를 반영한다.
4. 3) 논리적 읽기는 직렬로 수행되어야 하는 작업이다.
5. 4) 논리적 읽기는 SQL트레이스,동적성능뷰 에서 쉽게 얻을 수 있다.
6. 5) 논리적 읽기 횟수는 CPU와 디스크 I/O 서브시스템에 가해지는 부하에 독립적이다.

최적화 기준

좋음 : 5건 > Buffer gets / A-rows
보통 : 10건 < Buffer gets / A-rows < 15건
나쁨 : 20건 < Buffer gets / A-rows

논리적 읽기 횟수 측정방법

1) dbms_xplan 패키지
A-rows 대비 Buffers 값에 대한 비교
--> buffers 은 SQL트레이스 TKPROF 유틸리티 결과의 cr 과 동일
단,집계함수나 다른 테이블과의 결합이 있는지를 확인하고 정확한 엑세스 패스에 대한 고려가 필요하다.

13.1.2 함정

1) 읽기 일관성

데이터의 읽기일관성을 보장하기 위해서 current블록에 대한 언두 블록을 사용하여 데이터 블록의 consistent 사본이 런타임에 생성된다.
이와 같은 실행을 위해서 몇번의 논리적 읽기가 추가적으로로 더 발생한다.
따라서 consistent 블록의 재구성 개수에 따라서 크게 논리적 블록의 읽기 수가 크게 달라진다.

2) 로우 프리페칭

논리적 읽기는 데이터베이스 엔진이 블록을 한번읽어드릴때마다 1개씩 증가한다.
로우 프로패칭값이 해당 데이터의 로우에 가까울 수록 논리적 읽기는 증가하고 높으면 줄어든다.
다만, 실제 운영시스템에 반영하기전에 해다 테스트는 운영 어플리케이션의 값과 일치시켜야 왜곡을 피할 수 있다.

13.1.3 원인

1) 적절한 액세스 패스가 없는 경우
2) 적절한 액세스 패스는 존재하지만 쿼리 옵티마이저가 이를 사용하지 못하는 경우
3) 테이블이나 인덱스가 파티션되어 있으나 파티션 프루닝이 불가능한 경우 , 이 경우 전체 파티션을 액세스한다.
4) 테이블,인덱스에 적절한 파티션이 안되어진 경우
5) 통계정보 부족하거나 최신이 아닐 경우, 쿼리 옵티마이져가 잘못된 예측을 할 경우
6) 쿼리 옵티마이져 자체 버그

13.1.4 해결책

SQL구문을 효과적으로 실행하기 위해서는 노리적 읽기 횟수를 줄여야 한다. 다시말해 더 적은 블록을 액세스하는 액세스 패스를 사용해야한다.
새로운 액세스(인덱스 등)을 추가하거나 물리적인 레이아웃(파티셔닝)을 구현해야 한다.
이러한 결정을 쉽게 하기 위해서 선택도(Selectivity)가 있으며 이에 따라 두가지 주요 범주로 SQL를 구문한다.
1) 약한 선택도 (테이블)
2) 강한 선택도 (인덱스)
3) 중간 선택도 (파티셔닝 테이블/인덱스)

한 개의 로우 추출

모든 액세스 구조에서 rowid를 통한 한 번의 논리적 읽기가 수행된다.
힙 테이블에서는 적어도 두 번의 논리적 읽기가 필요한데,한번은 인덱스 읽기 , 다른한번은 테이블 읽기, 로우수의 증가에 따라 인덱스 높이가 증가하고 논리적 읽기 횟수도 증가한다.
IOT의 경우 하나 줄어든 엑세스를 보여준다.
단일 테이블 해시 클러스터에서는 논리적 읽기 횟수가 로우의 수에 독립적일 뿐만 아니라, 항상 한번의 논ㄴ리적 읽기를 일으킨다.

수천개의 로우 추출

인덱스가 없는 비-파티션테이블의 경우 선택도가 약한 경우에만 사용한다.
리스트 파티션 테이블을 읽는데 필요한 논리적 읽기 횟수는 선택도에 비래한다. 어떤 상황에서도 최소한의 논리적 읽기가 수행된다.
단일 테이블 해쉬 클러스터를 읽는 데 필요한 논리적 읽기 횟수는 선택도가 중간이나 높은 겨우에만 이에 비례한다.
인덱스를 통한 테이블 읽기는 논리적 읽기 횟수는 물리적인 분포에 따라 크게 달라진다. 따라서 선택도만으로는 액세스 패스가 데이터를 효율적으로 처리할 수 있는지 확인하기 충분하지 않다.

13.2 약한 선택도를 가진 SQL 구문

풀테이블스캔 또는 풀 파티션 스캔을 사용한다.

13.2.1 풀 테이블 스캔

풀 테이블스캔 - HWM 아래에 있는 모든 블록을 순차적으로 읽는방법
10.2 버전 : 서버프로세스가 버퍼 캐쉬를 읽어서 처리한다.
11.1 버전 : 읽어야하는 블록의 수 , 대상 테이블의 블록 중 이미 버퍼캐쉬에 존재하는 비율 , 테이블 생성 시 BUFFER_POOL 스토리지 파라미터를 KEEP으로 설정한것에 따라 디스크 I/O 작업이 달라진다.
풀테이블 스캔 = 논리적 읽기 = 블록의 수 != 로우의 건수

HWM 재조정

HWM 재설정을 위해선 Object-reorg 작업 필요

13.2.2 풀 파티션 스캔

파티션은 불필요한 데이터를 사전에 제외하기 위한 기능으로 논리적 읽기횟수를 줄이는 핵심기능이다. (파티션프루닝)
2가지 전제조건 1) 테이블 파티션이 되어 있어야 한다. 2) SQL구문에 조건 또는 조인조건에 파티션키가 존재해야한다.

13.2.3 범위 파티셔닝(range)

Partition Range Single
단 하나의 파티션만 액세스
만약 조건에 바인드변수를 사용할 경우 Pstart , Pstop 값이 KEY로 표시됨
파싱당시에 알수 없어 런타임시 파티션 프루닝이 이루어진다.

Partition Range Iterator
여러개의 파티션을 액세스, 연속된 범위에 대해서만 동작한다.
12.1.0.2 존맵기반의 파티션 프루닝에도 사용된다.

Partition Range Inlist
한개 또는 여러개의 IN조건으로 구성된 경우 발생
Pstart , Pstop 은 KEY(I) 표시 , Starts 는 몇개의 파티션을 읽었는지를 의미한다.

Partition Range All
파티션 키에 아무런 제한 조건이 적용되지 않은 경우라면 모든 파티션을 액세스한다.
파티션 키에 대한 부정형 연산이 제한 조건으로 사용될 때 사용된다.

Partition Range Empty
어떠한 파티션에도 처리와 관련된 데이터가 저장되어 있지 않음을 인식했을?

Parition Range OR
파티션키에 대한 분리성 조건 (OR 조건)을 가지는 경우라면

Partition Range Subquery
제한 조건이 조인조건인 경우
조인 조건을 이용한 파티션 프루닝은 항상 합리적인 것은 아니다.
따라서 다음의 3가지 전략을 가지고 접근한다.
1) 파티션 프루닝을 피하는 것이다.
2) NESTED LOOP 조인을 이용해 파티션 프루닝이 발생해야 하는 테이블을 두번? 자식으로 액세스 하는것이다.
3) HASH JOIN 또는 MERGE JOIN을 수행하는것이다. 서브쿼리 프루닝 (첫번째 자식에서 액세스 하는 테이블에 대해서 재귀 쿼리 수행하여 조인 조건에서 사용하는 컬럼을 찾고, 여기서 찾은 컬럼값을 사용하여 두번? 자식의 파티션키를 찾는다.)
파티션 프루닝을 통해 얻을 수 있는 이득보다 재귀 쿼리의 실행으로 인한 오버해드가 적을 경우에만 세번째 기법을 이용한다.

Partition Range Join-filter
11.1부터 조인필터프루닝 , 블룸필터 프루닝을 제공한다.

Partition Range Multi-Column
파티션 키가 여러 컬럼으로 구성된 경우, 제한 조건에 모든 컬럼이 포한되어 있지 않을 ?에도 파티션 프루닝이 가능하다.

Partition Range And
11.2 부터는 옵티마이저가 여러가지 프루닝 기법을 동시에 사용할 수 있다. (AND 프루닝)

13.2.6 설계 고려사항

1) 어떤 컬럼에 어떤 빈도로 제한 조건이 적용될것으로 예상하는지
2) 이들 컬럼에 어떤 종류의 데?가 저장되는지
3) 어떤 SQL 조건( = , IN , BETWEEN , IS NULL)을 사용할 것인지
4) 데이터를 정기적으로 압축하거나 삭제할지 , 그리고 어떤 컬럼을 기준으로 처리할지

범위 파티션 : 본질적으로 순차적인 데이터에 적합
리스트 파티션 : 값의 개수가 제한적인 경우에 적합
해시 파티션 ; 값의 개수가 파티션의 개수보다 훨씬 많은 경우에 적합

13.2.7 풀 인덱스 스캔

첫째 , 인덱스가 쿼리에 사용된 모든 컬럼을 포함하는 경우다.
둘째 , 인덱스와 같은 순서로 추출을 해야 한다.
셋째 , count함수와 관련된 경우이다.

13.3 강한 선택도를 가진 SQL 구문

13.3.1 rowid 액세스

where 절에 직접 rowid를 지정하는 것이다.
수작업 데이터 관리 애플리케이션에 사용되는데 한번은 현재 데이터를 보여주는 용도이고 두번째는 변경 내용을 저장하기 위한 용도이다.

13.3.2 인덱스 액세스

강한 선택도를 가진 SQL문에 가장 많이 사용되는 액세스 패스이다.

클러스터 팩터

얼마나 많은 인접한 인덱스 키가 테이블에서 동일한 데이터 블록을 참조하지 않는지를 나타낸다.

b-트리 인덱스 vs 비트맵 인덱스

- b-트리 인덱스와 비트맵 인덱스에서만 지원하는 핵심 기능

기능	b-트리	비트맵
기본키와 유일키	V
로우 레벨 ?킹	V
여러 인덱스의 효율적인 조합		V
파티션 테이블에서 글로벌 인덱스 및 non-partitioned 인덱스	V

비트맵 인덱스 사용 못하는 2가지 경우

1) primary key 와 unique key는 b-트리 인덱스만 사용할 수 있다.
2) 로우 롹이 안된다. 인덱스 항목(index entry)에 동시에 변경이 불가능하여 확장성에 심각한 제한을 가진다.
또 다른 문제점은 변경 시 b-트리보다 더 많은 리두로그를 생성한다.

비트맵 인덱스 사용해도 좋은 경우

1) 변경이 자주 발생하지 않은 낮은 카디널리티 테이터에 적합하다.
2) where절이 여러개의 조건을 포함하는 경우
3) 낮은 커디널리티에 대한 AND / OR 조건
4) COUNT함수
5) 널 값을 조회하는 검색 조건

주요 핵심

b-트리든 비트맵이든 인덱스에서 rowid를 가져오는 논리적읽기가 테이블을 읽는데 필요한 논리적 읽기보다 훨씬 작다.

index unique scan 및 index range scan을 일으키는 조건

조건	B-트리	비트맵
동등조건	v
is null	v
범위(between,<,>,)	v
in	v
like	v
부정형 비교(!=,<>)와 is not null		v

13. 데이터 액세스 최적화 (by wooriwil) [2018.01.12]