[SQL튜닝/인덱스와 조인] 테이블 Random Access 부하 (RowID의 구조, Buffer Pinning, Clustering Factor)

🖥️ 들어가며

📌 RowID의 구조: DBA(오 + 파 + 블) + 로우 번호
📌 Buffer Pinning
📌 Clustering Factor

---

✏️ 1. RowID의 구조

• 데이터 오브젝트 번호(16자리)
• 데이터 파일 번호(3자리)
• 블록 번호(6자리)
• 로우 번호(3자리)
• DBA(Data Block Address, 데이터 블록 어드레스)
  • 오 + 파 + 블
  • 특정 데이터의 물리적 위치를 나타냅니다.
  • 데이터 접근 과정
    • 1) 디스크 주소를 찾을 때, 먼저 메모리를 검색합니다.*
    • 2) 메모리에 없으면 디스크를 검색합니다.
    • 3) 찾은 데이터를 다시 메모리에 로드합니다.
  • *Hash Bucket
    • Hash function을 사용하여 메모리에서 데이터를 빠르게 찾을 수 있습니다.
    • 다른 입력값이라도 같은 해시 결과를 가질 수 있습니다. (해시 충돌, Hash Collision)
      (ex: 나머지를 구하는 것과 비슷한 원리, 1%3=1, 4%3=1)
• RowID 구조
  • DBA(오 + 파 + 블) + 로우 번호(로케이션) 
  • 이 구조를 통해 Oracle은 효율적으로 특정 데이터 행을 찾을 수 있습니다.

 

✏️ 2. Cache Buffer Chain Latch → Hash Bucket → Buffer Header → Buffer Block

⭐⭐⭐ Cache Buffer Chain Latch, DBA, Hash Function, SGA, Buffer Pinning

• 데이터 접근 프로세스
  • 1. Cache Buffer Chain Latch* 획득
  • 2. Hash Bucket 검색(Hash Function(DBA*) 사용)
  • 3. Buffer Header 확인
  • 4. Buffer Block 접근
• Cache Buffer Chain Latch
  • Latch 획득의 부하: Cache Buffer Chain Latch
  • 일반적으로 1Latch는 32개의 Hash Bucket 그룹을 관리합니다.
  • spin 횟수를 기준으로 여러 번 시도하고 횟수 내 실패 시 프로세스가 대기(sleep) 상태로 전환됩니다.(V$session wait 발생)
    • LRU 알고리즘에 의해 메모리에서 Age Out이 되었을 경우, LRU Latch 획득 필요
  • 다시 획득하면 Buffer Block을 찾아가고, 누군가 사용하고 있으면 Buffer Busy Wait이 발생됩니다.
    • Buffer Block 대기: Buffer Busy Wait
• DBA
  • 오 + 파 + 블
  • RDB에서는 DBA로 찾는 게 가장 빠릅니다. 하지만 SGA는 경합이 있으므로 Cache Buffer Chain Latch에서 획득이 필요합니다. (경합 방지, 동기화)

• Hash Function
  
  • DBA 입력 → Hash Function을 사용 → Hash Bucket을 결정합니다.
  • Hash Bucket은 Buffer Header 목록(=Hash Chain)을 참조, 각 Buffer Header는 특정 Buffer Block을 가리킵니다.
• SGA
  • lock: 큐 관리, 선입선출
  • latch: 선점 방식 (큐 관리 -> 오히려 오버헤드 발생)
  • mutex: latch보다 가벼운 lock
• Buffer Pinning
  • Logical Read count로 잡히지 않습니다 == 블록을 읽은 횟수로 체크되지 않습니다 == SQL 튜닝 Good
  • 다음 번 Read 시 현재 읽은 동일 Block을 Read할 경우 대상 Block이 Age Out되지 않도록 Pin을 걸어둡니다.
  • DBA를 통해 메모리 번지를 PGA에 저장해 빠르게 접급합니다.
  • 대기 없이 블록을 바로 참조할 수 있어 효율적입니다.(핀을 풀기 전까진 그대로 버퍼 피닝이 유지됩니다.)

 

✏️ 3. Clustering Factor

• 인덱스 key순으로 데이터가 정렬된 정도입니다.
  • 인덱스 오픈
  • 변수 선언
  • 인덱스를 순차적으로 읽어 이전 RowID 블록과 다음 RowID 블록이 상이할 때 +1 증가합니다.

C.F가 좋을 때(수치가 낮을 경우)	C.F가 나쁠 때(수치가 높을 경우)
블록 수에 근접 (좋을수록 인덱스 키 순서가 데이터 물리적 저장 순서와 유사하여 동일한 블록에서 여러 로우를 효율적으로 읽을 수 있음)	로우 수에 근접 (하나의 로우를 읽을 때마다 다른 블록을 접근해야 할 수 있)
랜덤 액세스 효율이 좋음	랜덤 엑세스 효율이 매우 나쁨
Buffer Pinning 효과로 인해 Random I/O (Logical Read) 미 발생

• 비용 계산 방법
  • 비용(Cost)
  • = blevel -- 인덱스 수직적 탐색 비용
    + [리프 블록 수 * 유효 인덱스 선택도] -- 인덱스 수평적 탐색 비용
    + [클러스터링 팩터 * 유효 테이블 선택도] -- 테이블 Random Access 비용
  • blevel
    : 리프 블록에 도달하기 전까지 읽게 될 루트 및 브랜치 블록 개수
  • 유효 인덱스 선택도
    : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
  • 유효 테이블 선택도
    : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)
• Table Full Scan과 Index Scan의 손익 분기점
  • 통상적으로 찾고자 하는 레코드가 전체 용량 대비 10%라고 하나, 항상 그렇지는 않습니다.
  • 정확히 말하면 C.F에 의해 좌우됩니다.
  • C.F 개선 방법
    • SQL Server: Clustered Index
    • Oracle의 Clustered Index, IOT가 존재합니다.

---

📒 정리하면

1. RowID 구조: 데이터 오브젝트 번호 + 파일 번호 + 블록 번호 + 로우 번호
   DBA(Data Block Address): 오 + 파 + 블
   DBA와 로우 번호를 통해, 특정 데이트의 물리적 위치를 나타냅니다.
2. 데이터 접근 프로세스: Cache Buffer Chain Latch 획득 → Hash Bucket 검색 → Buffer Header 확인 → Buffer Block 접근의 순서로 진행되며, Buffer Pinning을 통해 효율적인 데이터 접근이 가능합니다.
3. Clustering Factor(C.F): 인덱스 키 순서대로 데이터가 정렬된 정도를 나타내며, CF가 낮을수록 랜덤 액세스 효율이 좋고 Buffer Pinning 효과가 높습니다.
4. 비용 = blevel + [리프 블록 수 * 유효 인덱스 선택도] + [클러스터링 팩터 * 유효 테이블 선택도]
   이를 통해 쿼리 실행 계획을 최적화합니다.
5. CF 개선 방법: SQL Server의 Clustered Index, Oracle의 IOT를 사용하여 개선할 수 있습니다.

---

• 참고자료
  • http://www.gurubee.net/lecture/3259