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

01. Index Rowid에 의한 Table Access

---

컬럼이 Index에 포함되는 경우가 아니라면 Index Scan 이후 "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 Rowid를 통해 Memory에서 Block읽는 방법

 

Index가 존재할 때

Index Leaf Block에 존재하는 엔트리의 Rowid마다 Hash Function을 적용해

결과값(해쉬 값)을 반환 받게 됩니다. 

 

이 결과값 중 비슷한 결과값끼리 묶이게 되는데

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 Function을 통한 결과가 되겠습니다.

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

 

 

 

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

 

● Index에서 하나의 Rowid를 읽고 Rowid를 Hash 함수에 적용해 그 Hash 값을 확인합니다.

● Buffer Header의 네모칸들 사이에는 Hash Chain Latch에 의해 보호되므로 원하는 Hash 값을 찾으러 갈때까지 Hash Chain에 대한 Latch 얻으려고 시도한다. (위로 예를 들면 내가 31이란 값을 얻으려고한다면 1에서 11가는 Latch 확보 11->21 가는 Latch를 확보) -> CACHE_BUFFER_CHAIN_LATCH

 

● 만약 다른 프로세스가 해당 Latch를 잡고 있으면 Latch가 풀렸는지 확인하는 작업을 일정횟수만큼 반복한다.

● 그러고도 실패하면 OS에 반환하고 잠시 대기 상태로 빠지는데 이때(Latch Free) 대기 이벤트가 나타난다.

● 정해진 시간 동안 잠을 자다가 다시 Latch 상태를 확인하고, 계속해서 Latch가 풀리지 않았다면 다시 대기

    상태로 빠질 수도 있다.

● Latch가 해제되었다면 Latch를 재빨리 획득하고 원하던 Hash Chain으로 진입한다.

● 거기서 데이터 블록이 찾아지면 Latch를 해제하고 바로 읽으면 되는데, 앞서 해당 Block을 액세스한

    프로세스가 아직 일을 마치치 못해 버퍼 Lock을 쥔 상태라면 또다시 대기해야한다.

    이때 대기는 (Buffer Busy Waits) 입니다.

 

● Block을 읽고 버퍼 Lock을 해제해야하므로 다시 Hash Chain Latch를 얻으려고 시도합니다.

    이때 또다시 경합이 발생할 수 있습니다.

 

 

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

Disk에서 Block읽어 Memory에 올리기

 

 

Index Rowid는 Table 레코드와 물리적으로 연결돼 있지 않기 때문에 Index를 통한

Table Access는 생각보다 고비용 구조라는걸 상기시키기 위해서입니다.

 

설령 모든 데이터가 Memory에 캐싱돼 있더라도

Table Record를 찾기위해선

 

1) 매번 DBA를 Hashing 하고

2) Latch 획득 과정을 반복하며

3) 동시 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 예시

Clustering Factor

• 왼쪽은 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 수직적 탐색 비용
      (Leaf Block 수 * 유효 Index 선택도)     +  -- Index 수평적 탐색 비용
      (Clustering Factor * 유효 Table 선택도)    -- Table Random Access 비용

• bLevel : Leaf Block에 도달하기 전까지 읽게 될 Root Block 및 Branch Block 개수
• 유효 Index 선택도 : 전체 Index Record 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
• 유효 Table 선택도 : 전체 Record 중에서 Index 스캔을 완료하고서 최종적으로 Table을 방문할 것으로 예상되는 비율(%)

 

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

• C.F가 좋다는 말은 궁극적으로 물리적인 Disk I/O횟수를 감소시크는 효과가 있습니다.
• 이유
  
  • 하나의 Block을 읽을 때 그 내에 같은 값을 가진 Record를 캐싱할 확률이 높기 때문입니다.
  • 결국 여러 Block을 거치지 않고 보다 적은 Block 수에서 찾는 다는 점

 

※ 인덱스 손익분기점

• 통상적으로 찾고자 하는 레코드가 전체 용량 대비 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에 의해 크게 달라진다.

 

기능

1. IOT(Index-Organized Table)
   • 테이블을 인덱스 구조로 생성
   • 인덱스 리프 블록이 곧 데이터 블록 -> 인덱스를 수직 탐색 후 테이블 레코드를 읽기 위한 Random Access 불필요
2. Clustered Table
   
   • 키 값이 같은 레코드는 같은 블록에 모이도록 저장하여, 클러스터 인덱스를 이용할 때는 테이블 Random Access가 키 값 별로 한 번 씩만 발생
3. 파티셔닝
   
   • 읽고자 하는 데이터가 많을 때는 인덱스를 이용하지 않는 편이 낫다고 하지만, 수천만 건을 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가 생각만큼 빠르지 않다는 점 입니다.  

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