Chap01. 04 테이블 Random 액세스 부하

Tech_Wave 2021. 7. 21. 19:22

01. 인덱스 Rowid에 의한 테이블 액세스

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 "Table Random Access" 가 발생 한다.

SQL> SELECT * FROM 고객 WHERE 지역 = '서울';

Execution Plan
-------------------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS
1   0      TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
2   1        INDEX (RANGE SCAN) OF '고객_지역_IDX' (INDEX)

특징

실행계획 상 Table Access By Index ROWID라는 표기 확인
INDEX에 저장되어있는 ROWID는 물리적 주소정보(데이터 오브젝트 번호 + 데이터 파일 번호 + 블록번호 + 로우번호)

Index의 Rowid를 통해 Memory(DB_BUFFER_CACHE)에서 Table을 어떻게 읽어 들이는지 확인해보겠습니다.

Index가 존재할 때 Index Leaf Block에 존재하는 엔트리의 Rowid마다 Hash 함수를 적용해 결과값(해쉬 값)을 반환 받게 됩니다.
이 결과값 중 비슷한 결과값끼리 묶이게 되는데 Buffer Header영역의 Hash Bucket에 묶이게 됩니다.

예를 들어볼까요?

1 -> 1, 11, 21, 31, 41 ....

2 -> 2, 12, 22, 32, 42 ....

3 -> 3, 13, 23, 33, 43 ....

4 -> 4, 14, 24, 34, 44 .....

.....

여기서 1,2,3,4는 Hash Bucket이 되고

나머지 값들은 Rowid들이 Hash 함수를 통한 결과가 되겠습니다.

==================================================

그럼 Index Rowid를 통한 Table Access하는 순서를 보도록 하겠습니다.

Index에서 하나의 Rowid를 읽고 DBA(Data Block Address, 디스크 상의 블록 위치 정보)를 Hash 함수에 적용해 그 Hash 값을 확인합니다.
Buffer Header의 네모칸들 사이에는 Hash Chain Latch에 의해 보호되므로 원하는 Hash 값을 찾으러 갈때까지 Hash Chain에 대한 Latch 얻으려고 시도한다.
- 예를 들면 내가 31이란 값을 얻으려고 한다면 1에서 11가는 Latch 확보 11->21 가는 Latch를 확보 -> CACHE_BUFFER_CHAIN_LATCH
다른 프로세스가 해당 Latch를 잡고 있으면 Latch가 풀렸는지 확인하는 작업을 일정횟수만큼 반복한다.
그러고도 실패하면 CPU를 OS에 반환하고 잠시 대기 상태로 빠지는데 이때(Latch Free) 대기 이벤트가 나타난다.
정해진 시간 동안 잠을 자다가 다시 Latch 상태를 확인하고, 계속해서 Latch가 풀리지 않았다면 다시 대기상태로 빠질 수도 있다.
Latch가 해제되었다면 Latch를 재빨리 획득하고 원하던 Hash Chain으로 진입한다.
거기서 데이터 블록이 찾아지면 Latch를 해제하고 바로 읽으면 되는데, 앞서 해당 Block을 액세스한 프로세스가 아직 일을 마치치 못해 버퍼 Lock을 쥔 상태라면 또다시 대기해야 한다. 이때 나타나는 대기 이벤트가 (Buffer Busy Waits) 입니다.
블록 읽기를 마치고 나면 버퍼 Lock을 해제해야하므로 다시 Hash Chain Latch를 얻으려고 시도합니다. 이때 또다시 경합이 발생할 수 있습니다.

Hash Chain을 스캔했는데 데이터 블록을 찾지 못했을 때 즉,

Memory에서 못찾아 Disk에서 가져와야 할 경우 더욱 복잡해집니다.

디스크로부터 블록을 퍼 올리려면 우선 Free 버퍼를 할당 받아야 하므로 LRU 리스트를 스캔한다. 이를 위해서는 cache buffers lru chain 래치를 얻어야 하는데, 래치 경합이 심할 때는 여기서도 latch free 이벤트가 나타날 수 있다.
LRU 리스트를 정해진 임계치만큼 스캔했는데도 Free 상태의 버퍼를 찾지 못하면 DBWR에게 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보해 달라는 신호를 보낸다. 그런 후 해당 작업이 끝날 때까지 잠시 대기 상태에 빠지는데, 이때 나타나는 대기 이벤트가 free buffer waits 이다.
Free 버퍼를 할당 받은 후에는 I/O 서브시스템에 I/O 요청을 하고 다시 대기 상태에 빠지는데, 이때 db file sequential read 대기 이벤트가 나타난다.
마지막으로, 읽은 블록을 LRU 리스트 상에서 위치를 옮겨야 하기 때문에 다시 cache buffers lru chain 래치를 얻어야 하는데, 이 또한 원활하지 못할 때는 latch free 이벤트가 나타난다.

위의 내용들은 오라클에서 하나의 레코드를 찾아가는 데 있어 가장 빠르다고 알려진 rowid에 의한 테이블 액세스가

Index Rowid는 Table 레코드와 물리적으로 연결돼 있지 않기 때문에 (Index를 통한 Table Access 과정이) 생각보다 고비용 구조라는걸 상기시키기 위해서입니다.

설령 모든 데이터가 Memory에 캐싱돼 있더라도 Table Record를 찾기위해선

매번 DBA를 Hashing 하고
Latch 획득 과정을 반복하며
동시 Access가 심할 때는 Latch와 Buffer Lock에 대한 경합까지 발생
- (첫번째그림)많은 Latch들이 발생할 가능성이 높다.

Index를 통한 Data 얻는 과정이 빠르다고 하였지만 내부적으로 이 만큼 많은 일이 발생하게 됩니다.

	Sequential Access	Random Access
1	하나의 블록에서 순차적을 읽는다.	주로 하나의 블록에서 하나의 레코드만 읽는다.
2	알차게 빼 먹는다.	효율이 낮다 / 높은비용
3	Index Leaf Block 읽을 때 / Full Scan 할 때	Rowid 이용 테이블 액세스
4	적은비용	DBA를 이용한 인덱스 수직적 탐색
5	Full Scan일 경우 Multi-Block I/O 가능	클러스터링팩터(C.F)가 낮을 때 높은 성능
6	-	Single Block I/O

결론 : 실행계획 상 "Table Access By Index ROWID" 가 발생한다면, 위에 작성한 수많은 과정과 부하들이 발생하는 것을 항상 인지하고 있으면 좋을꺼 같습니다.

02. 인덱스 클러스터링 팩터

클러스터링 팩터(Clustering Factor, CF)
- 군집성 계수(=데이터가 모여 있는 정도)
- 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미

C.F 예시

왼쪽은 DEPTNO의 10과 20이 서로 한 뭉텅이로 저장된 것을 확인 -> Clustering Factor가 좋다.
오른쪽은 DEPTNO의 10과 20이 서로 띄엄 띄엄 저장된 것을 확인 -> Clustering Factor가 나쁘다.

Index를 통해 Table Access 시 C.F가 좋은 경우와 나쁜 경우의 그림

Index Leaf Block의 순서가 Table Record 순서와 일치 시 C.F가 가장 좋습니다.

	C.F 좋을 때(수치가 낮을 경우)	C.F 나쁠 때(수치가 높을 경우)
1	블록 수에 근접	로우 수에 근접
2	랜덤 액세스 효율이 좋다.	랜덤 액세스 효율이 매우 나쁘다.
3	버퍼 Pinning 효과로 인해 Random I/O (Logical Read) 미 발생

※ Index를 이용한 Table Access 비용 계산과정(출제된 문제)

비용 = bLevel                               +     -- Index 수직적 탐색 비용
      (리프 블록 수 * 유효 인덱스 선택도)     +     -- Index 수평적 탐색 비용
      (클러스터링 팩터 * 유효 테이블 선택도)         -- Table Random Access 비용

bLevel : 리프 블록에 도달하기 전까지 읽게 될 브랜치 블록 개수
유효 Index 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
유효 Table 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)

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

C.F가 좋다는 말
- 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것을 말합니다.
- 궁극적으로 물리적인 Disk I/O횟수를 감소시크는 효과가 있습니다.
이유
- 하나의 블록을 읽을 때 그 내에 같은 값을 가진 레코드를 캐싱할 확률이 높기 때문입니다.
- 결국 여러 블록을 거치지 않고 보다 적은 블록 수에서 찾는 다는 점

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

인덱스 C.F는 단적으로 말해, 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수를 의미합니다.
C.F가 가장 좋을 때는 인덱스 통계에 나타나는 Clustering_factor가 전체 테이블 블록 개수와 일치하고,
가장 안 좋을 때는 총 레코드 개수와 일치합니다.

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

※ 인덱스 손익분기점

통상적으로 찾고자 하는 레코드가 전체 용량 대비 10%라 하나, 항상 그렇지는 않음
정확히 말해, C.F에 의해 좌우
C.F를 극복하기 위해 손익분기점을 극복하기 위한 기능 사용
- SQL Server -> Clustered Index
- Oracle -> Clustered Index, IOT

특징

인덱스 ROWID에 의한 테이블 액세스는 Random Access인 반면, Table Full Scan은 Sequential 액세스 방식으로 이뤄진다.
DISK I/O시, 인덱스 ROWID에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Table Full Scan은 Multiblock Read 방식을 사용합니다.
위 요인에의해 인덱스 손익분기점은 일반적으로 5% ~ 20%의 낮은 수준에서 결정되며, C.F에 의해 크게 달라진다.

기능

IOT(Index-Organized Table)
- 테이블을 인덱스 구조로 생성
- 인덱스 리프 블록이 곧 데이터 블록 -> 인덱스를 수직 탐색 후 테이블 레코드를 읽기 위한 Random Access 불필요
Clustered Table
- 키 값이 같은 레코드는 같은 블록에 모이도록 저장하여, 클러스터 인덱스를 이용할 때는 테이블 Random Access가 키 값 별로 한 번 씩만 발생
파티셔닝
- 읽고자 하는 데이터가 많을 때는 인덱스를 이용하지 않는 편이 낫다고 하지만, 수천만 건을 Table Full Scan 할 경우 난감하며, 파티셔닝으로 일부 파이션만 읽고 멈출 수 있습니다.

==================================================

RDBMS VS Memory DB

Memory DB는 Disk없이 Memory에서만 Data를 올려놓고 사용하는 DB입니다.

Oracle은 Memory영역(DB_BUFFER_CACHE)과 Disk영역이 존재하면서

자주 사용하는 Data는 Memory영역에 올려두면서 Memory에 없는 Data는 Disk에서

Block을 읽어다 사용합니다.

만약에 읽는 Data가 Memory 상에 모두 존재한다면(버퍼캐시히트율이 높다면)

Memory DB와 성능은 같지 않을까?

답은 그래도 "Memory DB가 더 빠르다" 입니다.

Memory DB에서는 Instance 가동 시 Disk에 저장된 데이터를 버퍼 캐시로 로딩하고

이어서 Index를 실시간으로 만들게 되는데 이때 주소는 Disk상의 주소가 아닌

메모리상의 주소정보로서 Pointer를 생성하게 됩니다.

때문에 Memory DB가 더 빠른 것이며

Rowid에 의한 Table Access가 생각만큼 빠르지 않다는 점 입니다.

====================================================