저의 직장에서는 대부분 오라클을 사용합니다.
한 때 티베로를 활용한 프로젝트를 해봤지만, 지금도 여전히 오라클을 기반으로 주로 구축하고, 운영하고 있습니다.
오라클을 사용하는 이유는 무엇일까요?
저 개인적인 생각은,
많이 사용하는 데이터베이스이다보니 레퍼런스가 많습니다.
시스템을 구축 또는 도입하기 위해 정확한 측정을 하기에는 어려움이 있습니다.
그럴때 가장 중요한 요소가 비슷한 동종업계 구축사례를 통해 객관적인(?) 근거를 확보할 수 있죠.
그리고 구글링을 통해 이슈가 있는 부분에 대해 해결책을 찾기도 좋습니다.
단순 우리나라뿐 아니라 외국 사례에서도 많은 해결법을 찾을 수가 있습니다.
오라클에서 많이 사용하는 인덱스와 관련된 내용을 조금 작성해 보려고 합니다.
Oracle OLTP 환경에서 수많은 데이터 중 특정 건을 조회하고 활용할때에는 Index를 빼고 이야기를 할 수 없을듯 합니다.
이 글을 보고 계신다면 기본적인 인덱스의 개념은 가지고 읽으시리라고 봅니다.
그럼 인덱스가 항상 유리하냐? 그게 아니니까 이 내용을 기록하는 거겠죠?
인덱스가 유리한 경우는 손익분기점을 넘지 않는 조건에서는 유리합니다.
반대로 손익분기점을 넘어버리면 많이~ 아주 많이 느려지는 경우도 있습니다.
그 원인은 아래 내용과 관련이 있습니다.
■ 인덱스 손익분기점
- 의미 : Index Rang Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점
- 인덱스에 의한 액세스가 Full Table Scan 보다 더 느려지게 만드는 가장 핵심적이 두가지 요인
① 인덱스 rowid에 의한 테이블 액세스 = Random 액세스
Full Table Scan = Sequential 액세스
② 인덱스 rowid에 의한 테이블 액세스 = Single Block Read
Full Table Scan = Multiblock Read
- 일반적으로 5 ~ 20% 수준에서 결정 되지만 CF에 따라 달라짐.
- 인덱스 클러스터링 팩터가 나쁘면 테이블 블록을 여러 번 반복액세스해 논리적I/O가 증가하고 물리적 I/O발생량도 증가
- 인덱스 손익 분기점 데이터는 상황에 따라 크게 달라지므로, 정해진 수치 또는 범위를 정해 놓고 인덱스의 효용성을 판단하면 예상하지 않은 결과가 초래
■ 인덱스 클러스터링 팩터
- 군집성 계수(= 데이터가 모여있는 정도)
- 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미 (Sequential 액세스 특성)
- 인덱스 클러스터링 팩터가 가장 좋은 상태는 인덱스 레코드 정렬 순서와 거기서 가리키는 테이블 레코드 정렬 순서가 100% 일치하는 것
- 인덱스 클러스터링 팩터가 좋지 않은 상태는 인덱스 레코드 정렬 순화 테이블 레코드 정렬 순서가 전혀 일치 하지 않는 것
어렵죠? ㅎㅎ
중요한 것은 인덱스는 Single Block 단위로 랜덤하게 읽고, 풀스캔은 Multi Block 단위로 순차적으로 읽습니다.
클러스터링 팩터란 관계된 데이터가 모여있는 것을 이야기 하는데 아래 그림을 참고하시면 도움이 될 듯 합니다.
오라클에서는 데이터를 블록단위로 읽기 때문에 관계된 데이터가 여러 블록으로 떨어져 있게 되면, 위 그림처럼 여러 블록을 읽어야 하는 경우가 발생해서 I/O 횟수가 증가하게 됩니다.
즉, 성능이 확 떨어지게 됩니다.
인덱스로 설명을 하자면,
SELECT 할 데이터가 Sequential 하게 모여있지 않아 여러 블록으로 분산될 경우, Sigle Block 단위로 여러 번의 I/O가 발생하면서 성능의 저하를 가져오게 됩니다.
또 아래 내용도 관련있습니다.
■ Single Block V.S. Multi Block
- Single Block I/O. 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 방법
. 인덱스를 통한 Table Acess일 경우 인덱스와 테이블 모두 이 방식을 사용
. Singl Block I/O 방식으로 읽은 블록들은 LRU 리스트 상 MRU쪽 end로 연결되므로 한번 적재되면 버퍼 캐시에 비교적 오래 머뭄
- Multi Block I/O
. Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 방법
. Extent 범위 단위에서 읽는다. db_file_muliblock_read_count 파라미터에 의해 결정
. Multiblock I/O 방식으로 읽은 블록들은 LRU 리스트에서 LRU쪽 end로 연결되므로 적재되고 얼마 지나지 않아 버퍼캐시에서 밀려남
※ 대량의 데이터를 Multiblock I/O 방식으로 읽을 때 Single Block I/O 보다 성능상 유리한 것은 I/O Call 발생 횟수를 그만큼 줄여주기 때문
. Index Range Scan 뿐 아니라 Index Full Scan시에도 논리적인 순서에 따라 Single Block I/O방식으로 읽음
. Index Fast Full Scan은 Multiblock I/O 방식을 사용
→ Oracle 10g부터는 Index Range Scan 또는 Index Full Scan 일 때도 Multiblock I/O 방식으로 읽는 경우가 있는데, 위처럼 테이블 액세스 없이 인덱스만 읽고 처리할 때
아무래도 글로 적혀있으면 이해가 잘 안됩니다.
그래서 준비한 아래 그림이 있습니다.
바로 감이 오시죠?
멀티블록단위로 읽어들이는 경우는 한번에 많은 양을 가져와서 처리하기 때문에 I/O 횟수가 많이 줄어듭니다.
반대로 싱글블록단위로 읽어들이는 경우에는 훨씬 많은 I/O 횟수로 처리를 해야합니다.
적절한 비유인지 모르겠지만, 포크레인을 연상해보시면 될 듯 합니다.
포크레인의 포크의 사이즈가 크면 한번에 많은 양의 흙을 퍼서 트럭으로 실을 수 있죠?
반대로 작은 사이즈의 포크라면 적은 양을 여러번 퍼야 원하는 모래의 양을 실을수 있습니다.
즉, 퍼온 모래의 양은 동일하지만 여러번 퍼야하므로 시간이 오래 소요됩니다.
기본적인 개념을 잡기위해 내용을 작성해보았습니다. 도움이 되셨을라나 모르겠네요.
튜닝을 하기위해서는 보다 더 상세한 내역을 들여다 보아야 하지만, 기본적인 위 개념만 가지고 있으면 상세한 튜닝을 하실 때 도움이 될 듯합니다.