CH01.인덱스 원리와 활용 - 04. 테이블 Random 액세스 부하

이번에는 대량의 데이터를 처리할 때의 테이블 Random 액세스가 가장 큰 부하 요인으로 작용하는 원인을 자세히 설명하겠습니다.

 

(1) 인덱스 ROWID에 의한 테이블 액세스

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 '테이블 Random 액세스'가 반드시 일어나게 됩니다. 실행계획에서는 Table Access by index ROWID라고 표시됩니다.

물리적 주소? 논리적 주소?

인덱스에 저장돼 있는 rowid는 물리적 주소정보라고 일컬어지는데, 오브젝트 번호, 데이터파일 번호, 블록 번호 같은 물리적 요소들로 구성되어 있기 때문입니다. 하지만 보는 시각에 따라서는 논리적 주소정보라고 표현되기도 하는데 rowid가 물리적 위치정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문입니다.

일반 구조 테이블에서 사용되는 physical ROWID가 내용적으로 논리적 의미를 담고 있다는 뜻입니다.

 

메인 메모리 DB와의 비교

데이터를 모두 메모리에 로드해놓고 메모리를 통해서만 I/O를 수행하는 DB라고 할 수 있습니다. 

버퍼 캐시 히트율이 99%인 데이터베이스 vs 메인 메모리 DB와 비교 했을 때 메인 메모리 DB의 속도가 훨씬 빠릅니다. 특히 대량의 테이블을 인덱스를 통해 액세스할 때는 더 큰 차이가 납니다.

메인 메모리 DB의 경우 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼 캐시로 로딩하고 이어서 인덱스를 실시간으로 만듭니다. 이때 인덱스는 오라클처럼 디스크 상의 주소정보를 담는 게 아니라 메모리상의 주소정보 즉, 포인터를 담습니다.

포인터는 메모리상에서 데이터를 찾아가는 데 있어 가장 빠른방법이며 그 비용은 거의 0에 가깝습니다.

반면 오라클은 테이블 블록이 수시로 버퍼 캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 직접 포인터로 연결할 수 없는 구조입니다. 대신 디스크 상의 블록 위치 정보(DBA Data Block Address)를 해시 키 값으로 삼아 해싱 알고리즘을 통해 버퍼 블록을 찾습니다. 매번 위치가 달라지더라도 캐싱되는 해시 버킷만큼은 고정적이게 됩니다.

 

rowid는 우편주소에 해당

오라클의 rowid는 우편주소에 해당하고 메인 메모리DB의 포인터는 전화번호에 해당합니다. 전화번호는 물리적으로 연결되어 전화번호를 누르면 바로 상대방과 통화가 가능하지만 우편주소는 주소에 따라 찾아다니는 구조이므로 느립니다.

 

인덱스 rowid에 의한 테이블 액세스 구조

오라클도 포인터로 빠르게 액세스하는 버퍼 Pinning기법을 사용하지만 반복적으로 읽힐 가능성이 큰 블록에 대해서만 일부 적용하고 있습니다. 이 매커니즘의 도움을 받지 않은 일반적인 인덱스 rowid에 의한 테이블 액세스가 실제로 얼마나 고비용의 작업인지 그림과 함께 설명하겠습니다.

1.인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치정보)를 해시 함수에 적용해 해시 값을 확인합니다.

2. 각 해시 체인은 래치에 의해 보호되므로 해시 값이 가리키는 해시 체인에 대한 래치를 얻으려고 시도합니다.

(하나의 cache buufer chains 래치가 여러개 해시 체인을 동시에 관리합니다.)

3.다른 프로세스가 래치를 잡고 있으면 래치가 풀렸는지 확인하는 작업을 반복합니다.(기본은 2,000번)

4.그러고도 실패하면 CPU를 OS에 반환하고 대기이벤트를 뿌리며 대기 상태로 빠집니다.(latch : cache buffer chains)

5.정해진 시간동안 대기 상태에 있다가 깨어나서 다시 래치 상태를 확인합니다. ( 래치 획득 실패 시 다시 대기 상태로 갈 수도 있습니다.)

6.래치가 해제되었다면 래치를 획득하고 원하던 해시 체인으로 진입합니다.

7.거기서 데이터 블록이 찾아지면 래치를 해제하고 바로 읽으면 되는데, 앞서 해당 블록을 액세스한 프로세스가 일을 마지치 못해 버퍼 Lock을 쥔 상태면 다시 대기해야 합니다.(buffer busy waits)

8.블록 읽기를 마치고 나면 버퍼 Lock을 해제해야 하므로 다시 해시 체인 래치를 얻으려고 시도합니다. 이때 또 경합이 발생할 수 있습니다.

 

해시 체인을 스캔했는데 데이터 블록을 버퍼캐시에서 찾지 못했을 때는 디스크 I/O까지 발생되므로 더 비용이 발생하게 됩니다.

정리하면, 인덱스 rowid는 테이블 레코드와 물리적으로 연결돼 있지 않기 때문에 인덱스를 통한 테이블 액세스는 생각보다 고비용 구조이며 설령 모든 데이터가 메모리에 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA를 해싱하고 래치 획득 과정을 반복해야 하기 때문에 동시 액세스가 심할 때는 래치와 버퍼 Lock에 대한 경합까지 발생하게 됩니다.

 

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

군집성 계수(=데이터가 모여있는 정도) 

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미합니다. 클러스터링 팩터가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋은데 비슷한 조건의 값들이 물리적으로 모여있다면 데이터를 찾는 속도가 빨라지는 원리입니다.

왼쪽은 클러스터링 팩터가 좋은 상태의 그림으로 인덱스 레코드 정렬순서와 테이블 레코드 정렬 순서가 100%일치하는 경우입니다.

오른쪽은 반대로 서로의 정렬순서가 맞지않는 상태입니다.

 

클러스터링 팩터 조회

조회는 통계정보를 생성하고 나면 오라클이 계산한 인덱스 CF뷰에서 확인할 수 있습니다.

 

통계정보 수집

exec dbms_stats.gather_table_stats(user,'T'); 

 

클러스터링 팩터 확인

select i.index_name,t.blocks table_block ,i.clustering_factor from user_indexes i ,user_tables t

where t.table_name = i.table_name;

 

테이블 블록(table_block) 수치와 근접할수록 데이터가 잘 정렬돼 있음을 의미합니다.

 

클러스터링 팩터와 물리적 I/O

클러스터링 팩터가 좋으면 물리적인 디스크 I/O 횟수를 감소시키는 효과가 있습니다. 오라클에서는 블록 단위 I/O가 이루어지므로 인덱스를 통해 하나의 레코드를 읽으면 같은 블록에 속한 다른 레코드들도 함게 캐싱되는 경과를 가져올 것이고, 클러스터링 팩터가좋은 인덱스면 그 레코드들도 가까운 시점에 읽힐 가능성이 높습니다. 반대의 경우에는 더 많은 블록을 읽어야 하므로 물리적인 디스크 I/O 횟수도 증가하게 됩니다.

 

클러스터링 팩터와 논리적 I/O

인덱스 클러스터링 팩터는 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수를 의미합니다. 따라서 클러스터링 팩터가 가장 좋을 때는 인덱스 통계에 나타나는 clustering_factor가 전체 테이블 블록 개수와 일치하고 가장 안 좋을 때는 총 레코드 개수와 일치하게 될 것입니다.

인덱스 통계에서 볼 수 있는 clustering_factor는 인덱스를 통해 테이블을 액세스할 때 예상되는 논리적 I/O개수를 더 정확히 표현하고 있습니다. 

 

버퍼 Pinning에 의한 논리적 I/O 감소 원리

똑같은 개수의 레코드를 읽는데 인덱스 클러스터링 팩터에따라 논리적인 블록 I/O개수가 차이가나는 이유는 인덱스를 통해 액세스되는 하나의 테이블 버퍼 블록을 Pinning하기 때문입니다.

방금 액세스한 버퍼에 대한 Pin을 즉가 해제하지 않고 데이터베이스 Call 내에서 계속 유지하는 기능이 버퍼 Pinning입니다. 따라서 연속된 인덱스 레코드가 같은 블록을 가리킨다면 래치 획득 과정을 생략하고 버퍼를 Pin한 상태에서 읽기 때문에 논리적인 블록 읽기(Logical Reads)횟수가 증가하지 않습니다.

실선은 버퍼 Pinning된 상태를 나타내는데 이렇게 되면 김유신~김두환까지 레코드를 읽을 때는 논리적 블록 I/O를 읽으키지 않고 읽을 수 있게되는 것입니다.  따라서 12건의 레코드를 읽는데 두번의 블록 I/O만 발생하게 되는 것입니다.

 

 

(3)인덱스 손익분기점

Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 손익분기점이라고 부릅니다.

-인덱스 rowid에 의한 테이블 액세스는 random액세스인 반면, Full Table Scan은 Sequential액세스 방식으로 이루어집니다.

-디스크 I/O시 인덱스 rowid에 의한 테이블 액세스는 Single Block Read방식인 반면, Full Table Scan은 Multiblock Read방식입니다.

따라서 클러스터링 팩터가 나쁘면 손익분기점이 5%미만에서 결정되고 좋으면 90%까지 올라갈 수도 있습니다.

테이블 reorg함으로써 클러스터링 팩터를 좋게 만든다면 인덱스 손익분기점이 높아져 효용성이 증가할 수 있지만 최후의 수단이으로 생각하고 일상적인 튜닝기법으로 남용하지는 않아야 합니다.

 

손익분기점을 극복하기 위한 기능들

1.IOT(Index-Organized Table) 활용

테이블을 인덱스 구조로 생성하는 것입니다. 테이블 자체가 인덱스 구조이므로 항상 정렬딘 상태를 유지합니다.

2.클러스터 테이블 활용

키 값이 같은 레코드는 같은 블록에 모이도록 저장하기 때문에 클러스터 인덱스를 이용할 때는 테이블 Random 액세스가 키 값별로 한 번씩만 발생하고 클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문엔 넓은 범위를 읽더라도 비효율이 없습니다.

3.파티셔닝

대량 범위 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝한다면 Full Table Scan하더라도 일부 파티션만 읽고 멈추도록 할 수 있습니다.

클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 놓지만 파티셔닝은 세그먼트 단위로 모아줍니다.