1.3 데이터 저장 구조 및 I/O 메커니즘

I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다.

1.3.1 SQL이 느린 이유

SQL이 느린 이유는 디스크 I/O 때문이다.
프로세스는 실행 중인 프로그램이며, 생명주기를 갖는다.

실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태(Runnable Queue)로 전환했다가 다시 실행 상태로 전환한다. 프로세스가 일하고 있을 때 디스크 I/O가 발생하면 CPU를 OS에 반환하고 잠시 수면(Wating) 상태에서 I/O가 완료되길 기다린다. 결국, 수 많은 프로세스에 의해 발생한 디스크 I/O 때문에 경합이 심해지고 그만큼 대기 시간도 늘어난다.

---

1.3.2 데이터베이스 저장 구조

• 테이블스페이스 : 세그먼트를 담는 콘테이너, 여러 개의 데이터파일(디스크 상의 물리적인 OS파일)로 구성된다.
• 세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트. 여러 익스텐트로 구성된다.
• 익스텐트 : 공간을 확장하는 단위. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다.(세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 가능성이 높다. 파일 경합을 줄이기 위해 DBMS가 분산 저장하기 때문이다.)
• 데이터파일 : 디스크 상의 물리적인 OS 파일

익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다.

---

1.3.3 블록단위 I/O

블록은 DBMS가 데이터를 읽고 쓰는 단위다. 이 때문에 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.

---

1.3.4 시퀀셜 엑세스 vs 랜덤 액세스

• 시퀀셜 액세스
  논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다. 테이블 블록은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 '맵(map)'으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다. 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, Full Table Scan이다.
• 랜덤 액세스
  논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.

---

1.3.5 논리적 I/O vs 물리적 I/O

DB 버퍼캐시

2절(https://gyujingyujin.tistory.com/12?category=1262044) 에서 공유메모리 SGA(System Global Area)의 구성요소 중 '라이브러리 캐시'를 살펴 봤었는데 데이터를 캐싱하는 DB 버퍼캐시도 SGA의 가장 중요한 구성요소 중 하나다.

• 라이브러리캐시 : SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'.
• DB 버퍼캐시 : 디스크에서 읽은 데이터 블록을 캐싱하는 '데이터 캐시'. 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다.

논리적 I/O vs 물리적 I/O

• 논리적 블록 I/O
  SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O. Direct Path I/O가 작동하는 경우가 있으므로 논리적 I/O와 메모리 I/O가 정확히 같은 의미는 아니지만, 일반적으로 같다고 생각해도 무방하다. 전기적 신호.
• 물리적 블록 I/O
  디스크에서 발생한 총 블록 I/O. SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적으로 I/O 한다. 액세스 암(Arm)을 통해 물리적 작용이 일어나므로 메모리 I/O에 비해 10,000배쯤 느리다. (디스크 액세스 -> 버퍼캐시 적재 -> 논리적 블록 I/O)

버퍼캐시 히트율

BCHR(Buffer Cache Hit Ratio) 구하는 공식은 아래와 같다.

BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) * 100
     = ( (논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
     = ( 1 - (물리적 I/O) / (논리적 I/O) ) * 100

물리적 I/O는 통제 불가능한 외생변수이므로 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

---

1.3.6 Single Block I/O vs Multiblock I/O

• Single Block I/O
  한 번에 한 블록씩 요청해서 메모리에 적재하는 방식. 인덱스와 테이블 블록을 읽을 때.
• Multiblock I/O
  한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식. 많은 데이터 블록을 읽을 때 효율적. 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 사용.프로세스가 잠자는 횟수를 줄여준다. 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call을 할때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능이다. 인접한 블록이란 같은 익스텐트에 속한 블록을 의미한다.

---

1.3.7 Table Full Scan vs Index Range Scan

• Table Full Scan
  테이블에 속한 블록 '전체'를 읽어서 찾는 방식. '시쿼셜엑세스' 와 'Multiblock I/O' 방식으로 디스크 블록을 읽는다. 큰 테이블에서 소량 데이터를 검색할 때는 비효율 적이므로 이 때는 반드시 인덱스를 이용해야한다.
• Indec Range Scan
  '랜덤액세스' 와 'Single Block I/O' 방식으로 디스크 블록을 읽는다. 캐시에서 블록을 못 찾으면 '레코드 하나를 읽기 위해 매번 잠을 자는 I/O 메커니즘'이다. 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다. 읽었던 블록을 반복해서 읽는 비효율이 있다.

---

1.3.8 캐시 탐색 메커니즘

Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.

버퍼캐시에서 블록을 찾을 때 이처럼 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스하는 방식을 사용한다. 해시 구조의 특징은 다음과 같다.

• 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨.
• 다른 입력 값이 동일한 해시 체인(=버킷)에 연결될 수 있음.
• 해시 체인 내에서는 정렬이 보장되지 않음.

메모리 공유자원에 대한 액세스 직렬화

버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다. 따라서 동시에 접근하면 블록 정합성 문제가 생길 수 있기 때문에 직렬화(serialization) 메커니즘이 필요하다.(줄세우기) 특정 순간에는 한 프로세스만 사용할 수 있다. 그 순간 다른 프로세스는 줄 서서 기다려야 한다. 이 줄세우기가 가능하도록 지원하는 메커니즘이 래치(Latch) 다.
해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 일이 생기면 곤란하다. 이를 막기 위해 해시 체인 래치가 존재한다. 해시체인래치를 흭득한 프로세스만이 체인으로 진입할 수 있다.
SGA를 구성하는 서브 캐시마다 별도의 래치가 존재하는데, 버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다.
캐시버퍼 체인뿐만 아니라 버퍼블록 자체에도 직렬화 메커니즘이 존재한다. 바로 '버퍼Lock' 이다. 이런 직렬화 메커니즘에 의한 캐시 경함을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 한다. 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더이 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제를 해결한다.