Simple Record

선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

선택도 = 1 / NDV(Number of Distinct Value)

카디널리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV

예) 상품분류 컬럼이 ‘가전’, ‘의류’, ‘식음료’, ‘생활용품’ 네 개의 값이 있을 때,

where 상품분류 = ‘가전’ 해당 조건에 대한 선택 도는 25%(1/4)

만약 전체 레코드가 10만건이면 카디널리티는 2만 5천

통계정보

오브젝트 통계

테이블 통계

테이블 통계 정보 수집 방법

테이블 통계 정보 조회

또는 all_tab_statistics 뷰를 통해 조회 가능

테이블 통계항목

인덱스 통계

인덱스 통계 수집

인덱스 조회

인덱스 통계정보는 all_ind_statistics 뷰에서 조회가 가능하다

컬럼 통계(히스토그램 포함)

컬럼통계는 테이블 통계 수집할 때 함께 수집된다

컬럼 통계 조회

컬럼 히스토그램

컬럼 값별로 데이터 비중 또는 빈도를 미리 계산해 놓은 통계정보

실제 값을 읽어서 계산해둔 값으로 데이터 분포가 많이 변하지 않는 한 거의 정확하다

해스토그램 수집 방법

히스토그램을 수집하려면, 테이블 통계 수집 시 method_opt 파라미터를 지정하면 된다

수집된 컬럼 히스토그램 조회

all_tab_histograms 뷰에서 같은 저보 조회 가능

시스템 통계

애플리케이션 및 하드웨어 성능 특성을 측정한 것으로 아래 항목을 포함 한다

• CPU 속도
• 평균적인 Single Block I/O 속도
• 평균적인 Multiblock I/O 속도
• 평균적인 Multiblock I/O 개수
• I/O 서브시스템의 최대 처리량
• 병렬 Slave의 평균적인 처리량

sys.aux_stats$ 뷰에서 조회할 수 있다

비용 계산 원리

인덱스 키값을 모두 = 조건으로 검색할 때의 비용 계산

인덱스 키값이 모두 = 조건이 아닐 때는 컬럼 통계까지 활용하여 비용 계산

비용(Cost)

I/O 비용 모델 : I/O 비용 모델을 사용할 때 실행게획에 나타나는 Cost는 ‘예상 I/O Call 횟수’를 의미

CPU 비용 모델 : Single Block I/O 를 기준으로 한 상대적 시간을 표현

옵티마이저

• 비용기반(Cost-Based) 옵티마이저(CBO) : 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그 중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저
• 규칙기반(Rule-Based) 옵티마이저(RBO) : 통계정보를 활용하지 않고 단순한 규칙에만 의존하여 대량 데이터를 처리하는데 부적합

옵티마이저 모드

• ALL_ROWS : 전체 처리속도 최적화 : 전체를 읽는 것을 전제로 시스템 리소스를 가장 적게 사용하는 실행계획을 선택
• FIRST_ROWS : 최초 응답속도 최적화 : 앞쪽 일부만 읽다가 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택
• FIRST_ROWS_N : 최초 N건 응답속도 최적화 : 앞쪽 N개 로우만 읽고 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택

옵티마이저 모드 설정

힌트 적용

옵티마이저에 영향을 미치는 요소

• SQL과 연산자 형태 : SQL 형태, 연산자(=, IN, LIKE, BETWEEN, 부등호 등)
• 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터
• 제약 설정 : PK, FK, Check, Not Null
• 통계정보
• 옵티마이저 힌트
• 옵티마이저 관련 파라미터 : 옵티마이저 파라미터 목록 확인

옵티아미저 한계

• 옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 필요한 만큼 충분히 확보하는 것은 불가능한 일, 통계정보를 수집하고 관리하는 데 어마어마한 시간과 비용이 들기 때문
• 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 단점
• 최적화에 허용되는 시간이 매우 짧다

개발자의 역할

- 필요한 최소 블록만 읽도록 쿼리 작성

1) 출력 대상이 아닌 게시물에 대해서도 GET_ICON 함수 적용

2) 회원, 게시판유형, 질문유형 테이블과 조인 → 출력 대상 집합 확정 후 조인 필요

- 최적의 옵티마이징 팩터 제공

최적화는 옵티마이저가 수행하지만, 잘 할 수 있도록 적절한 수단을 제공하는 것은 사용자의 몫

• 전략적인 인덱스 구성
• DBMS가 제공하는 다양한 기능 활용
• 옵티마이저 모드 설정
• 정확하고 안정적인 통계정보

옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고, 통계정보를 잘 수집해 주는 것이 중요

그리고 난 후에 전략적인 인덱스 구성이 필수적으로 뒷받침되어야한다

또한 DBMS가 제공하는 기능을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 다양한 수단을 제공해 주어야한다

- 필요시, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

데이터베이스 튜닝

SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동

• SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화 등
• DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계 등
• 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등

1. 테이블 파티션

파티션 종류 : Range, 해시, 리스트

• 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 → 가용성 향상
• 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산

Range 파티션 : 가장 기초적인 방식으로 주로 날짜 컬럼을 기준으로 파티셔닝한다

파티션 테이블에 값을 입력하면 각 레코드를 파티션 키 값에 따라 분할 저장

검색할 때 : 조건절 비교 값(상수 또는 변수)에 똑같은 해시 함수를 적용함으로써 읽을 파티션 결정

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

라이트 파티션

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

리스트 파티션은 순서와 상관없이 불연속적인 값의 목록에 의해 결정

사용자가 정의한 논리적인 그룹에 따라 분할한다

테이블 파티션

• 비파티션 테이블
• 파티션 테이블

인덱스 파티션

• 로컬 파티션 인덱스 : 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스
• 글로벌 파티션 인덱스
• 비파티션 인덱스

로컬이 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스이며, 테이블 파티션과 독립적인 구성(파티션키, 파티션 기준값 정의)을 갖는다

테이블과 인덱스 파티션 조합

로컬 파티션 인덱스

CREATE INDEX 마지막에 LOCAL옵션을 추가하면 로컬 파티션 인덱스를 생성할 수 있다

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

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

글로벌 파티션 인덱스

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

구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다

CREATE INDEX 마지막에 GLOBAL 키워드를 추가하고 파티션을 정의하면 된다

글로벌 파티션 인덱스는 테이블 파티션 구성(drop, exchange, split 등)하는 순간 Unusable 상태로 바뀌므로 재생성해줘야한다

로컬 인덱스 처럼 보이도록 인덱스를 구성해도 로컬 파티션이 아니므로 오라클이 인덱스를 자동으로 관리해주지 않는다

비파티션 인덱스

파티셔닝하지 않은 인덱스이다

일반 CREATE INDEX문으로 생성하면 비파티션 인덱스가 생성된다

비파티션 인덱스는 테이블 파티션 구성을 변경(drop, exchange, split 등)하는 순간 Unusable상태로 바뀌어서 인덱스를 재생성해줘야 한다

파티션 인덱스는 Prefixed, Nonprefixed로 나눌 수 있다

Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치

Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않음, 파티션 키가 인덱스 컬럼에 아예속하지 않은 때도 포함

로컬과 글로벌의 Prefixed, Nonprefixed 조합

• 로컬 Prefixed 파티션 인덱스
• 로컬 Nonprefixed 파티션 인덱스
• 글로벌 Prefixed 파티션 인덱스
• 비파티션 인덱스

중요한 인덱스 파티션 제약

Unique 인덱스를 파티셔닝하려면, 파티션 키가 모두 인덱스 구성 컬럼이어야 한다

파티션 키가 인덱스 구성 컬럼인 상태

주문 번호로 중복값이 있는지 확인하려면 인덱스 파티션을 모두 탐색해야한다. 주문일자로 나누어진 파티션 테이블 모두에 주문번호가 들어갈 수 있기 때문

다른 트랜잭션이 같은 주문번호로 다른 파티션에 입력하는 현상까지 막으려면 추가적인 Lock 매커니즘이 필요하다

Unique 인덱스를 파티셔닝할 때 파티션 키가 인덱스 컬럼에 포함되어야 DML 성능 보장하기 위한 필수 조건 이다

서비스 중단 없이 파티션 구조를 빠르게 변경하려면, PK를 포함한 모든 인덱스가 로컬 파티션 인덱스이어야한다

파티션을 활용한 대량 UPDATE 튜닝

입력/수정/삭제하는 데이터 비중이 5%를 넘는다면, 인덱스를 그대로 둔 상태에서 작업하기 보다는 인덱스 없이 작업한 후 재생성하는 게 더 빠르다

다만 대량 데이터의 경우 인덱스를 재생성하는데 걸리는 시간이 만만치 않으므로 이에 대해 고민하고 수행해야한다

파티션 Exchange를 이용한 대량 데이터 변경

테이블이 파티셔닝돼 있고 인덱스가 로컬 파티션인 경우, 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방법이 있다

1. 임시 테이블을 생성한다. 가능하면 nologging 모드로 생성

2. 거래 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정

3. 임시 테이블에 원본 테이블과 같은 구조로 인덱스 생성. 가능하면 nologging 모드로 생성

4. 2014년 12월 파티션과 임시 테이블을 Exchange

5. 임시 테이블을 Drop한다

6. 파티션을 logging 모드로 전환(nologging으로 작업한 경우)

파티션을 활용한 대량 DELETE 튜닝

인덱스를 실시간으로 관리하려면 어마어마한 시간이 소요

초대용량 테이블 인덱스를 모두 Drop 했다가 다시 생성하기도 쉽지 않다

Delete 가 느린 이유

1. 테이블 레코드 삭제
2. 테이블 레코드 삭제에 대한 Undo Logging
3. 테이블 레코드 삭제에 대한 Redo Logging
4. 인덱스 레코드 삭제
5. 인덱스 레코드 삭제에 대한 Undo Logging
6. 인덱스 레코드 삭제에 대한 Redo Logging
7. Undo에 대한 Redo Logging

파티션 Drop을 이용한 대량 데이터 삭제

테이블이 삭제 조건절 컬럼 기준으로 파티셔닝돼 있고 인덱스도 로컬 파티션이라면 삭제가 간단하다

파티션 Truncate를 이용한 대량 데이터 삭제

거래일자 조건에 해당하는 데이터를 일괄 삭제하지 않고 아래와 같이 또 다른 삭제 조건이 있는 경우

만약 조건을 만족하는 데이터가 소수이면, Delete 문을 그대로 수행하면 된다

조건을 만족하는 데이터가 다수라고 하면, 남길 데이터만 백업했다가 재입력하는 방식이 빠른다

1. 임시 테이블을 생성하고 남길 데이터만 복제

2. 삭제 대상 테이블 파티션을 Truncate

3. 임시 테이블에 복제해 둔 데이터를 원본 테이블에 입력

4. 임시 테이블 Drop

서비스 중단 없이 파티션 Drop 또는 Truncate를 위해서는 아래 조건을 만족해야한다

1. 파티션 키와 커팅 기준 컬럼이 일치해야 함 ex) 파티션 키와 커팅 기준 컬럼이 모두 ‘신청일자’
2. 파티션 단위와 커팅 주기가 일치해야 함 ex) 월 단위 파티션을 월 주기로 커팅
3. 모든 인덱스가 로컬 파티션 인덱스이어야 함 ex) 파티션 키는 ‘신청일자’, PK는 ‘신청일자 + 신청순번’ PK 인덱스는 삭제 기준(파티션 키) 컬럼이 인덱스 구성 컬럼이어야 로컬 파티셔닝 가능

파티션을 활용한 대량 INSERT 튜닝

비파티션 테이블일 때

비파티션 테이블에 손익분기점을 넘는 대량 데이터를 INSERT 하려면, 인덱스를 Unusable 시켰다가 재생성하는 방식이 더 빠를 수 있다

1. 가능하다면 테이블을 nologging 모드로 전환

2. 인덱스를 Unusable 상태로 전황

3. 가능하다면 Direct Path Insert 방식으로 대량 데이터를 입력

4. 가능하다면 nologging 모드로 인덱스를 재생성

5. nologging 모드로 작업했다면 logging 모드로 전황

파티션 테이블일 때

초대용량 테이블 인덱스 재생성이 부담되기 때문에 인덱스를 그대로 둔 상태로 INSERT 한다

테이블이 파티셔닝돼 있고, 인덱스도 로컬 파티션이라면 파티션 단위로 인덱스를 재생성할 수 있어 좋다

1. 가능하다면 테이블의 파티션을 nologging 모드로 전환

2. 작업 대상 테이블 파티션과 매칭되는 인덱스 파티션을 Unusable로 전환

3. 가능하다면 Direct Path Insert 방식으로 대량 데이터 입력

4. 가능하다면 nologging 모드로 인덱스 파티션을 재생성

5. nologging 모드로 작업했다면, 작업 파티션을 logging 모드로 전환

Lock과 트랜잭션 동시성 제어

Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 매커니즘이다

오라클 Lock

오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용

DML 로우 Lock

두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지

하나의 로우를 변경하려면 로우 Lock을 먼저 실행해야 한다

DML 로우 Lock에는 배타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행중인 로우를 다른 트랜잭션이 UPDATE나 DELETE 할 수 없다

INSERT 에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생(Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력할 때 발생)

MVCC 모델을 사용하는 오라클은 SELECT 문에 로우 Lock을 사용하지 않는다

따라서 DML 과 SELECT는 서로 진행을 방해하지 않고, SELECT 끼리도 서로 방해하지 않는다

DML 테이블 Lock

현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해 테이블 Lock이 DML Lock보다 먼저 설정된다

RS: row share(또는 SS : sub share)

RX: row exclusive (또는 SX: sub exclusive)

S: share

SRX: share row exclusive (또는 SSX: share/sub exclusive)

X: exclusive

INSERT, UPDATE, DELETE, MERGE 문을 위해 로우 Lock을 설정하려면 해당 테이블에 RX(=SX) 모드 테이블 Lock을 먼저 설정해야 한다

오라클에서 테이블 Lock은 해당 테이블에서 현재 어떤 작업을 수행중인지 알리는 일종의 Flag이다

대상 리소스가 사용중일 때

• Lock이 해제될 때 까지 기다린다.(select * from t for update)
• 일정 시간만 기다리다 포기한다.(select * from t for update wait 3)
• 기다리지 않고 작업을 포기한다.(select * from t for update nowait)

DML을 수행할 때 묵시적으로 테이블 Lock을 설정

설정을 통해서 NOWAIT 옵션 지정 가능

Lock을 푸는 열쇠는 커밋이다

블록킹 : 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태 ⇒ 커밋으로 해소 가능

교착상태 : 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황

WAIT : LGWR가 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 때까지 기다린다.(동기식 커밋)

NOWAIT : LGWR의 완료 메시지를 기다리지 않고 바로 다음 트랜잭션을 진행(비동기식 커밋)

IMMEDIATE : 커밋 명령을 받을 때마다 LGWR가 로그 버퍼를 파일에 기록

BATCH : 세션 내부에 트랜잭션 데이터를 일정량 버퍼링했다가 일괄 처리

트랜잭션 동시성 제어

비관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정할 것으로 가정, Lock은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할 수 없다

적립포인트를 계산하는 동안 고객의 실적정보를 변경하면 문제가 될 수 있다

비관적 동시성 제어로 데이터 잘못 갱신되는 문제 방지

해당 방식을 통해 Lock을 무한정 기다리지 않을 수 있다

낙관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정, 데이터를 읽을 때 Lock을 설정하지 않는다

그렇기 때문에 데이터 갱신 여부를 판단하는 로직이 필요하다

낙관적 동시성 제어에서도 UPDATE 전 for update를 수행함으로 써 Lock에 대한 예외처리를 하면, 다른 트랜잭션이 설정한 Lock을 기다리지 않게 구현 가능

* for update 사용을 두려워하지 말자

두 테이블에 대해 로우 Lock 발생

주문수량이 있는 테이블에 대해서만 로우 Lock 발생

채번 방식에 따른 INSERT 성능 비교

채번 테이블 : 각 테이블 식별자의 단일 컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식. 채번 레코드를 읽어서 1을 더한 값으로 변경하고, 그 값을 새로운 레코드를 입력하는 데 사용

장점:

- 범용성이 좋다

- INSERT 과정에 중복 레코드 발생에 대비한 예외(Exception) 처리에 크게 신경쓰지 앟낭도 되므로 채번 함수만 잘 정의하면 편리하게 사용할 수 있다

- INSERT 과정에 결변을 방지할 수 있다

- PK가 복합컬럼일 때도 사용할 수 있다

단점:

- 다른 채번방식에 비해 성능이 않 좋다(채번 레코드를 변경하기 위한 로우 Lock 경합 때문)

PL/SQL의 자율 트랜잭션 기능을 이용하면 메인 트랜잭션에 영향을 주지 않고 서브 트랜잭션에서 일부 자원만 Lock을 해제할 수 있다

자율 트랜잭션으로 선언하면, 그 내부에서 커밋을 수행해도 메인 트랜잭션은 커밋하지 않은 상태로 남는다

시퀀스 오브젝트

장점:

- 성능이 빠르다

- INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 크게 신경 쓰지 않아도 된다

시퀀스는 오라클 내부에서 관리하는 채번 테이블이다

따라서 채번 과정에서 Lock이 발생한다

캐시에 의해 빠른 성능을 제공

시퀀스 Lock

- 로우 캐시 Lock : 로우 캐시는 공유 캐시의 구성요소이므로 액세스 시 직렬화 필요

- 시퀀스 캐시 Lock : SQ Lock 이라고 부르며, 시퀀스 캐시는 공유 캐시에 위치하기 때문에 Lock 필요

- SV Lock : RAC 환경에서 시퀀스 캐시에 대한 직렬화(ORDER 옵션 사용), 단, 성능상 손실 순환 옵션을 사용하면 최대값에 도달하면 1부터 다시 시작하도록 할 수 있다

MAX + 1

최대 값을 구하고 거기에 1을 더해서 사용

장점:

- 시퀀스 또는 채번 테이블을 관리하는 부담이 없다

- 동시 트랜잭션에 의한 충돌이 많지 않으면, 성능이 매우 빠르다

- PK가 복합 컬럼인 경우, 즉 구분 속성별 순천을 채번할 때도 사용할 수 있다

단점:

- 레코드 중복에 대비한 세밀한 예외처리 필요

- 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다(레코드 중복에 의한 로우 Lock 때문)

채번 방식에 발행하는 Lock 경합 요소

Lock 경합 요소를 고려한 채번 방식 선택 기준

1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면 아무거나 사용해도 된다. 다만, 채번 테이블이나 시퀀스 오브젝트 관리 부담을 고려하면 MAX + 1 을 선택하는게 좋다

2. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일컬럼 일련번호라면, 시퀀스 방식

3. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 많으면, 중복에 의한 로우 Lock 경합 및 재실행 가능성이 낮다 시퀀스 보다 MAX + 1 방식이 구조적으로 좋다

4. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 적으면, 순환 옵션을 가진 시퀀스 오브젝트 활용 고려

시퀀스 보다 좋은 솔루션

한 개 이상의 구분 속성과 함께 뒤쪽에 순번 대시 입력일시 를 두는 방식으로 PK 구조 설계

⇒ 채번의 과정에서 생기는 Lock 이슈를 거의 해소할 수 있다

중복 가능성이 매우 희박하지만 예외처리는 필요하다

인덱스 블록 경합

INSERT 성능이 너무 빠른 경우 문제, Right Growing 인덱스에서 가장 흔히 볼 수 있다

인덱스는 키순으로 정렬된 상태를 유지하며 값이 입력된다

따라서 순차적으로 값이 증가하는 단일컬럼 인덱스를 Right Growing 인덱스라고 부르는데, 이 인덱스는 입력하는 값이 달라도 같은 블록을 갱신하려는 프로세스 간 버퍼 Lock 경합이 발생할 수 있다

구분 속성의 값 종류 개수가 적을 수록 경합도 심하다

일반적인 해소 방법은 인덱스를 해시 파티셔닝 하는 것

해시 파티셔닝하면 값이 순차적으로 증가하더라도 해시 함수에 의해 리턴 값이 결정 되므로 경합을 줄일 수 있다

인덱스를 리버스 키 인덱스로 전환하는 방법도 고려될 수 있다

글로벌 시퀀스 + 세션 시퀀스

세션시퀀스는 INSERT를 수행할 때마다 호출된다

글로번 시퀀스와 세션 시퀀스를 조합해서 사용하면 프로세스가 서로 다른 리프 블록에 값을 입력하므로 인덱스 경합이 발생하지 않는다

Scaleable 시퀀스(18C ^)

시퀀스를 생성할 때 SCALE 또는 SCALE EXTEND 옵션을 지정하면 사용할 수 있다

Scaleable 시퀀스에서 nextval을 호출하면, 인스턴스 번호, 세션 ID, 시퀀스 번호를 조합한 번호를 반환한다

EXTEND 생략 시 VAL3의 리딩 제로 없는 숫자를 반환

EXTEND : 0001, 0002, 0003

no EXTEND : 1, 2, 3

DML 성능에 영향을 미치는 요소

• 인덱스
• 무결성 제약
• 조건절
• 서브쿼리
• Redo 로깅
• Undo 로깅
• Lock
• 커밋

테이블에 레코드를 입력 시 인덱스에도 입력 필요.

테이블의 경우 Freelist(테이블별 여유공간 블록 목록) 통해 블록 할당

인덱스의 경우 정렬된 자료구조로 수직적 탐색을 통해 입력할 블록을 찾아야함

DELETE 도 마찬가지

UPDATE의 경우 해당되는 인덱스만 찾아서 변경해주면 된다. 다만, 테이블에서 한건을 변경할 때 마다 인덱스에는 두개의 오퍼레이션이 발생(삭제 후 삽입)

DML에서 인덱스 개수가 미치는 영향이 크다.

핵심 트랜잭션 테이블에서 인덱스의 개수를 하나라도 줄이면 TPS(Transcation Per Second)는 그만큼 향상된다.

인덱스가 존재할 때와 그러지 않을 때의 insert 시간 비교

왼쪽 인덱스가 없을때, 가운데 인덱스가 2개 존재할 때, 오른쪽 일반 인덱스와 PK 제약을 모두 제거한 상태

데이터베이스에 논리적으로 의미있는 자료만 저장되게 하는 데이터 무결성 규칙

• 개체 무결성
• 참조 무결성
• 도메인 무결성
• 사용자 정의 무결성

PK, FK 제약은 Check, Not Null 제약보다 성능에 더 큰 영향을 미친다

Check, Not Null은 정의한 제약 조건을 준수하지는만 확인하면 되지만, PK, FK 제약은 실제 데이터를 조회해 봐야 하기 때문

Update, Delete 문의 실행 계획이 Select 문의 실행 계획과 다르지 않으므로 인덱스 튜닝 원리를 그대로 적용할 수 있다. 조인 튜닝 또한 동일하게 적용 가능하다

인덱스도 select와 같이 적용된다

Redo 로깅과 DML 성능

데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다

Redo 로그는 트랜잭션 데이터가 어떤 이유에서건 유실되었을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는데 사용된다

DML이 수행될 때마다 Redo 로그를 생성하므로 Redo 로깅은 DML 성능에 영향을 미친다

Redo 로그 목적

• Database Recovery : Media Fail 발생 시 데이터베이스를 복구하기 위해 사용(온라인 Redo 로그를 백업해 둔 Archived Redo 로그 이용) → Media Recovery
• Cache Recovery (Instnace Recovery 시 roll forward 단계) : 캐시에 저장된 변경사항이 디스크 상의 데이터 블록에 기록되지 않은 상태로 인스턴스가 비정상적으로 종료되면, 그때까지의 작업내용을 모두 잃게 된다.(버퍼캐시는 휘발성) 트랜잭션 데이터 유실에 대비하기 위해 Redo 로그를 남긴다
• Fast Commit : 변경된 메모리 버퍼 블록을 디스크 상의 데이터 블록에 반영하는 작업은 랜덤 액세스 방식으로 이루어지므로 매우 느리지만 로그는 Append 방식으로 기록하므로 상대적으로 빠르다. 따라서 변경사항을 Append 방식으로 빠르게 로그 파일에 기록하고 변경된 메모리 버퍼 블록과 데이터 파일 블록 간 동기화는 DBWR을 이용해 나중에 배치 방식으로 일괄 수행한다. 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 커밋을 완료하는 것을 Fast Commit 이라고 한다

Undo 로깅과 DML 성능

Redo는 트랜잭션을 재현함으로써 과거를 현재 상태로 되돌리는 데 사용 Undo는 트랜잭션을 롤백함으로써 현재를 과거로 돌리는데 사용

Undo 데이터 사용 조건

• Transaction Rollback : 트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백할 때 Undo 데이터 사용
• Transaction Recovery (Instance Recovery 시 rollback 단계) : Instance Crash 발생 후 Redo를 이용해 roll forward 단계가 완료되면 최종 커밋되지 않은 변경사항까지 모두 복구되므로 Undo 데이터를 사용하여 아직 커밋되지 않았던 트랜잭션들을 모두 롤백해야 한다
• Read Consistency : 읽기 일관성에 사용

MVCC(Multi Version Concurrency Control) 모델

current 모드: 디스크에서 캐시로 적재된 원본 블록을 현재 상태 그대로 읽는 방식

consistent 모드: 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본(CR Copy) 블록을 만들고 거기에 Undo 데이터를 적용함으로써 쿼리가 시작된 시점으로 되돌려 읽는 방식

SCN(System Commit Number) 마지막 커밋이 발생한 시점정보

블록 SCN: 각 블록이 마지막으로 변경된 시점을 관리하기 위해 블록 헤더에 기록됨

쿼리 SCN: 모든 쿼리는 Global 변수인 SCN 값을 먼저 확인하고 읽기 작업 시작, 이를 쿼리 SCN이라고 한다

데이더를 읽다가 블록 SCN이 쿼리 SCN보다 더 큰 블록을 만나면 복사본 블록을 만들고 Undo 데이터를 적용함으로써 쿼리가 시작된 시점으로 되돌려서 읽는다

Undo 데이터가 다른 트랜잭션에 의해 재사용됨으로써 쿼리 시작 시점으로 되돌리는 작업에 실패할 때 Snapshot too old 에러 발생

Lock과 DML 성능

Lock은 DML 성능에 매우 크고 직접적인 영향을 미친다. Lock을 필요 이상으로 자주, 길게 사용하거나 레벨이 높을수록 DML 성능은 느려진다

트랜잭션 격리 수준

• Read Uncommited
• Read Commited (기본)
• Reapeatable Read
• Serializable

트랜잭션의 저장 과정 (Write Ahead Logging)

1. DML 문을 실행하면 Redo 로그버퍼에 변경사항 기록
2. 버퍼블록에서 데이터를 변경(추가,수정,삭제), 버퍼캐시에서 블록을 찾지 못하면 데이터파일에서 읽는 작업부터 수행
3. 커밋
4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장
5. DBWR 프로세스가 변경된 버퍼블록들을 데이터파일에 일괄 저장

Log Force at Commit : 서버 프로세스가 커밋을 발행했다고 신보를 보낼때 LGWR 프로세스가 로그파일에 저장하므로 적어도 커밋시점에는 Redo 로그버퍼 내용이 로그파일에 기록됨

데이터베이스 Call

Parse Call : SQL 파싱과 최적화를 수행하는 단계. SQL과 실행계획을 라이브러리 캐시에서 찾으면, 최적화 단계 생각 가능

Execute Call: SQL을 실행하는 단계. DML은 이 단계에서 모든 과정이 종료. SELET 문은 Fetch 단계를 거친다

Fetch Call: 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정. SELECT문에서만 나타나며 전송할 데이터가 많으면 Fetch Call 여러번 발생

User Call, Recursive Call

Call 이 발생하는 위치에 따라 User Call과 Recursive Call로 나뉜다

User Call: 네트워크를 통해 DBMS 외부로부터 인입되는 Call 3-Tier 아키텍처에서 User Call은 WAS(또는 AP서버) 서버에서 발생하는 Call

Recursive Call: DBMS 내부에서 발생하는 Call

커밋과 성능

: 커밋을 자주하는 경우 트랜잭션 원자성에 문제가 생긴다

: 매우 오래 걸리는 트랜잭션을 한 번도 커미하지 않고 진행하면 Undo 공간 부족으로 인해 시스템에 부작용을 초래할 수 있다

: 적당한 주기로 커밋을 하는것이 좋다

커밋을 자주하면 수행 시간이 오래걸리게 되는데, 이는 User Call에서는 더 많은 시간이 걸리게 된다

One SQL

• Insert Into Select
• 수정가능 조인 뷰
• Merge 문

실무에서 One SQL로 구현이 쉽지 않다

Array Processing 기능을 활용하면 One SQL을 쓰지 않고도 Call 부하를 획기적으로 줄일 수 있다

PL/SQL

Java

인덱스 및 제약 해제를 통한 대량 DML 튜닝

동시 트랜잭션 없이 대량 데이터를 적재하는 배치 프로그램에서는 인덱스 및 제약 조건을 해제함으로써 큰 성능 개선 효과를 얻을 수 있다

PK 제약 및 인덱스 생성 (총 인덱스 2개)

PK제약과 인덱스 해제

1. PK제약에 Unique 인덱스를 사용한 경우

PK 제약 해제 및 인덱스 삭제

인덱스 비활성화

PK 제약 설정 및 인덱스 활성화

2.PK제약에 Non-Unique 인덱스 사용

PK제약을 Unusable한 상태에서는 데이터를 입력할 수 없다

PK 인덱스를 Drop 하지 않고 Unusable 상태에서 데이터를 입력하려면 PK 제약에 Non-Unique 인덱스를 사용하면 된다

Non-Unique 인덱스 생성 및 PK 제약으로 지정

PK 제약 해제 및 인덱스 비활성화

PK 제약 설정 및 인덱스 활성화

수정가능 조인 뷰

전통적인 UPDATE

전통적인 UPDATE문은 다른 테이블과 조인이 필요할 때 비율을 완전히 해소할 수 없다

수정가능 조인 뷰

수정가능 조인 뷰를 활용하면 참조 테이블과 두 번 조인하는 비효율을 없앨 수 있다

수정가능 조인 뷰에서는 1쪽 집합에 PK 제약을 설정하거나 Unique 인덱스를 생성해야 수정가능 조인뷰를 통한 입력/수정/삭제가 가능하다

키 보존 테이블

: 조인된 결과집합을 통해서도 중복 값 없이 Unique 하게 식별이 가능한 테이블 Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과집합을 통한 식별이 가능

ORA-01779 오류 회피 : Group By 한 집합과 조인한 테이블은 키가 보존되므로 해당 문제를 해결할 수 있다

Merge 문

DW Merge 예시

Merge문은 Source 테이블 기준으로 Target 테이블과 Left Outer 방식으로 조인해서 조인 성공하면 Update, 실패하면 Insert 한다

Update 와 Insert를 선택적으로 처리 가능

where 절을 통한 추가 조건절 기술 가능

이미 저장된 데이터를 조건에 따라 지우는 기능 제공

Delete의 경우

Merge문을 수행한 결과가 Null 이면 삭제하지 않는다

조인에 성공한 데이터만 삭제할 수 있다

Direct Path I/O

DW, 배치 프로그램에서는 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O 메커니즘이 오히려 성능을 떨어뜨릴 수 있다

그래서 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다

동작하는 경우

• 병렬 쿼리로 Full Scan을 수행할 때
• 병렬 DML을 수행할 때
• Direct Path Insert 를 수행할 때
• Temp 세그먼트 블록들을 읽고 쓸 때
• direct 옵션을 지정하고 export를 수행할 때
• nocache 옵션을 지정한 LOB 컬럼을 읽을 때

Direct Path Insert

INSERT가 느린 이유

1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다
2. Freelist에서 할당 받은 블록을 버퍼캐시에서 찾는다
3. 버퍼캐시에 없으면, 데이터 파일에서 읽어 버퍼캐시에 적재한다
4. INSERT 내용을 Undo 세그먼트에 기록한다
5. INSERT 내용을 Redo 로그에 기록한다

Direct Path Insert 적용 방법

• INSERT … SELECT 문에 append 힌트 적용
• parallel 힌트를 이용해 병렬 모드로 INSERT
• direct 옵션을 지정하고 SQL Loader(sqlldr)로 데이터 적재
• CTAS 문 수행

Direct Path Insert 방식이 빠른 이유

1. Freelist를 참조하지 않고 HWM(High-Water Mark) 바깥 영역에 데이터를 순차적으로 입력한다
2. 블록을 버퍼캐시에서 탐색하지 않는다
3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다
4. Undo 로깅을 하지 않는다
5. Redo 로깅을 안하게 할 수 있다(nologging 모드 상태일 때)

Direct Path Insert 주의점

• exclusive 모드 TM Lock 발생
• Freelist를 조회하지 않고 HWM 바깥 영역을 입력하므로 테이블에 여유공간이 있어도 재사용하지 않는다

병렬 DML

INSERT는 append 힌트로 Direct Path Write 방식을 유도할 수 있지만, UPDATE, DELETE 는 불가능하다

병렬 DML로 Direct Path Write 방식 사용가능

병렬 DML 활성화 방법

아래 힌트 적용 시 대상 레코드 찾는 작업(insert는 select 쿼리, update/delete 는 조건절 검색)과 데이터 추가/변경/삭제 병렬로 진행

병렬 DML을 활성화 하지 않고 수행하면 대상 레코드를 찾는 작업만 병렬로 진행되어 추가/변경/삭제 는 QC(Query Coordinator)가 혼자 담당하므로 병목 발생

병렬 INSERT는 append 힌트를 적용하지 않아도 Direct Path Insert 방식을 사용한다

하지만 병렬 DML이 작동하지 않을 경우를 대비해 apeend 힌트를 같이 사용하는 것이 좋다

병렬 DML이 작동하지 않더라도 QC 가 Direct Path Insert를 사용하면 어느정도 만족할 만한 성능을 낼 수 있기 때문

enable_parallel_dml 힌트를 통해 활성화 가능(12c이후)

* 병렬 DML도 Direct Path Write 방식을 사용하므로 데이터 입력/수정/삭제 때 Exclusive 모드 TM Lock 발생

병렬 DML 동작 확인 방법

UPDATE 가 PX COORDINATOR 아래 쪽애 나타나면 UPDATE를 각 병렬 프로세스가 처리

UPDATE 가 PX COORDINATOR 위쪽에 나타나면 UPDATE를 QC가 처리

소트 수행 과정

기본적으로 PGA에 할당한 Sort Area에서 이루어진다. 다만 Sort Area가 다 차면 디스크 Temp 테이블스페이스를 활용한다

1. 메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료(Internal Sort)
2. 디스크 소트 : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우(External Sort)

소트 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, 일차적으로 Sort Area에사 정렬 시도

양이 많으면 템프페이블 스페이스에 임시 세그먼트 만들어 저장

Sort Area가 찰 때 마다 Temp영역에 저장해 두는 단계 집할을 Sort Run이라고 부른다

소트 연산은 메모리 집약적일 뿐 아니라 CPU 집약적

소트 오퍼레이션

1. Sort Aggregate : 실제 데이터 정렬을 하지는 않지만 Sort Area 사용

2. Sort Order By

3. Sort Group By

Hash Group By

해싱 알고리즘을 사용해 버킷을 찾아 그룹별로 집계 항목 갱신

4. Sort Unique

5. Sort Join 소트 머지 조인 시

6. Window Sort

Union, Union All

Union은 중복 제거를 하므로 Union All보다 수행시간이 길다 다만 Union 하는 두 집합이 서로 상호 배타적이라고하면 Union 대신 Union All를 사용하는게 더 낫다

Exists

중복 레코드를 제거할 목적으로 Distinct를 사용하는데 Exists를 사용하게 되면 서브쿼리의 데이터 존재여부만 확인하게 되므로 부분 범위 처리가 가능해진다

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

인덱스는 항상 키 컬럼 순으로 청렬된 상태를 유지한다

이를 활용하면 Order By, Group By 절이 있어도 소트 연산을 생략 할 수 있다

여기에 Top N 쿼리 특성을 결합하면 온라인 트랜잭션 처리 시스템에서 매우 빠른 응답 속도를 낼 수 있다

인덱스 선두 컬럼이 “종목코드 + 거래일시” 순으로 구성되지 않은 경우 소트 연산 생략 불가

인덱스 선두 컬럼이 “종목코드 + 거래일시” 순으로 구성 된 경우

실행 계획에 Sort Order By 항목이 없어졌다

최소/최대 값 구하기

인덱스는 정렬돼 있으므로 이를 이용하면 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있다

인덱스 맨 왼쪽으로 내려가서 첫 번째 읽는 값이 최소값, 맨 오른쪽으로 내려가 읽는 첫 번째 값이 최대값이다

1. 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함돼 있고, 인덱스 선두 컬럼이 모두 = 조건인 경우

인덱스가 DEPTNO + MGR + SAL 구성된 경우 이다

인덱스가 DEPTNO + SAL + MGR 인경우 DEPTNO 가 액세스 조건 MGR이 필터 조건으로 조건을 만족하는 레코드 한개를 찾았을 때 멈추게 된다

인덱스가 SAL + DEPTNO + MGR 인경우 DEPTNO, MGR이 필터 조건으로 인덱스 가장 우측에서 스캔 시작하여 조건을 만족하는 레코드를 하나 찾으면 멈춘다

인덱스가 DEPTNO + SAL인 경우 인덱스를 통해 MGR이 필터링 되지 않으므로 테이블에서 필터링이 되어야하므로 DEPTNO = 30 인 모든 레코드를 읽어 필터링 후 MAX값을 구한다. First Row StopKey 알고리즘이 동작하지 않는다

Top N 쿼리 이용 최소/최대 값 구하기

Top N 쿼리에 동작하는 Top N Stopkey 알고리즘은 모든 컬럼이 인덱스에 포함돼 있지 않아도 잘 동작한다

DEPTNO = 30을 만족하는 가장 오른쪽 레코드 부터 역순으로 스캔하면서 테이블 엑세스 하다가 MGR 조건이 일치하는 레코드를 찾았을 때 멈춘다

이력조회

복합한 이력 조회

해당 방법은 인덱스 컬럼을 가공했으므로 First Row Stopkey 알고리즘이 동작하지 않는다

해당 방법은 상태변경이력을 여러번 조회하는 비효율은 맀으나 First Row Stopkey 알고리즘이 잘 작동한다. 다만 컬럼이 많이진다면 SQL 문은 위 처럼 복잡해진다

Index_des 힌트 활용

인덱스를 역순으로 읽도록 index_desc 힌트를 사용하고, 첫번째 레코드에서 바로 멈추도록 rownum≤1조건절 사용

해당 쿼리의 경우 predicate pushing 쿼리 변환이 동작하여 인라인 뷰 안쪽으로 장비번호 조건절이 들어가면서 동작한다. Top N Stopkey 알고리즘이 작동하지 않아 성능이 느려질 수 는 있지만 쿼리 결과 집합은 보장된다

인덱스 활용 패턴은 랜덤 I/O 발생량 만큼 성능도 비례해서 느려지므로 대량 데이터 조회 시에는 윈도우 함수를 활용하는 것이 full scan과 해시조인을 이용하기 때문에 과거 이력까지 읽지만 인덱스를 이용하는 방식보다 빠르다

이처럼 KEEP 절을 활용할 수 도 있다

Sort Group By 생략

region이 선두 컬럼인 인덱스를 이용하면 Sort Group By 연산을 생략할 수 있다

인덱스가 정렬되어있으므로 순서대로 읽어 처리하다 다른 값을 만나게 되면 운반단위에 집계값을 저장하는 식으로 처리한다

인덱스를 이용해 Nosort 방식으로 Group By를 처리하면 부분 범위 처리가 가능해진다

소트 데이터 줄이기

1번의 경우 lpad에 의해 가동된 결과 집합을 sort area에 저장하므로 2번의 가공하지 않는 상태로 정렬을 하는 sql이 sort area를 더 적게 사용한다

1번의 경우 예수금원장의 모든 컬럼을 sort area에 저장하므로 계좌번호, 총예수금만 저장하는 2번 sql이 더 적응 sort area를 사용한다

Top N 쿼리를 사용하게 되면 N개의 sort area 공간을 사용하게 되므로 소트 부하가 적다

sort order by 오퍼레이션이 나타났지만 stopkey로 표시되면 top n 소트 알고리즘이 동작하였다

이 알고리즘이 동작하면서 sort area 사용량을 최소화 해준다

그 결과 sorts (disk) 항목을 사용하지 않았다

Top N 쿼리 아닐 시 소트 부하

해당 쿼리를 사용하면서 쿼리는 조금 간결해졌으나 실행계획에 stopkey가 사라지고 pr, pw가 증가한 것을 확인할 수 있다

이를 통해 Top N 소트 알고리즘이 동작하지 않았고, 정렬을 수행하는데 disk를 사용하였다

정렬을 위해 temp 테이블스페이스를 이용했다

분석함수에서 Top N 소트

윈도우 함수 중 rank나 row_number함수는 max 함수보다 소트 부하가 적다

Top N 소트 알고리즘이 동작하기 때문이다

max를 이용하는 경우 pr 13456, pw 8960 이 발생하였다

rank함수를 사용하는 경우 pr 40, pw 40으로 physical read, write가 발생하긴했으나 max 함수 사용시 보다 줄었고, 수행시간도 8초 가량 차이가 발생했다

NL(Nested Loop) 조인

NL 조인은 중첩 루프문과 같은 수행 구조를 사용

NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 사용

Outer 쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다(Table Full Scan 하더라도 한 번에 그치기 때문)

단, Inner 테이블은 인덱스를 사용해야 한다

Outer 에서 읽은 건수 만큼 Table Full Scan을 반복하기 때문이다

위 NL 조인 실행 계획을 보면 윗쪽 테이블 기준 아래쪽 테이블과 NL 조인 한다고 생각하면 된다

use_nl(alias) : alias를 Inner 테이블로 해서 NL 조인 하라는 hint

이 처럼 여러 테이블의 alias를 사용하면, 네개의 테이블을 NL 조인하는데 순서는 옵티마이저가 알아서 정하도록 하는 것이다

Leading 등을 활용해서 순서를 제어하는 것이 힌트 사용시 도움이 될 것 같다

위 쿼리의 경우 비교 순서는 2 ,3 1, 4 이다

ordered 로 사원, 고객 순서로 수행되고 index(e)로 인덱스를 이용한다

현재 조건문에 존재하는 컬럼이 포함된 인덱스는 사원_x1이므로 입사일자 조건이 먼저 수행되고 이어서 부서코드로 filter 하게된다

그리고 조인 컬럼을 포함하는 인덱스가 고객_x1이므로 해당 인덱스를 사용하여 NL 조인한 후 최종주문금액 조건으로 filter 하게 된다

NL 조인 튜닝 포인트

• 조인 액세스 횟수가 많을 수록 성능은 느려진다. 따라서, 조인 액세스 횟수가 Outer 테이블을 읽고 필터링한 결과 건수에 의해 결정

온라인 트랜잭션 처리(OLTP) 시스템에서 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서

NL 조인 특징

• 랜덤 액세스 위주
• 한 레코드씩 순차적으로 진행
• 인덱스 구성 전략이 특히 중요

이런 특징 때문에 NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리 시스템에 적합

NL 조인 확장 메커니즘

1. 전통적인 실행계획

2. 테이블 Prefetch 실행계획

nlj_prefetch, no_nlj_prefetch 힌트로 사용, 비사용 지정 가능

3. 배치 I/O 실행계획

: 배치 I/O 실행계획이 나타날 때는 결과 집합의 정렬 순서도 다를 수 있으므로 주의 필요

소트 머지 조인

조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효율적이지 않을 때 사용

SGA

공유 메모리 태시된 데이터를 여러 프로세스가 공유해서 사용. 직렬화 매커니즘 존재(Lock, 래치)

PGA

프로세스에 할당된 메모리 영역으로 프로세스만 독립적으로 사용하는 공간으로 래치 메커니즘 불필요

- SGA 버퍼캐시에서 읽을 때 보다 속도가 더 빠르다

- PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블 스페이스를 이용한다

소트머지 기본 메커니즘

1. 소트 단계
   양쪽 집합을 조인 컬럼 기준으로 정렬
   소트 시 PGA의 Sort Area 에 저장, 공간 부족 시 Temp 테이블스페이스에 저장
2. 머지 단계
   정렬한 양쪽 집합을 서로 머지 NL 조인과 방식이 다르지 않음
   다만 소트 단계에 의해 정렬되어 있으므로 중간에 멈출 수 있음

use_merge(alias) 힌트로 소트머지 조인 유도 가능

ordered에 의해 순서대로 조인하고 고객 테이블 조인 시 소트 머지 조인 사용하도록 하는 힌트

소트 머지가 사용되는 상황

• 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)

소트 머지 조인 특징

• 조인 전 양쪽 집합 정렬
• NL 조인과 같은 방식으로 진행
• 실시간으로 인덱스 생성하는 것과 같음 (정렬)
• PGA 사용으로 직렬화 과정 없어 SGA에 비해 속도 우위

해시 조인

해시 조인 기본 메커니즘

• Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블(해시 맵) 생성
• Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인

use_hash(alias) 힌트로 유도

해시 테이블은 PGA Hash Area 에 저장 된다

해시 테이블이 너무 커서 PGA 에 담을 수 없으면, Temp 테이블 스페이스에 저장

Build와 Probe 단계에서 같은 해시 함수를 사용하므로 같은 값을 입력하면 같은 해시 값을 반환한다

따라서 해시 함수가 반환한 값에 해당하는 해시 체인만 스캔

해시 조인이 빠른 이유

PGA 영역을 사용하므로 래치 획득 과정이 없어 빠르게 탐색 가능하기 때문

!중요

해시 테이블에는 ROWID 만 있는 것이 아닌 SQL에 사용한 컬럼을 모두 저장 하고 있다

안그러면 ROWID로 다시 테이블 블록을 액세스해야 하기 때문

소트 머지 조인 보다 해시 조인이 빠른 이유는 사전 작업(정렬)의 과정과 해시 테이블(해시 맵)을 만드는 과정의 차이에서 발생하는 것

대용량 Build Input 처리

해시 조인하려는 두 테이블이 모두 대용량인 경우 인메모리 해시 조인이 불가능 하다

이럴 경우 분할 정복 방식으로진행된다

• 파티션 단계
  조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환되는 해시 값에 따라 동적으로 파티셔닝
  독립적으로 처리할 수 있느 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝 생성
  Temp 테이블 스페이스를 사용하므로 인메모리 보다 성능이 많이 떨어짐
• 파티션 짝에 대해 하나씩 조인 수행
  파티션 하기 전 테이블 크기와 상관 없이, 파티션 짝 별로 작은 쪽은 Build Input으로 선택하여 해시 테이블(해시 맵) 생성

해당 sql의 경우 use_hash(e, c) 힌트로 해시 조인을 유도 했다

이때 use_hash만 사용했으므로 Build Input은 옵티마이저에 의해 결정된다

일반적으로 각 테이블에 대한 카디널리티를 비교하고 작은 테이블을 선택한다

Build Input을 직접 선택하고자 한다면 ordered 나 reading 힌트를 사용하면 된다

3개 이상의 테이블을 해시 조인 하는 경우

Build Input을 선택한 상황에서 순서를 바꾸고 싶을 때는 swap_join_inputs 힌트를 이용하여 순서를 바꿀 수 있다

swap_join_inputs : Build Input으로 지정하는 힌트

no_swap_join_inputs : Probe로 지정하는 힌트

조인 메소드 선택 기준

소량 데이터 조인 ⇒ NL 조인(use_nl)

대량 데이터 조인 ⇒ 해시 조인(use_hash)

대량 데이터 조인이지만 해시 조인으로 처리 불가능 할 때(조건식이 등치 조건이 아닐 때) ⇒ 소트 머지 조인(use_merge)

수행 빈도가 매우 높은 쿼리에 대한 기준

• (최적화된) NL 조인과 해시 조인 성능이 같으면 ⇒ NL 조인
• 해시 조인이 약간 더 빨라도 NL 조인
• NL 조인보다 해시 조인이 매우 빠른 경우 ⇒ 해시 조인

NL 조인을 먼저 고려해야 하는 이유

NL 조인에서 사용하는 인덱스는 영구적으로 유지되면서 다양한 쿼리에 공유 및 재사용하는 자료구조. 하지만 해시 테이블은 단 하나의 쿼리를 위해 생성 되고 조인이 끝나면 곧바로 소명하는 자료구조이기 때문

해시 조인 사용 조건

• 수행 빈도가 낮고
• 쿼리 수행 시간이 오리 걸리는
• 대량 데이터 조인할 때

⇒ 배치, DW, OLAP성 쿼리 특징

서브쿼리 조인

서브쿼리 변환

쿼리 변환은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서 더 나은 성능이 기대되는 형태로 재작성하는 것

서브쿼리란

하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록

• 스칼라 서브쿼리 : 한 레코드당 하나의 값을 반환하는 서브쿼리. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외 사항을 제외하면 컬럼이 올 수 있는 대부분의 위치에 사용 가능
• 인라인 뷰 : FROM 절에 사용한 서브쿼리
• 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리. 특히, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 “상관관계 있는 서브쿼리” 라고 한다.

오라클에서는 3가지로 분류 된다.

필터 오퍼레이션

서브쿼리를 필터 방식으로 처리

no_unnest 힌트를 통해 서브쿼리를 풀지말고 수행하도록 힌트 적용

필터 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다.

부분 범위 처리도 가능!

NL 조인과의 차이점은 필터는 캐싱 기능을 가진다는 것

필터 처리한 결과, 서브쿼리 입력 값에 따른 반환 값을 캐싱하는 기능

이 기능이 작동하므로 서브쿼리 수행 전 캐시부터 확인 한다

필터 서브쿼리는 메인쿼리에 종속적이므로 항상 메인쿼리가 드라이빙 집합이다

서브쿼리를 unnesting 하면 필터 오퍼레이션보다 더 좋은 실행 방법을 찾은 가능성이 크다

이때 서브쿼리에 rownum을 사용했다면 unnesting 이 되지 않는다

rownum은 강력한 unnesting 방지 공식

Pusing 서브쿼리

서브쿼리 필터링을 가능한 한 앞 단계에서 처리되도록 강제하는 기능

unnesting 되지 않은 서브쿼리에서만 동작

push_subq 힌트를 통해 유도 할 수 있으며, unnesting 되지 않은 서브쿼리에서만 동작하므로 no_unnest 힌트와 함께 쓰는 것 바람직하다

서브쿼리 필터링을 가능한 한 나중에 처리하고 싶으면, no_push_subq 힌트를 사용할 수 있다

뷰와 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화 한다

인라인 뷰 안에 있는 당원에 거래한 모든 고객의 거래 데이터를 읽는다

merge 힌트로 인라인 뷰를 머징한 결과

해당 쿼리와 같이 변환되면서 인덱스를 이용하여 전월 이후 가입 고객에 대해서만 읽을 수 있게 변환 되었다

단점으로는 group by를 하고 나서야 데이터를 출력할 수 있으므로 부분 범위 처리가 불가능 하다

조인 조건 Pushdown

push_pred 힌트를 이용하여 유도할 수 있으며,

push_pred 힌트가 적용되면 불가능한 문법이지만 아래와 같은 쿼리로 옵티마이저가 최적화

따라서 가입일시 조건에 맞는 고객에 대해 당월 거래 내역만 읽어 group by를 수행하게 되고, 부분 범위 처리가 가능해진다

push_pred 힌트를 쓸 때는 merge로 유도되지 않도록 no_merge 힌트도 같이 쓰는 습관을 들이자

인라인 뷰에서 메인쿼리 테이블 컬럼을 참조하면 에러가 발생하는데, Lateral 로 인라인 뷰를 선언하게 되면 인라인 뷰에서 메인쿼리 테이블의 컬럼을 참조할 수 있다

다만, 기존 방법으로도 원하는 실행계획을 제어할 수 있으므로, 모종의 이유로 Pushdown이 안될 때 만 활용 하자

스칼라 서브쿼리의 특징

• 함수처럼 재귀적으로 실행되는 구조가 아니다
• 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행
• 처리과정에서 캐싱 작용이 일어난다(캐싱은 쿼리 단위로 이루어진다)
• PGA 메모리 공간을 사용한다

부작용

• 입력 값의 종류가 소수여야 효과적이다. 많으면 각 종류마다 캐싱이 일어나게되고 cpu, 메모리도 더 사용한다

스칼라 서브쿼리는 두 개 이상의 값 반환할 수 없다

스칼라 서브쿼리 unnesting

스칼라 서브쿼리는 병렬 쿼리에서는 사용될 수 없다

대량 데이터를처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이다

스칼라 서브쿼리를 unnesting 하면 스칼라 서브쿼리 인데도 해시 조인 이 가능한 형태로 풀릴 수 있다

인덱스 스캔 효율화

루트 블록 스캔 과정에서 특정 레코드를 찾았다면 그것이 가리키는 리프 블록으로 내려가면 안되고 그 이전 레코드가 가리키는 리프 블록 으로 내려가야 한다. 레코드의 값보다 크거나 같은 값들이 리프 블록에 존재하므로, 이전 레코드에 조건에 포함되는 레코드가 존재할 수 있기 때문이다

루트 블록에서 C1, C2가 B,3 이전의 레코드의 리프블록으로 내려가서 B, 3인 레코드를 찾고 B, 4인 레코드를 만나는 순간 스캔을 멈춘다

루트 블록에서 C1, C2가 B, 3 이전의 레코드의 리프블록으로 내려가 B, 3인 레코드를 찾고 C1이 C인 값을 만나면 스캔을 멈춘다. C2는 스캔을 멈추는데는 역할을 하지 못했지만 스캔의 시작점을 결정하는데는 역할을 했다

C2는 수직적 탐색(스캔 시작지점)을 찾는데는 역할을 하지 못했으나 스캔을 멈추는데는 역할을 했다

C2는 수직적 탐색(스캔의 시작지점)을 찾는데 역할을 했고, 스캔을 멈추는데도 역할을 했다. 즉, 스캔량을 줄이는데 역할을 했다

C1 조건은 스캔의 시작과 끝을 결정하는데 역할을 했으나, C2는 그렇지 못했다

C2는 스캔량을 줄이는데 거의 역할을 못했다

좌측은 성능검으로 시작하는 용어를 스캔할 때,

우측은 성능으로 시작하고 4번째 값이 선 인 단어를 스캔할 때의 경우이다

인덱스 선행 컬럼이 조건절에 없거나 = 조건이 아니면 인덱스 스캔 과정에서 비효율이 발생한다

인덱스를 7463개의 블록을 읽으며서 10개의 블록을 찾았고, 이 블록을 가지고 10개의 블록에 테이블 엑세스를 했다

단, 버퍼 피닝에 의해 같은 블록에 있는 데이터는 추가로 블록에 엑세스 하지 않아 7463블록에 8블록을 추가로 읽어 총 7471개의 블록을 읽었다

선두 컬럼 : 인덱스 구성상 ‘맨 앞쪽’에 있는 컬럼을 지칭할 때 사용

선행 컬럼 : 어떤 컬럼보다 ‘상대적으로 앞쪽’에 놓인 컬럼을 지칭할 때 사용

비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용 = 인덱스 루트와 브랜치 레벨에서 읽은 블록 수 + 인덱스 리프 블록을 스캔하는 과정에 읽는 블록 수 + 테이블 액세스 과정에 읽는 블록 수

테이블과 달리 인덱스에는 같은 값을 갖는 레코드들이 서로 군집해 있다

첫번째 나타나는 범위 검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다

인덱스 선행 컬럼이 모두 = 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문!

Between 을 IN-List로 전환

IN-Llist를 사용하는 경우 IN-List 내 존재하는 개수 만큼 수직적 탐색이 발생한다

IN-List 항목 개수가 늘어날 수 있다면 BETWEEN을 IN-List로 전환하는 방식은 사용하기 곤란하다

이런 경우는 NL조인이나 서브쿼리로 구현하면 된다

BETWEEN 조건을 IN-List 조건으로 전환할 때 IN-List 개수가 많으면 수직적 탐색이 많이 발생한다

이는 BETWEEN 조건으로 리프 블록을 많이 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다

NUM_INDEX_KEYS 힌트 : 인덱스의 지정한 번호의 컬럼까지만 액세스 조건으로 사용하라는 의미

해당 힌트를 쓰지 않고 컬럼을 가공하여 같은 효과를 낼 수 있다

BETWEEN과 LIKE 비교

BETWEEN의 경우 판매월이 201912이고 판매구분이 B인 첫번째 레코드를 만나면 스캔을 멈춘다

LIKE는 판매월이 201912인 레코드를 모두 스캔하고나서야 멈춘다

OR 조건 활용한 옵션 처리

인덱스 선두 컬럼에 대해 옵션 조건에 OR 조건을 사용하면 인덱스를 사용할 수 없다

인덱스에 포함되지 않는 컬럼의 경우는 OR 조건을 사용해도 괜찮다

OR 조건을 활용한 옵션 조건 처리

• 인덱스 액세스 조건으로 사용 불가
• 인덱스 필터 조건으로도 사용 불가
• 테이블 필터 조건으로만 사용 가능

OR 조건 사용의 장점은 NULL 허용 컬럼이어도 사용할 수 있다는 것

LIKE/BETWEEN 패턴을 사용하고자 할 때 점검해야할 것

• 인덱스 선두 컬럼

• NULL 허용 컬럼
  이때 고객ID가 NULL 허용 컬럼이면 결과집합에 오류가 생긴다

• 숫자형 컬럼
• 가변 길이 컬럼

UNION ALL 을 사용하면 코딩량은 늘지만 고객ID를 입력할 때와 그러지 않을 때에 대해 인덱스를 사용할 수 있다

PL/SQL 사용자 정의 함수는 생각보다 느리다

그 이유는

• 가상머신 상에서 실행되는 인터프리터 언어
• 호출 시마다 컨텍스트 스위칭 발생
• 내장 SQL에 대한 Recursive Call 발생 *

효율적인 인덱스 구성을 통한 함수 호출 최소화

조건절에 PL/SQL 함수를 사용했을 때 Full Scan으로 읽으면 해당 함수가 테이블 건수만큼 수행된다

하지만 다른 조건절과 같이 있으면 함수는 조건절을 만족하는 건 수 만큼만 실행되게 되므로 좀 더 효율적이게 된다

마지막으로 함수가 = 로 동등 비교하게 되는 컬럼이 포함된 인덱스를 사용하여 해당 컬럼이 인덱스 액세스 조건으로 사용되면 함수는 한번만 수행된다

인덱스 구성 시 중요한 두가지 선택 기준

• 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다
• = 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다

스캔 효율성 이외의 판단 기준

• 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하
• 저장 공간
• 인덱스 관리 비용 등

I/O를 최소화 하면서 소트 연산을 생략하기 위한 인덱스 구성 공식

• = 연산자로 사용한 조건절 컬럼 선정
• ORDER BY 절에 기술한 컬럼 추가
• = 연산자가 아닌 조건절 컬럼은 데이터 붆포를 고려해 추가 여부 결정

IN-LIst 는 = 이 아니다

IN-List 가 = 이 되기 위해 IN-List Iterator 로 풀리게 되면 UNION ALL로 결과 집합을 묶기 때문에 Sort 연산을 생략 할 수 없다

인덱스 ROWID = 논리적 주소

DBA = 데이터파일번호 + 블록번호 : 디스크 상에서 블록을 찾기 위한 주소 정보

버퍼캐시 : I/O 성능을 높이기 위해 사용된다

인덱스를 이용해 테이블 블록을 찾아가는 과정

인덱스를 통해 찾은 ROWID를 통해 데이터 가져올 때 버퍼캐시에서 데이터를 찾기 위해 DBA(데이터파일번호 + 블록번호)를 해시 함수에 입력하여 해시 체인을 찾은 후 해시 체인에서 버퍼 헤더를 찾는다

버퍼 헤더는 메모리의 주소값을 가지고 있기 때문에 그 포인터를 이용해 버퍼 블록을 찾는다

인덱스로 테이블 블록을 엑세스할 때는 리프 블록에서 읽은 ROWID를 분해하여 DBA 정보를 얻는다

테이블 Full Scan을 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다

ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾고, 못 찾으면 디스크에서 블록을 읽어 버퍼캐시에 적재한 후 읽는다

인덱스 클러스터링 팩터(CF)

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정보

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다

인덱스 클러스터링 팩터가 가장 좋은 상태

인덱스 클러스터링 팩터가 가장 안 좋은 상태

CF가 좋은 컬럼에 생성한 인덱스가 검색 효율이 좋은 이유

인덱스 ROWID로 테이블을 액세스할 때, 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지하는데, 이를 버퍼 Pinning이라고 한다

이를 통해 다음 인덱스 레코드를 읽을 때 같은 테이블 블록인 경우 래치 획득과 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있어 논리적인 블록 I/O 과정을 생략할 수 있기 땜누에 블록 I/O가 적게 발생한다

인덱스 손익 분기점

Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점

인덱스를 이용한 테이블 액세스가 Table Full Scan 보다 더 느려지게 만드는 가장 핵심적인 두 가지 요인

• Table Full Scan은 시퀀셜 액세스이나 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식
• Table Full Scan은 Multiblock I/O인 반면 인덱스 ROWID를 이용한 테이블 액세스는 SingleBlock I/O 방식

인덱스 손익 분기점은 보통 5 ~ 20%의 낮은 수준에서 결정된다(CF에 따라 크게 달라진다)

온라인 프로그램 : 소량 데이터를 읽고 갱신, 인덱스를 효과적으로 활용하는 것이 중요

배치 프로그램 튜닝 : 항상 전체범위 처리 기준으로 튜닝 필요, 처리대상 집합 중 일부를 빠르게 처리하는 것이 아닌 전체를 빠르게 처리하는 것을 목표로 한다

Covered 쿼리 : 인덱스만 읽어서 처리하는 쿼리

이때 사용하는 인덱스를 Covered 인덱스라고 부른다

Include 인덱스

include로 포함된 컬럼은 리프 블록에만 저장한다

따라서 수직탐색에는 사용할 수 없다

다만, 수평탐색에서는 필터조건으로 사용할 수 있다(테이블 랜덤 액세스 횟수 줄이는데 사용 가능)

위에 두 인덱스가 있을 때 인덱스 스캔량은 emp_x02가 더 적다, sal 도 액세스 조건으로 사용하기 때문

emp_x02는 소트 연산 생략이 가능하지만 emo_x01은 불가능하다

include 인덱스는 랜덤 액세스를 줄이는 용도로 개발되었다

인덱스 구조 테이블(오라클에서는 IOT(Index-Organized Table), MS 는 클러스터형 인덱스)

테이블을 인덱스 구조로 만드는 구문

일반 테이블은 heap 구조

힙구조는 데이터를 입력할 때 랜덤 방식을 사용(순서 없음), IOT 구조는 인덱스 구조 테이블이므로 정렬 상태를 유지(인위적으로 클러스터 팩터를 좋게 만드는 방법)

클러스터 테이블

인덱스 클러스터 테이블

클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조, 한 블록에 다 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결

여러 테이블 레코드를 같은 블록에 저장할 수 있는데, 이런 경우는 “다중 테이블 클러스터” 라고 부른다

일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없음

인덱스 클러스터 테이블 구성

1. 클러스터 생성

2. 인덱스 생성

3. 클러스터 테이블 생성

클러스터 인덱스도 B*Tree 인덱스 구조이지만 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다

일반 테이블 인덱스 레코드는 테이블 레코드와 1:1 이지만 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다

클러스터 인덱스의 키 값은 항상 Unique 하다

해시 클러스터 테이블

인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다

클러스터 생성 시 아래와 같이 hashkeys ‘숫자’ 를 입력하여 생성한다

부분 범위 처리

전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송

정렬 조건이 있을 때 부분 범위 처리

정렬이 있는 경우 모든 데이터를 읽어 정렬을 마치고 나서야 전송을 시작할 수 있기 때문에 전체 범위 처리가 된다

단, 정렬절에 있는 컬럼이 선두인 인덱스가 존재한다면 부분 범위 처리가 가능하다.(인덱스가 정렬되어있기 때문에 정렬을 하지 않아도 되므로 가능)

모든 DBMS는 데이터를 조금씩 나누어 전송한다. 다만 부분 범위 처리를 프로그램에 적용하고 안하고는 개발자의 목이다

부분 범위 처리가 의미가 있기 위해서는 멈출 수 있어야 한다

DB에 직접 접속하는 2-Tier 환경에서는 구현이 가능하지만, 클라이언트와 DB 사이에 WAS, AP 서버 등 이 존재하는 n-Tier 환경에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없기 때문에 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서를 닫아야 한다

배치 I/O

읽는 블록마다 건건이 I/O Call을 발생시키는 것이 아닌 테이블 블록에 대한 디스크 I/O Call을 미루었다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 방식

* 데이터 순서를 보장할 수 없다

데이터를 찾는 방법은 두가지가 존재한다.

1. 테이블 전체 스캔
2. 인덱스 이용

인덱스 튜닝의 두 가지 핵심 요소

1. 인덱스 스캔 효율화 튜닝
   - 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
2. 랜덤 액세스 최소화 튜닝⭐️
   - 테이블 액세스 횟수를 줄이는 것

SQL 튜닝은 랜덤 1/0와의 전쟁

데이터베이스 성능이 느린 이유는 디스크 I/O 때문!

OLTP(OnLine Transaction Processing)에서는 디스크 I/O 중에서도 랜덤 I/O가 특히 중요

인덱스는 정렬되어있기 때문에 범위 스캔(Range Scan) 가능 하다

DBMS 의 인덱스는 일반적으로 B*Tree(Balanced Tree) 인덱스를 사용한다

인덱스 내에는 가장 왼쪽 첫 번째 레코드를 가리키는 LMC(LeftMost Child) 레코드가 존재한다

LMC가 가리키는 주소의 블록에는 키 값을 가진 첫 번째 레코드 보다 작거나 같은 레코드가 저장되어있다

리프 블록에 저장된 각 레코드는 키 값 순으로 정렬돼 있을 뿐 아니라 테이블 레코드를 가리키는 주소값(ROWID)을 갖는다

ROWID = 데이터 블록 주소 + 로우 번호

ROWID를 알면 테이블 레코드를 찾아갈 수 있다

ROWID = 데이터 블록 주소 + 로우 번호

데이터 블록 주소 = 데이터 파일 번호 + 블록 번호

블록 번호 = 데이터파일 내에서 부여한 상대적 순번

로우 번호 = 블록 내 순번

인덱스 탐색 : 인덱스 탐색은 두 가지로 나뉜다

1. 수직적 탐색
   - 인덱스 스캔 시작지점을 찾는 과정
   - 수직적 탐색 과정에서 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동 한다
2. 수평적 탐색
   - 데이터를 찾는 과정
   - 스캔 시작점으로 부터 찾고자 하는 데이터가 더 안 나타날 때까지 인텍스 리프 블록을 수평적으로 스캔

결합 인덱스 : 두 개 이상의 컬럼을 결합해서 만든 인덱스

인덱스는 B*Tree(Balanced Tree, 루트에서 리프 블록까지 읽는 블록 수 가 같음) 이므로 컬럼 순서에 따라 일량의 차이가 나지는 않는다

인덱스의 기본 사용 방법은 인덱스를 Range Scan 하는 방법을 의미

• 인덱스 컬럼을 가공하지 않아야 인덱스를 정상적으로 사용(Range Scan)할 수 있다
• Like를 이용한 중간 값 검색을 할 때도 Range Scan할 수 없다
• OR 으로 검색하는 경우 Range Scan 불가능 → Or Expansion을 사용하면 Range Scan 가능(use_concat 힌트) or 를 union all sql로 변환 하여 실행하기 때문

* 이유: 인덱스 스캔 시작점을 찾을 수 없기 때문!

IN 조건절의 경우 OR 조건을 표현하는 다른 방식으로 SQL 옵티마이저가 IN 개수 만큼 Index Range Scan을 반복한다.(IN-List Iterator)

인덱스를 정상적으로 사용한다 = 리프 블록에서 스캔 시작점을 찾아서 스캔하다가 중간에 멈추는 것을 의미

인덱스 Range Scan을 위한 첫 번째 조건 : 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다

인덱스는 데이터가 정렬되어있기 때문에 인덱스를 이용하여 소팅 연산을 생략하는 효과를 볼 수 있다

ORDER BY 의 정렬 요소를 가공하면 정렬 연산을 생략할 수 없다

ORDER BY 에 인덱스 요소를 가공하여 사용하였기 때문에 별도 정렬 연산이 발생한다

ORDER BY 에 인덱스인 주문번호가 들어간 것 같지만 실제로는 TO_CHAR로 변환된 주문번호가 들어가기 때문에 소팅 연산이 발생 한다 이 경우 테이블 alias 인 A를 ORDER BY 주문번호에 붙여주면 정렬 연산을 생략할 수 있다

인덱스는 정렬되어 있기 때문에 인덱스 구성요소에 대한 MIN ,MAX를 구하는 경우 인덱스 리프블록의 왼쪽(MIN), 오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다

가공 후 MIN, MAX를 사용하는 경우에 대해서는 정상적으로 동작하지 못한다

스칼라 서브쿼리의 수 만큼 테이블을 여러번 읽어야하기 때문에 비효율적이고 SQL도 복잡해진다

인덱스를 사용하기 위해 조건절을 지정하는 경우 자동 형변환을 조심해야한다

이 경우 인덱스를 정상적으로 사용하지 못하는데, 그 이유는 생년월일 데이터가 TO_NUMBER 로 자동 형변환이 일어났고 이에 따라서 인덱스 컬럼이 가공되어 인덱스를 정상적으로 사용하지 못한 것이다

자동 형변환

문자 < 숫자

문자 < 날짜

LIKE를 사용하는 경에는 문자형 기준으로 숫자형 컬럼이 변환

자동 형변화 시 주의사항

숫자형 컬럼과 문자형 컬럼을 비교하면 문자형 컬럼이 숫자로 변환된다

이때 문자형이 숫자로 변환이 불가능하는 경우에는 에러가 발생한다

Index Range Scan

B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식

인덱스 루트에서 리프 블록까지 수직적 탐색 후 필요한 범위만 스캔

* 인덱스의 선두 컬럼을 가공하지 않은 상태로 조건절에 사용!

Index Full Scan

수직적 탐색 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다

인덱스 선두 컬럼이 조건절에 없는 상황에서 조회 대상 테이블이 대용량 테이블인 경우 Full Scan에 따른 부담이 크다면 옵티마이저가 Index Full Scan을 고려한다

Index Full Scan을 하면 Range Scan과 마친가지고 결과 집합이 인덱스 컬럼 순으로 정렬되어있다. 따라서 Sort 연산을 생략할 목적으로 사용할 수 있다

Index Full Scan을 사용할 때 아래와 같이 대부분의 레코드에 대해 테이블 엑세스가 발생하는 경우 Full Scan 보다 오히려 불리할 수 있다 다만 아래의 경우는 결과 집합의 일부만 빠르게 출력할 목적으로 first_rows 힌트를 사용했기 때문에 Index Full Scan을 사용했다

* 부분범위 처리가 가능한 상황에서는 극적인 성능 개선 효과를 가져다 줄 수 있다

Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식으로 Unique 인덱스를 = 조건으로 탐색하는 경우 동작(인덱스에 포함된 모든 컬럼에 대해 조건절에 = 조건으로 검색할 때)

Index Skip Scan

인덱스 선두 컬럼이 조건절에 없어도 사용할 수 있는 방법(9i에 등장) 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많은 때 유용 Distinct Value가 적은 두 개의 선두컬럼이 모두 조건절에 없는 경우, 중간 컬럼이 없는 경우도 사용 가능

Index Fast Full Scan

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하는 방법(물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽는다.)

- 결과 집합이 정렬되지 않음.

- 쿼리에 사용한 컬럼이 모두 인덱스에 포함되어있을 때만 사용 가능

- 인덱스가 파티션 돼 있지 않아도 병렬 쿼리가 가능

병렬 쿼리 시 에는 Direct Path I/O 방식 사용으로 I/O 속도가 더 빨라진다

Index Range Scan Descending

Index Range Scan과 동일 방식으로 스캔하나 인덱스 뒤에서 부터 앞쪽으로 스캔하기 때문에 결과 집합이 내림차 순으로 된 결과를 얻을 수 있다.(index_desc 힌트로 유도 가능)