[친절한 SQL 튜닝] 데이터 저장 구조 및 I/O 메커니즘

01. SQL이 느린 이유?

• 디스크 I/O, 디스크(ex 하드디스크)에서 읽기 또는 쓰기 요청 처리하는 시간의 백분율
• 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 waiting 상태에서 I/O가 완료되기를 대기
• I/O 작업이 많으면, 성능이 많은 영향을 준다.

 

02. 기본 단위, 블록

• 데이터 베이스에서 데이터를 읽고 쓰는 단위 = 블록(Block) (오라클 8KB)
• 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
• 인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

03. 테이블  블록을 액세스 하는 방식

시퀀스 엑세스

• 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
• ex) 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식

랜덤 엑세스

• 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

 

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

논리적 I/O

• SQL을 처리하는 과정에서 발생한 총 블록 I/O
• SQL이 참조하는 테이블에 데이터를 입력하거나 삭제하지 않는 상황에서 조건절에 같은 변수 값을 입력하면, 아무리 여러 번 실행해도 매번 읽는 블록 수는 같다.
• 논리적 I/O 횟수는 일반적으로 *DB 버퍼캐시에서 블록을 읽는 횟수와 일치

* DB 버퍼 캐시 : 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이기 때문에, SGA(System Global Area)의 일부로서 데이터 파일에서 읽은 데이터 블록 복사본을 보관한다. (메모리 공간)

 

물리적 I/O

• SQL을 처리하는 과정에서 디스크에서 발생한 총 블록 I/O
• SQL 처리 도중 읽어야 할 블록을 버퍼 캐시에서 찾지 못할 때만 디스크를 액세스 하므로 논리적 블록 I/O 중 일부를 물리적 I/O라고 한다.
• 버퍼 캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O를 물리적 I/O

물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하기 위해서는 물리적 I/O가 아닌 논리적 I/O을 줄여야 한다. 논리적 I/O는 어떻게 줄일까? SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다. 논리적 I/O는 항상 일정하게 발생하지만, SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생 변수이다. 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

 

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

I/O call 할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다. 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식을 "Single Block I/O", 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식을 "Multiblock I/O"라고 한다.

 

Single Block I/O

• 인덱스를 이용할 때 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용
• 인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다.
  • 인덱스 루트
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

 

Multiblock I/O 

• 많은 데이터 블록을 읽을 때는 Multiblock I/O 방식이 효율적이다.
• 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용한다. 테이블이 크면 클수록 더 적합하다.
• 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O call을 할 때 디스크 상에 그 블록과 인접한 블록들을 한 번에 읽어 캐시에 미리 적재

읽고자 하는 블록을 DB 버퍼 캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O call 한다. 그동안 프로세스는 대기 큐에서 잠을 잔다. 대용량 테이블이면 수많은 블록을 디스크에서 읽는 동안 여러 차례 잠을 잘 텐데, 기왕이 잠을 자려면 한꺼번에 많은 양을 요청해야 잠자는 횟수를 줄이고 성능을 높일 수 있다. 대용량 테이블을 Full Scan 할 때 Multiblock I/O 단위를 크게 설정하면 성능이 좋아진다.

 

 

06. Table Full Scan vs Index Range Scan

Table Full Scan

• 테이블 전체를 스캔해서 읽는 방식, 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식
• 한 번의 I/O로 여러 블록을 읽어 온다. (Multiblock I/O)
• 옵티마이저가 Table Full Scan을 선택하는 경우
  • 적용 가능한 인덱스가 없을 경우
  • 넓은 범위의 데이터 액세스
  • 소량의 테이블 액세스
  • 병렬처리 액세스
  • FULL 힌트를 적용한 경우
• Table Full Scan = 시퀀셜 액세스 + Multiblock I/O 방식
• 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 한번 Sleep을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O 하는 메커니즘, 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.

 

Index Range Scan

• 인덱스를 이용해서 읽는 방식, 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
• 큰 테이블에서 소량의 데이터를 검색할 때는 반드시 인덱스를 사용
• 랜덤 엑세스 + Single block I/O
• 캐시에서 블록을 찾지 못하면 레코드 하나를 읽기 위해 매번 Sleep을 해야 하는 I/O 메커니즘이다. 스토리지 성능이 좋아지더라도 성능이 좋아지지 못할 수 있다.
• 한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램은 인덱스를 적용하면 더 성능이 떨어지는 경우 존재
• 한 블록에 평균 500개 레코드가 있으면, 같은 블록을 최대 500번 읽을 수 있다. 만약 인덱스를 이용해 전체 레코드를 액세스 한다면, 모든 블록을 평균 500번씩 읽게 되는 셈이다. 각 블록을 단 한 번에 읽는 Table Full Scan 보다 훨씬 불리하다.

항상 인덱스만 사용하는 것이 옳지 않고, Table Full Scan이 항상 나쁜 건 아니다. 모든 성능 문제를 인덱스로 해결하려고 해서는 안된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.

 

 

직렬화 메커니즘

• SGA의 버퍼 캐시는 공유 자원
• 하나의 버퍼 블록에 두 개 이상의 프로세스가 동시에 접근하려고 할 때 발생하며, 블록 정합성에 문제가 발생할 수 있다,
• 따라서 한 프로세스씩 순차적으로 접근하도록 구현해야 한다. = 직렬화 메커니즘
• 캐시 버퍼 체인뿐만 아니라 버퍼 블록 자체에도 직렬화 메커니즘이 존재한다. = 버퍼락
• 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일 량 (논리적 I/O) 자체를 줄여야 한다.

 

---

 

책 읽고 정리하는 과정
1. '친절한 SQL 튜닝' 책을 정독하며, 중요한 부분 바로바로 타이핑
2. 한 챕터가 끝나면, 타이핑한 부분을 복습하며 관련 구글링 및 재정리

 

1.3.1 SQL이 느린이유?

• 디스크 I/O
• 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 waiting 상태에서 I/O가 완료되기를 기다린다.
• I/O가 많으면 성능이 느려진다.

데이터 베이스에서 데이터를 읽고 쓰는 단위 = 블록 (오라클 8KB)

데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.

인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

테이블 또는 인덱스 블록을 엑세스 하는 방식으로는 시퀀셜 엑세스와 랜덤 엑세스가 있다.

시퀀셜 엑세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식

ex) 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식

 

랜덤 엑세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

 

DB 버퍼 캐시

: 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이다.

SGA의 라이브러리 캐시가 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라면, DB 버퍼 캐시는 '데이터 캐시' 라고 할 수 있다.

> Show SGA 

 

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

논리적 I/O : SQL을 처리하는 과정에서 발생한 총 블록 I/O를 말한다.

: SQL이 참조하는 테이블에 데이터를 입력하거나 삭제하지 않는 상황에서 족건절에 같은 변수 값을 입력하면, 아무리 여러 번 실행해도 매번 읽는 블록 수는 같다. SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O가 된다.

: 논리적 I/O 횟수는 일반적으로 DB 버퍼캐시에서 블록을 읽는 횟수와 일치한다.

 

물리적 I/O : 디스크에서 발생한 총 블록 I/O를 말한다. SQL 처리 도중 읽어야 할 블록을 버퍼 캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적 I/O 라고 한다.

: 버퍼 캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O를 물리적 I/O

 

블록 I/O 적정량은 검색 범위, 조인하는 테이블 개수, 대상 테이블 크기, 인덱스 구조 등에 의해 결정 된다.

디스크 I/O vs 메모리 I/O : 10,000배

 

애플리케이션 특성에 따라 다르지만, 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 버퍼캐시 히트율을 달성해야한다.

 

물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상시키기 위해서는 물리적 I/O가 아닌 논리적 I/O을 줄여야 한다.

논리적 I/O는 어떻게 줄일까? SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다. 논리적 I/O는 항상 일정하게 발생하지만, SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생 변수 이다. 논리적 I/O를 줄임으로서 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

Single Block I/O vs Multiblock I/O

메모리 캐시가 클수록 좋지만, 데이터를 모두 캐시에 적재할 수는 없다. 비용적 한계, 기술적 한계 때문에 전체 데이터 중 일부만 캐시에 적재해서 읽을 수 있다.

I/O call 할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다. 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식을 "Single Block I/O", 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식을 "Multiblock I/O" 라고 한다.

 

인덱스를 이용할 때 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용한다.

인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다.

• 인덱스 루트
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

 

많은 데이터 블록을 읽을 때는 Multiblock I/O 방식이 효율적이다. 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용한다. 테이블이 크면 클수록 더 적합하다

: 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O call을 할 때 디스크 상에 그 블록과 인접한 블록들을 한번에 읽어 캐시에 미리 적재하는 기능이다.

 

읽고자 하는 블록을 DB 버퍼 캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O call 한다. 그 동안 프로세스는 대기 큐에서 잠을 잔다. 대용량 테이블이면 수 많은 블록을 디스크에서 읽는 동안 여러 차례 잠을 잘 텐데, 기왕이 잠을 자려면 한꺼번에 많은 양을 요청해야 잠자는 횟수를 줄이고 성능을 높일 수 있다. 대용량 테이블을 Full Scan 할 때 Multiblock I/O 단위를 크게 설정하면 성능이 좋아진다.

 

Table Full Scan vs Index Range Scan

Table Full Scan : 테이블 전체를 스캔해서 읽는 방식, 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식

Index Range Scan : 인덱스를 이용해서 읽는 방식, 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식

한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램은 인덱스을 적용하면 더 성능이 떨어지는 경우가 있다. 조인을 포함한 SQL이면 조인 메소드로 해시 조인을 선택해주면 된다.

 

Table Full Scan = 시퀀셜 엑세스 + Multiblock I/O 방식

: 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 한번 수면을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O 하는 메커니즘, 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.

 

큰 테이블에서 소량의 데이터를 검색할 떄는 반드시 인덱스를 사용해야 한다.

Index Range Sacn = 랜덤 엑세스 + Single block I/O

: 캐시에서 블록을 찾지 못하면 레코드 하나를 읽기 위해 매번 잠을 자는 I/O 메커니즘이다. 많은 데이털르 읽을 때는 Table Full Scan 보다 성능이 적다. 열심히 일해야 할 프로세스가 잠을 자는데, 스토리지 성능이 좋아지면 뭐하겠는가

한 블록에 평균 500캐 레코드가 있으면, 같은 블록을 최대 500번 읽을 수 있다. 만약 인덱스를 이용해 전체 레코드를 엑세스 한다면, 모든 블록을 평균 500번씩 읽게 되는 셈이다. 각 블록을 단 한번에 읽는 Table Full Scan 보다 훨씬 불리하다.

 

항상 인덱스만 사용하는 것이 옳지 않고, Table Full Scan이 항상 나쁜 건 아니다. 모든 성능 문제를 인덱스로 해결할려고 해서는 안된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.

 

SGA의 버퍼 캐시는 공유 자원이다. 따라서 하나의 버퍼블록에 두 개 이상의 프로세스가 동시에 접근하려고 할 때 발생한다. 동 시에 접근하면 블록 정합성에 문제가 생길 수 있다. 따라서 한 프로세스씩 순차적으로 접근하도록 구현해야 함, 이를 위해 직렬화 매커니즘이 필요하다. 캐시 버퍼 체인 뿐만 아니라 버퍼 블록 자체에도 직렬화 메키니즘이 존재한다. 바로 '버퍼락'. 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량 (논리적 I/O) 자체를 줄여야 한다.