I/O 튜닝이 곧 SQL 튜닝이다
SQL이 느린 이유는 디스크 I/O때문이다
I/O = 잠 (SLEEP)
OS 또는 I/O 서브 시스템이 I/O를 처리하는 동안 프로레스는 잠을 자기 때문
- 프로세스 : 실행중인 프로그램
- 프로세스 생명주기 : new → ( ready → running → waiting )반복 → terminated
- 실행중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태 ( Runnable Queue )로 전환했다가 다시 실행 상태로 전환한다.
- 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 생명주기가 필요하다
- interrupt 없이 열심히 일하던 프로세도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면(Waiting) 상태에서 I/O가 완료되기를 기다린다. 정해진 OS함수를 호출(I/O Call)하고 CPU를 반환한 채 알람을 설정하고 대기 큐(Wait Queue)에서 잠을 자는것이다.
⇒ I/O가 많으면 성능이 느릴수 밖에 없다.
데이터베이스 저장 구조
- 데이터 파일 | 디스크 상의 물리적인 OS파일
- 테이블 스페이스 | 세그먼트를 담는 콘테이너, 여러개의 데이터파일로 구성
- 세그먼트 | 데이터 저장공간이 필요한 오브젝트( 테이블, 인덱스, 파티션, LOB 등 )
- 여러개의 익스텐트로 구성
- 테이블,인덱스를 생성할 때 데이터를 어떤 테이블 스페이스에 저장할지를 지정한다.
- 파티션 구조가 아니라면, 테이블/인덱스는 각각 하나의 세그먼트이다.
- 파티션 구조라면 각 파티션이 하나의 세그먼트가 된다
- 익스텐트 | 공간을 확장하는 단위, 연록된 블록들의 집합
- 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로 부터 익스텐트를 추가로 할당 받는다,
- 블록 | 데이터를 읽고 쓰는 단위
- 사용자가 입력한 레코드를 실제로 저장하는 공간
- 페이지 라고도 부름 ( DB2, SQL Server )
- 한 블록은 한 테이블이 독점한다 → 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.
DBA ( Data Block Address )
데이터 블록 자신만의 고유 주소값 - 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타낸다.
인덱스를 이용해 테이블 레코드를 읽을 대는 인덱스 ROWID를 이용해야하며
ROWID 는 DBA + 로우번호(블록 내 순번, 로케이션)으로 구성되어 있다.
블록 단위 O/I
DBMS가 데이터를 읽고 쓰는 단위 (데이터 I/O단위) : 블록
→ 테이블, 인덱스에서 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽음
시퀀셜 엑세스 vs 랜덤 엑세스
테이블 또는 인덱스 블록을 액세스 하는 방식 2가지
시퀀셜 엑세스
-
논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
-
인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있으므로, 이 주소값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 액세스이다.
-
테이블의 경우 서로 논리적인 연결고리를 가지고 있지 않다
⇒ 세그먼트에 할당된 인트텐트 목록을 세그먼트 헤더에 맵(map)으로 관리한다,
익스텐트 맵은 각 익스텐스트이 첫 번째 블록 주소값을 갖는다
읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫번째 블록뒤에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 곧 Full Table Scan이다.
논리적 I/O vs 물리적 I/O
DB 버퍼 캐시
- 데이터 캐시, 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 불록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다
- 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다
논리적 I/O vs 물리적 I/O
- 논리적 블록 I/O : SQL을 수행하면서 읽은 총 블록 I/O
- SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말한다.
- 물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O
- SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스 하므로 논리적 블록 I/O중 일부를 물리적으로 I/O한다.
- 디스크 I/O가 메모리 I/O 보다 ,10000배쯤 느리다.
버퍼캐시 히트율 ( BCHR )
읽은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾는 비율
BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) x 100
= ( (논리적I/O - 물리적I/O) / 논리적I/O ) x 100
= ( 1 - (물리적I/O) / (논리적 I/O) ) x 100
🔸 물리적 I/O = 논리적 I/O x ( 100 - BCHR )
- SQL 성능을 높이기 위해서 할 수 있는 일은 논리적 I/O를 줄이는 일뿐이다.
- ⭐ 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL튜닝이다.
- BCHR이 높다고 해서 무조건 효율적인 SQL을 의미하지 않는다
- 같은 블록을 비효율적으로 반복해서 읽는 경우
- 같은 블록을 비효율적으로 반복해서 읽는 경우
Single Block I/O vs Multi Block I/O
Single Block I/O
- 한 번에 한 블록씩 요청해서 메모리에 적재하는 방법
- 인덱스를 이용항 때는 기본적으로는 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용
Multi Block I/O
- 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방법
- 인덱스를 이용하지 않고 테이블을 전체 스캔할 때 사용
- 테이블이 클수록 Multi Block I/O 단위도 크면 좋다
- 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할 때 디스크 상에 그 블록과 인접한 블록들을 한번에 읽어 캐시에 미리 적재하는 기능
- 한번에 담는 양 : db_file_multiblock_read_count ( 보통 128 )
Table Full Sacn vs Table Range Scan
테이블 전체를 스캔하는 방식 vs 인덱스를 이용한 테이블 액세스
- 큰테이블에서 소량 데이터를 검색할 때는 인덱스가 유리
- 읽을 데이터가 일정량을 넘으면 Table Full Sacn이 유리
캐시 탐색 메커니즘
