친절한 SQL 튜닝 정리 (1장)

1장 SQL 처리 과정과 I/O

구조적, 집합적, 선언적 질의 언어

  - 오라클 PL/SQL, SQL Server T-SQL처럼 절차적 프로그래밍 기능을 구현할 수 있는 확장 언어도 제공하지만 구조적, 집합적, 선언적 질의 언어

 

SQL 최적화

  - SQL 파싱(파싱 트리 생성, Syntax 체크, Semantic 체크) → SQL 최적화 → 로우 소스 생성

 

옵티마이저 힌트

  - 힌트 안에 인자를 나열할 땐 '.'(콤마)를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안 된다

  - 테이블을 지정할 때 스키마명까지 명시하면 안 된다 (SELECT /*+ FULL(SCOTT.EMP) */ FROM EMP)

  - FROM 절 테이블 명 앞에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다

 

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

  - Interrupt 없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면 상태에서 I/O가 완료되기를 기다린다. 정해진 OS 함수를 호출(I/O)하고 CPU를 반환한 채 알람을 설정하고 대기 큐(Wait Queue)에서 잠을 자는 것이다. 열심히 일해야 할 프로세스가 한가하게 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수밖에 없다.

→ I/O 하는 동안 프로세스 상태에 대해 인지하지 못하고 있었음

 

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

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

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

  - 테이블은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다. 익스텐트의 첫 번째 블록 주소 값을 가진다. 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 곧 Full Table Scan이다.

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

→ 테이블 Full Scan을 위해 세그먼트 헤더에 맵으로 익스텐트 목록(첫 번쨰 블록 주소 값)을 관리함을 새로 알게 됨

 

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

  - 디스크 I/O가 SQL 성능을 결정한다.

  - 라이브러리캐시 : SQL과 실행계획, DB 저장형 함수/프로시저 캐싱하는 '코드 캐시'

  - DB버퍼캐시 : 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call 줄이는 '데이터 캐시'

  - SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O

 

버퍼캐시 히트율 (BCHR : Buffer Cache Hit Ratio)

  - ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) x 100

  - ( ( 논리적 I/O - 물리적 I/O ) / 논리적 I/O) x 100

  - ( 1 - ( 물리적 I/O ) / (논리적 I/O ) ) x 100

  - 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I./O가 아닌 논리적 I/O를 줄여야 한다는 사실이다. 

  - SQL 성능을 높이기 위해서 할 수 있는 일은 논리적 I/O를 줄이는 일뿐이다.

  - BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 해서 효율적인 SQL을 의미하지 않는다는 사실이다. 같은 블록을 비효율적으로 반복해서 읽으면 BCHR이 높아진다.

 

Single Block I/O vs Multiblock I/O

  - Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식

  - Single Block I/O 예시 : 인덱스 루트 블록을 읽을 때, 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때, 인덱스 브랜치 블록에서 얻은 정보로 리프 블록을 읽을 때, 인덱스 리프 블록에서 얻은 주소로 테이블 블록을 읽을 때)

  - Multiblock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식. 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call을 할 때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능 (보통 1MB 단위로 I/O 수행)

  - Multiblock I/O 방식으로 읽더라도 익스텐트 경계를 넘지 못한다

 

Table Full Scan vs Index Range Scan

  - Table Full Scan : 테이블 전체를 스캔해서 읽은 방식. Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 '한 번의 수면(I/O Call)을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O 하는 메커니즘'이다.

  - Index Range Scan : 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보. 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스 이용

 

캐시 탐색 메커니즘

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

  - 인덱스 루트 블록을 읽을 때

  - 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때

  - 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때

  - 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

  - 테이블 블록을 Full Scan 할 때

  - 캐시버퍼 체인 뿐만 아니라 버퍼블록 자체에도 직렬화 메커니즘이 존재한다. 바로 '버퍼 Lock'이다. 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일 량(논리적 I/O) 자체를 줄여야 한다.