[친절한 SQL 튜닝] 1장 SQL 처리 과정과 I/O - 1.3 데이터 저장 구조 및 I/O 메커니즘

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

1.3.1 SQL이 느린 이유

• SQL이 느린 이유: 디스크 I/O 때문
• 프로세스는 I/O를 처리하는 동안 수면(대기) 상태가 됨.
• I/O Call 속도는 Single Block I/O 기준으로 평균 10ms (= 1초가 1000ms 이므로 초당 100블록을 읽을 수 있음)
  • Single Block I/O: 한번에 하나의 블록을 읽는 방식(기본적인 인덱스와 테이블 블록을 읽어들일 때 사용)
  • Multi Block I/O: 대량의 블록을 한번에 가져오는 방식(대용량 데이터를 Full Scan 할 때)
• I/O 튜닝이 안된 시스템이라면 프로세스가 많은 경우 디스크 경합이 일어나 더 많은 시간이 소요될 수 있음.
  

1.3.2 데이터베이스 저장 구조

• 큰 범위 순서대로: 테이블 스페이스 > 세그먼트 > 익스텐트 > 블록
  1.테이블스페이스: 세그먼트를 담는 콘테이너. 여러 개의 데이터 파일(디스크 상의 물리적은 OS 파일)로 구성.

2. 세그먼트: 테이블, 인덱스처럼 저장공간이 필요한 오브젝트
3. 익스텐트
   • 세그먼트는 여러 익스텐트로 구성됨.
   • 파티션 구조가 아닌 경우 테이블, 인덱스는 하나의 세그먼트.
   • LOB(Large Object 테이터 타입) 컬럼은 자체로 하나의 세그먼트로 구성.
   • 테이블, 인덱스에 데이터를 입력하다가 공간이 부족한 경우 익스텐트를 추가로 할당 받음. 익스텐트는 연속된 블록의 집합.
   • 그러나, 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있음. 파일 경합을 줄이기 위해 DBMS가 여러 데이터 파일로 분산. (다른 익스텐트끼리는 연속되지 않을 수 있음)
4. 데이터 블록: 사용자가 실제로 데이터를 저장하는 공간. 데이터를 읽고 쓰는 단위. 한 블록에는 동일한 테이블의 데이터들이 담겨 있음.

1.3.3 블록 단위 I/O

• 특정 레코드 하나만 읽고 싶어도 해당 블록을 통째로 읽음.
• 오라클은 한 블록에 8KB 크기. 1byte를 읽더라도 8KB를 통째로 읽어야 함.

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

1. 시퀀셜 액세스: 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼있음. 이 주소 값에 따라 순차적으로 스캔하는 방식.

   • 익스텐트 맵: 테이블 블록 간 서로 논리적인 연결고리가 없으므로 필요한 것. 오라클은 세그먼트 헤더에 익스텐트 목록을 맵(map)으로 관리. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소값을 가짐.
   • Full Table Scan: 익스텐트 목록을 맵에서 얻고 -> 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록의 순서를 읽는 것

2. 랜덤 액세스: 레코드 하나의 읽기 위해 한 블록씩 접근.

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

1. DB 버퍼캐시: 데이터 캐시. 라이브러리 캐시(실행계획, DB 저장형 함수/프로시저와 같은 코드 캐시)와 함께 SGA(System Global Area, 오라클 서버의 공유 메모리 영역)의 가장 중요한 구성요소 중 하나.

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

• 논리적 블록 I/O: SQL 문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O.
• 물리적 블록 I/O: 디스크에서 발생한 총 블록 I/O. SQL 처리 도중 읽어야할 블록을 버퍼캐시에서 찾지 못할 경우 디스크에 액세스함. 디스크 I/O는 물리적 작용이 일어나기 때문에 메모리 I/O에 비해 보통 10,000배 느림.

3. 버퍼캐시 히트율

• BCHR(Buffer Cache Hit Ratio)
  • 읽은 전체 블록 중 물리적 디스크 I/O를 수반하지 않고 메모리에서 찾은 비율.
  • OLTP성 애플리케이션의 경우 시스템 레벨에서 평균 99%의 히트율을 달성해야 함.
  • 물리적 I/O는 외생변수로 인해 결정되므로 SQL 튜닝으로 논리적 I/O를 줄여야 함.

1.3.6 Single Block I/O vs. Multiblock I/O

• Single Block I/O: 인덱스와 테이블 블록을 읽을 때 사용. 적은 데이터를 읽을 때 효율적
• Multiblock I/O: 인덱스를 이용하지 않고 대량의 테이블 스캔을 사용할 때 사용.
  • 일반적인 OS 레벨 I/O 단위: 1MB
  • 오라클 레벨 I/O 단위: 8KB
  • 오라클에서 db_file_multiblock_read_count 파라미터를 128로 설정하면 최대치가 됨. (8KB * 128 = 1MB)
  • 동일한 익스텐트에 속한 블록까지만 읽음.

1.3.7 Table Full Scan vs. Index Range Scan

• Table Full Scan: 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식
• Index Range Scan: 인덱스를 이용한 테이블 액세스 (소량의 데이터를 읽을 때 사용)

1.3.8 캐시 탐색 메커니즘

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

• 래치(latch)
  • 버퍼캐시는 공유자원.
  • 하나의 버퍼블록을 두 개 이상 프로세스가 동시에 접근하려고 할 때 블록 정합성에 문제가 생길 수 있음.
  • 한 프로세스씩 순차적으로 접근하도록 직렬화 메커니즘이 필요함. => 이것이 바로 래치.
• 래치에 의한 경합이 발생할 수 있음.
• 버퍼 락(Buffor Lock): 캐시버퍼, 버퍼블록에 존재하는 직렬화 메커니즘.