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

1. 인덱스 레인지 스캔 vs 풀스캔

풀스캔은 항상 느리다? 무조건 쿼리는 인덱스를 타야한다? -> NO
"인덱스 레인지 스캔은 큰 테이블에서 소량의 데이터를 읽을때 효율적이다. "

2. SQL이 느리다면 십중팔구 I/O 때문

디스크 I/O 때문
디스크 I/O = SLEEP
디스크 I/O가 발생하는동안 프로세스는 wait 상태로 빠진다.

3. 데이터베이스 저장 구조

테이블스페이스 : 세그먼트를 담는 콘테이너, 여러개의 데이터 파일로 구성
세그먼트 : 테이블, 인덱스 처럼 데이터 저장공간이 필요한 오브젝트
익스텐트 : 공간을 확장하는 단위
블록 : 사용자가 입력한 레코드를 실제로 저장하는 공간, 페이지

• 익스텐트 내 블록은 연속적인 공간
• 익스텐트 끼리는 연속된 공간이 아니다.
• 테이블 세그먼트 헤더에는 각 익스텐트의 첫번째 블록 주소가 있다.

4. 블록단위 I/O

블록단위 I/O : 오라클은 기본적으로 8KB 크기의 블록을 사용
테이블 뿐만아니라 인덱스도 블록단위로 읽고 쓴다.

시퀀셜 액세스 VS 랜덤 액세스

시퀀셜 엑세스 : 논리적, 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
랜덤 엑세스 : 레코드 하나를 읽기 위해 한 블록씩 접근 하는 방식

테이블 풀스캔 : 시퀀셜 엑세스
인덱스 리프노드 에서도 앞뒤로 : 시퀀셜 엑세스
인덱스에서 실제 테이블 조회 : 랜덤 액세스

논리적 I/O VS 물리적 I/O

논리적 블록 I/O : 캐시 상관없이 실제 읽으려고 한 블록 수
물리적 블록 I/O : 캐시 상황도 고려되서 실제 발생한 총 블록 I/O

쿼리튜닝에서 논리적 I/O를 집중해야한다.
-> 물리적 I/O는 캐시 상황 등에 따라 그때 그때 달라질 수 있기 때문

Q. 쿼리실행시 적정 블록수는 얼마인가?
A. 쿼리 마다 다르다, 검색범위, 조인, 테이블 크기, 인덱스 구조 등에 따라 다름

싱글 블록 I/O VS 멀티 블록 I/O

싱글 블록 I/O : 한번에 하나의 블록
멀티 블록 I/O : 한번에 여러 블록

MySQL 리드 어헤드 : 풀 테이블 스캔시 한번에 여러 블록을 백그라운드 스레드에서 미리 읽어놓음
https://m.blog.naver.com/sqlmvp/221652490923

캐시

SQL 최적화 과정 캐싱 : 하드파싱 / 소프트파싱

소프트 파싱 : 쿼리 자체를 하나의 텍스트로 캐싱해서 해당 쿼리로 실행계획을 세우고 내부 프로시저를 생성하는 과정 자체를 재사용

하드 파싱 : 재사용 못하고 쿼리 실행 시 실행계획 세우고 내부 프로시저 생성까지 해야하는 경우

실행계획을 세우고 프로시저를 생성하는 과정은 CPU 바운드 작업 -> CPU 사용량이 높다면 과도한 하드파싱

자주 사용하는 쿼리인데 쿼리 텍스트가 매번 달라지면 소프트 파싱을 못함 -> 매번 하드파싱 -> CPU 사용량이 올라감 -> 그렇다면 무조건 바인드 변수를 사용하는게 좋을까?

System Global Area(SGA) : 여러 프로세스가 공통으로 엑세스 하는 메모리 공간
Library Cache : SQL 텍스트 자체를 캐싱 -> 소프트 파싱에 사용

데이터 자체를 캐싱 : 버퍼 캐시

SGA 내 DB Buffer Cache에 데이터 자체를 캐싱

버퍼캐시 히트율 BCHR
= ( 캐시에서 읽은 블록 수 / 읽은 총 블록 수 ) x 100
= ( (논리적 I/O - 물리적 I/O) / 논리적 I/O ) x 100

Q. BCHR가 높으면 무조건 효율적인 SQL 일까?
효율적인 SQL = 딱 필요한 블록 수 만큼만 I/O를 수행하는 SQL

한번 읽은 블록을 반복적으로 다시 읽는 쿼리 -> BCHR 는 높겠지만 -> 과연 효율적인 쿼리 일까?

딱 필요한 블록 수 만큼만 I/O를 수행하는 SQL을 작성하기는 어렵다 = 경험의 영역
그래서 이렇게 캐싱을 먼저 고려하게 되는것일 수도...

쿼리가 느려? -> 인덱스 안타는거 아니야? -> 인덱스 있는데도 느리네 ??? -> 그냥 캐싱해두면 되잖아
이렇게 말이다...

1장 정리

인덱스 레인지 스캔이 풀스캔보다 더 느릴 수 있다.
어차피 대부분의 모든 블록 읽을거라면 멀티 블록 I/O 활용 해서 풀스캔으로 빠르게 처리하는게 나을수도.

오라클에서는 실행계획을 세우는거 자체를 캐싱하는구나

실행계획은 말 그대로 계획이지 그렇게 실행했다는게 아니구나

쿼리 최적화는 논리적으로 얼마나 블록을 읽을지를 줄여나가는 거구나 : 딱 필요한 만큼만 읽으면 best

스터디를 진행하면서 나눈 이야기

1. 책에서는 SQL이 느린 이유가 I/O 때문이고 I/O 작업시 프로세스가 waiting 상태로 빠지기 때문이라고 했는데, 이 디스크 I/O 자체가 느리기 때문이 아닐까? 책에서는 Sleep 이라는것에 더 중점을 두어 설명하고 있는것 같다.

2. SQL을 한번 두번 세번 이렇게 반복적으로 실행할때마다 쿼리 속도는 빨라질것이라고 설명되어있는데 같은 쿼리라도 반복해서 실행하는것이 캐시상황에 도움이 될까? -> 이 부분은 일반적으로 쿼리를 실행하면 캐시상황이 좋아지기 때문에 히트율이 높아진다는 의미로 받아들이면 될것 같다.