SQL 튜닝 이론 정리1

인덱스의 구조

DBMS가 제공하는 인덱스 중 가장 일반적으로 사용되는 인덱스는 B*TREE 인덱스입니다. 루트블록에서 브랜치 블록을 거쳐 리프 블록까지 연결되는 구조입니다. 루트블록에서 리프블록까지의 거리가 인덱스의 깊이 입니다. 인덱스의 깊이는 루트블록에서 리프블록까지를 반복적으로 탐색할때 성능에 영향을 줍니다.

 

인덱스블록의 레코드는 컨텐츠(KEY)와 페이지(ROWID)로 이뤄집니다. 루트와 브랜치 블록의 레코드는 하위 블록의 데이터 범위를 키, 하위블록을 찾는데 필요한 주소정보를 ROWID로 합니다. 블록내부는 KEY를 기준으로 항상 정렬돼있습니다. 키가 동일하면 ROWID 순으로 정렬합니다. 인덱스 리프블록은 항상 키 순으로 정렬돼있어 양방향으로 범위스캔이 가능합니다. 이를 양방향 연결리스트 구조라고 합니다.

 

데이터 테이블의 인덱스 생성시 테이블의 레코드 수 만큼의 인덱스가 생성됩니다. 인덱스 레코드의 볼륨은 데이터 테이블 레코드의 볼륨의 1/3 수준으로 동일한 크기의 블록에 데이터 레코드 대비 3배 더 많은 인덱스 레코드를 저장할 수 있습니다.

 

ORACLE에서 인덱스 생성시, 인덱스 구성칼럼이 모두 NULL인 데이터 레코드는 인덱스 레코드를 생성하지 않습니다. 인덱스 구성칼럼 중 하나라도 값이 NULL이 아닌 칼럼이 있으면 인덱스 레코드를 생성합니다.

 

인덱스 생성직후, 루트블록과 리프블록은 동일합니다. 하나의 인덱스 블록에 저장가능한 최대 레코드의 수를 넘으면 루트블록과 리프블록이 분리됩니다. 인덱스의 깊이에 따라 인덱싱 할 수 있는 레코드의 수가 달라집니다. 일반적으로 400 ^ 인덱스 깊이 만큼의 데이터 레코드를 인덱싱 할 수 있습니다.

 

루트블록은 인덱스 트리의 최상위 블록으로 인덱스의 하위블록에 속한 인덱스의 값 범위와 하위 블록에 대한 주소가 있습니다. 즉, 루트블록에는 자신의 하위블록의 수 만큼의 인덱스 레코드가 존재합니다.

 

브랜치블록은 루트블록과 리프블록의 연결고리 역할을 합니다. 루트블록과 마찬가지로 하위 블록에 속한 인덱스 레코드의 범위와 하위블록에 대한 주소가 있습니다. 브랜치블록에는 하위 브랜치블록 또는 리프 블록의 수 만큼의 인덱스 레코드가 있습니다.

 

리프블록에는 데이터 블록의 값의 범위와 데이터 블록에 대한 ROWID가 있습니다. 키 순으로 정렬돼있으며 인접리프블록과 연결돼있습니다.

 

Random Access 와 Sequential Access

 

Random Access는 하나의 블록을 읽어 하나의 레코드만 꺼내 읽습니다. 인덱스 수직적 탐색을 통해 리프블록에 접근하기 위해 사용됩니다. Sequential Access는 블록에 존재하는 모든 레코드를 읽습니다. 인덱스 리프노드의 수평적 탐색에 사용되는 방식으로 낭비되는 레코드가 없어 적은비용으로 효육적인 탐색이 가능합니다.

 

인덱스 기본원리

 

쿼리의 상태에 따라 인덱스를 사용할 수 없거나 인덱스의 범위 스캔이 불가능한 경우가 있습니다.

 

인덱스 칼럼의 가공

 

인덱스는 칼럼의 원형값을 기준으로 정렬해있습니다. 쿼리에서 인덱스 칼럼을 가공하면 정상적으로 인덱스를 사용할 수 없습니다.

 

인덱스 선두 칼럼 NULL 검색

 

인덱스의 선두 칼럼을 NULL로 검색하면 인덱스를 사용하지 않고 TABLE FULL SCAN 합니다. 그 이유는 인덱스 구성칼럼이 모두 NULL 인 경우가 조건에 해당하지만 이 경우 인덱스 레코드를 생성하지 않기 때문입니다. 단, 조건절에 후행칼럼을 포함하는 경우 (예를들어 C1 IS NULL AND C2 ='X') 인 경우에는 위 경우에 해당하지 않기 때문에 인덱스를 사용할 수 있습니다.

 

묵시적 형변환

조건절 칼럼의 데이터타입과 상수의 데이터타입이 상이할 경우 묵시적 형변환이 발생합니다. 이때 칼럼이 변환되면 인덱스를 활용할 수 없습니다.

 

인덱스 스캔방식

 

INDEX RANGE SCAN

 

INDEX RANGE SCAN은 조건에 해당하는 범위의 리프블록을 모두 탐색하는 방식입니다. 작은 범위의 탐색에서는 빠른 속도를 보장하지만 탐색 범위가 일정 정도를 넘어가면 인덱스를 사용하지 않고 데이터 테이블 전체를 스캔하는 것 보다 비효율적입니다. 스캔 범위에 스캔후 발생하는 테이블 엑세스의 횟수를 얼마나 줄일 수 있는지가 관건입니다. INDEX RANGE SCAN을 위해서는 인덱스를 구성하는 선두 컬럼이 조건절에 반드시 포함돼야합니다.

 

INDEX FULL SCAN

적절한 인덱스를 사용할 수 없는경우 옵티마이저는 일반적으로 TABLE FULL SCAN 방식으로 수행합니다. 조건절에 쓰인 칼럼이 인덱스 선두칼럼은 아니지만 조건절에 사용된 모든 칼럼이 인덱스 구성칼럼인 경우 옵티마이저의 판단하에 INDEX FULL SCAN 방식을 사용합니다. 테이블 렌덤 액세스로 이어지는 레코드의 수가 적으면 INDEX FULL SCAN이 유리하고 많으면 TABLE FULL SCAN 방식이 유리합니다.

 

INDEX UNIQUE SCAN

 

조건절이 칼럼이 모두 인덱스에 포함돼있고 등치조건(=)으로만 검색하는 경우 INDEX UNIQUE SCAN 방식을 사용합니다.

 

INDEX SKIP SCAN

 

조건절에 인덱스 선두 칼럼은 없지만 선두 칼럼이 성별과 같이 DISTINCT가 매우 낮은 경우 이 방식을 활용할 수 있습니다. 이외에도 인덱스 선두 칼럼이 BETWEEN, LIKE, 부등호 조건으로 비교되는 경우에도 활용가능합니다.

 

INDEX FAST FULL SCAN

 

인덱스 리프블록 전체를 MULTIBLOCK READ 방식으로 스캔합니다. 조건절에 인덱스 선두 칼럼이 포함되지 않는 경우 옵티마이저는 INDEX FULL SCAN을 선택하는데 조건절에 사용된 모든 칼럼이 인덱스 후항칼럼이라 RANDOM I/O가 발생하지 않는경우 이 방식을 활용가능합니다.

 

ORACLE DBMS 구조

 

클라이언트가 리스너를 통해 서버 프로세스를 호출하면 서버 프로세스는 클라이언트 별로 PGA를 할당합니다. 클라이언트는 PGA에서 작업하고 SERVER PROCESS를 통해 DATABASE SERVER와 통신합니다. DATABASE SERVER는 공용 작업공간인 SGA 메모리 영역과 DATA BASE로 이루어져있습니다.

 

SGA 메모리 영역은 DATA BUFFER CACHE, REDO LOG BUFFER, SHARED AREA 로 구성됩니다. DATABASE는 DATAFILES, CONTROL FILES, REDO LOG FILES 3가지의 파일로 구성됩니다. DATAFILE은 사용자가 저장한 데이터, DBMS 유지를 위한 스키마정보를 저장합니다. CONTROL FILES 에는 DBMS에서 사용하는 파일의 경로가 저장됩니다. REDO LOG FILES에는 데이터의 변경내역을 기록합니다.

 

SGA

 

LOG BUFFER

DATA에 대한 INSERT, DELETE, UPDATE가 발생하면 REDO LOG BUFFER에 이력을 저장합니다. DB를 복구할때 과거에 실행한 SQL을 이용하기 위해서입니다. DML이 발생하면 DATA BUFFER CACHE에 저장하기 앞서 REDO LOG BUFFER에 변경이력을 저장합니다.

이후, COMMIT을 만나면 LOG WRITER가 REDO LOG BUFFER에 있는 정보를 데이터 베이스의 REDO LOG FILE에 저장합니다.

REDO LOG BUFFER를 작성할때 DATA BUFFER CHACHE 값을 사용하지 않는 이유는 RANDOM ACCESS가 발생하기 때문입니다.

REDO LOG BUFFER는 APPEND 방식으로 저장되고 순차적으로 저장된 데이터를 읽어올 수 있어 RANDOM ACCESS 방식에 비해 부하가 적습니다.

 

SHARED AREA (SHARED POOL)

 

SHARED AREA는 LIBRARY CACHE 와 DICTONARY CACHE로 구성됩니다. LIBRARY CACHE에는 SQL FULL TEXT와 실행계획이 있고 DICTONARY CACHE에는 테이블, 칼럼, 인덱스에 대한 구조정보가 있습니다.

 

SGA와 PGA 비교

 

SGA는 서버 프로세스의 공용 공간으로 프로세스간 경합과 LOCK이 발생합니다. PGA는 각각의 서버프로세스에 있는 작업공간으로 프로세스간 경합이 발생하지 않아 엑세스 속도는 빠르지만 적은 용량이 할당돼있습니다.

 

BACKGROUND PROCESS

 

PMON

 

동작하지 않지만 메모리를 점유하고 있는 좀비프로세스를 정리합니다.

 

SMON

비정상적으로 종료된 인스턴스의 리커버리를 담당합니다. 인스턴스 리커버리는 다음과 같은 과정을 거칩니다.

 

ROLL FORWARD - ROLL BACK - CHECKPOINT

 

트랜잭션 중 인스턴스에 문제가 발생하면 인스턴스가 변경한 데이터를 복구하기위해 REDO LOG FILE에있는 소스를 재실행합니다. 이를 ROLL FORWARD라 합니다. 커밋하지 않은 트랜잭션을 되돌리는 것을 ROLL BACK이라고 합니다. ROLL FORWARD, ROLL BACK을 마치면 CHECKPOINT를 표시하고 DATABASE를 정상화 합니다.

 

DBWR

 

DATA BUFFER CACHE에 있는 데이터를 DATAFILE로 WRITE 합니다.

 

LGWR

 

REDO LOG BUFFER에 있는 데이터를 REDO LOG FILES로 WRITE합니다.

 

CKPT

 

CKPT를 만나면 다른 거래를 모두 막고 LGWR를 실행한 후 DBWR를 기동합니다. 메모리와 데이터파일을 동기화 하고 정합성을 보장하는 역할을 합니다.

 

TABLE RANDOM ACCESS 부하

 

인덱스 리프노드에 있는 ROWID의 구성은 다음과 같습니다.

 

DATA BLOCK ADDRESS (DATA OBJECT NUMBER + DATA FILE NUMBER + BLOCK NUMBER) + ROW NUMBER

 

DATA BLOCK ADDRESS를 활용한 블록 접근

 

HASH FUNCTION에 DBA를 넣어 얻은 결과값을 기반으로 데이터 블록의 메모리주소를 획득합니다. DBA는 본래 디스크내의 블록주소지만 디스크에 접근하기 앞서 메모리를 먼저 조회히애하기때문에 HASH FUNCTION을 통해 메모리의 블록주소를 얻습니다.

 

HASH BUFFER CHAIN LATCH

 

BUFFER HEADER에서 메모리 주소를 얻는 과정에서 다른 프로세스와의 경합으로 인해 발생합니다.

 

BUFFER PINNING

 

프로세스가 다음번 READ때 현재의 READ와 동일한 BLOCK을 READ 할 것으로 예상되는 경우 현재 읽은 블록이 메모리에서 떠나지 않도록 pinning 하고, 블록의 주소인 DBA가 가리키는 메모리 번지수를 PGA에 저장해 다음반에는 즉시 찾아갈 수 있도록 하는 기법입니다. BUFFER PINNING은 LOGICAL READ COUNT에 잡히지 않기때문에 전체 BLOCK COUNT가 감소합니다.

 

CLUSTERING FACTOR

 

실제 디스크의 데이터가 인덱스의 키 순서대로 얼마나 정렬해있는지 정도를 나타내는 지표입니다. 인덱스를 순차적으로 읽으면서 이전 ROWID의 블록과 다음 ROWID의 블록이 다르면 1씩 증가합니다.

 

INDEX RANGE SCAN의 비용

 

인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색비용(리프블록 * 유효 인덱스 선택도) + 테이블 RANDOM ACCESS(클러스터링 팩터 * 유효 테이블 선택도)

 

TABLE RANDOM ACCESS 최소화 튜닝

 

결과값에 대한 모든 필터링을 테이블이 아닌 인덱스에서 하면 테이블 RANDOM ACCESS에 대한 비효율이 없습니다.

테이블에 인덱스가 과도하게 많으면 실행계획의 재사용이 힘들어지기 때문에 효율이 낮아집니다. 그렇기에 기존 인덱스에 새로운 칼럼을 추가하는 방식이 추천됩니다.

 

테이블을 생성하면 PK에 대한 인덱스가 자동으로 생성됩니다. PK 인덱스에 조건절에서 쓰이는 칼럼을 추가해 RONDOM ACCESS를 최소화 할 수 있습니다. 단, 인덱스 칼럼이 많아지면 클러스터링 팩터가 감소합니다.

 

Index Organized Table 과 클러스터 테이블

 

IOT (Index Organized Table)

 

IOT 테이블로 생성한 인덱스에는 테이블의 모든 레코드가 포함돼있습니다. 이런 특징으로 IOT 테이블은 TABLE RANDOM ACCESS 없이 빠른 조회가 가능합니다. INDEX 리프노드의 저장공간이 부족해지면 인덱스 스플릿이 발생합니다. IOT는 데이터 자체가 ROWID이기 때문에 변경이 불가하므로 IOT 리프노드는 ROWID 대신 PRIMARY KEY로 식별합니다.

 

이에 Secondary Index로 테이블을 ACCESS하기 위헤서는 추가적인 탐색과정을 거칩니다. IOT를 사용하는 것이 유리한 경우는 다음과 같습니다.

 

- 크기가 작고 NL 조인으로 자주 조인하는 테이블
- 폭이 좁고 긴 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회패턴이 서로 다른 테이블

 

클러스터 인덱스

 

동일한 키의 레코드가 동일한 블록에 모여있는 테이블의 인덱스입니다. 만약 한 블록에 동일한 키의 레코드를 모두 담을 수 없으면 새로운 블록을 할당하고 클러스터 체인으로 연결합니다. 클러스터 인덱스를 활용하면 물리적으로 동일한 블록에 여러 테이블의 레코드를 저장할 수 있습니다. 인덱스의 키값은 항상 UNIQUE 하고 테이블의 레코드와는 1:M 관계입니다. 넓은 범위의 데이터를 검색하는데 유리합니다. PRIMARY 키에 속한 데이터가 자주 변경되는 경우에는 적합하지 않습니다.

 

인덱스 스캔 효율

 

인덱스 스캔의 효율을 높이는 요소는 다음과 같습니다.

 

- 인덱스 매칭도
- 조건절에 사용하는 비교 연산자의 종류와 인덱스 칼럼 순서에 따른 인덱스 레코드의 군집성
- 인덱스 선행 칼럼이 등치조건(=)으로 사용
- BETWEEN을 IN-LIST로 변환
- INDEX SKIP SCAN을 이용한 탐색 레코드 수 줄이기
- 범위조건 최소화
- ACCESS PREDICATE/FILTER PREDICATE
- INDEX FRAGMENTAION

 

인덱스 매칭도를 높이는 방법

인덱스 선두칼럼은 가급적 등치조건으로 비교합니다

 

실행계획

 

SELECT *
FROM EMP E, DEPT D
WHERE E.SAL > 1500
AND E.JOB = 'MANAGER'
AND D.DEPTNO = E.DEPTNO;
-- EXECUTION PLAN
A-ROWS
-- SELECT STATEMENT                                      3
--  NESTED LOOPS                                         3
--
율효비 스세엑덤랜블이테 함 링터필 을건 서에블이테 만지했스세액 덤렌 블이테 을건 해통 을캔스
--
TABLE ACCESS BY INDEX ROWID OF EMP (TABLE)
3 --
--
INDEX RANGE SCAN` OF IX_EMP_X01(INDEX) 7 74() 지인레 스덱인
INDEX UNIQUE SCAN OF PK_DEPT(INDEX) 3
--  TABLE ACCESS BY INDEX ROWID OF DEPT(TABLE)           3
-- PREDICATE INFORMATION
-- 3 - FILTER("E", "JOB" == 'MANAGER')
-- 4 - ACCESS("E", "SAL" > 1500)
-- 5 - ACCESS("D", "DEPTNO" = "E"."DEPTNO")