[친절한 SQL 튜닝] 1장 SQL 처리 과정과 I/O

1.1 SQL 파싱과 최적화

SQL 

• SQL은 'Structured Query Language'의 줄임말
• 원하는 결과집함을 구조적, 집합적으로 선언하는 질의 언어

SQL 옵티마이저

• 원하는 결과 집합을 만들어내는 프로시저를 만들어 내는 DBMS 내부 엔진
• 가장 효율적으로 수행할 수 이쓴 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진

SQL 최적화

• DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정

SQL 최적화 과정

1. SQL 파싱 : SQL 파서가 사용자가 입력한 SQL을 파싱
2. SQL 최적화 : 옵티마이저가 다양한 실행 경로를 생성해 가장 효율적인 실행경로 선택
3. 로우 소스 생성 : 로우소스생성기가 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅

SQL 파싱 과정

1. 파싱 트리 생성
2. Syntax 체크 : 문법적 오류가 있는지 확인. ex) 사용가능한 키워드인지, 순서가 올바른지
3. Semantic 체크 : 의미상 오류가 있는지 확인. ex) 존재하지 않는 컬럼 사용, 권한 체크

옵티마이저의 최적화 단계

1. 다양한 실행 계획 생성
2. 데이터 딕셔너리의 오브젝트 통계 및 시스템 통계정보를 사용해 각 실행계획의 예상비용 산정
3. 비용이 가장 적게 드는 실행 계획 선택

실행 계획

• SQL 옵티마이저가 생성한 처리절차를 트리구조로 표현한 것. 
• 테이블을 스캔하는지, 인덱스를 스캔하는지, 어떤 인덱스를 스캔하는지 등 확인 가능

옵티마이저 힌트

• 보통 옵티마이저가 최적의 경로를 선택하지만, 옵티마이저 힌트를 사용해 임의로 경로를 지정할 수 있다. 

옵티마이저 힌트 사용법

1. 아래와 같이 주석 기호에 '+'를 붙인다. 

SELECT /*+ INDEX(테이블 PK명) */

 

2. 힌트 안에 인자를 나열할 땐 콤마를 사용하지만, 힌트와 힌트 사이에는 사용하지 않는다

/*+ INDEX(테이블 PK명) INDEX(테이블, PK명) */ -> 모두 유효
/*+ INDEX(테이블), FULL(테이블) */ -> 첫번째 힌트만 유효

 

3. 테이블을 지정할 때 스키마명을 명시하면 안된다

4. FROM절에서 테이블의 별칭을 지정했다면 힌트에도 반드시 테이블의 별칭을 사용한다. 

자주 사용하는 옵티마이저 힌트 목록

분류	힌트	설명
최적화 목표	ALL_ROWS	전체 처리속도 최적화
	FIRST_ROWS(N)	최초 N건 응답속도 최적화
액세스 방식	FULL	Table Full Scan으로 유도
	INDEX	Index Scan으로 유도
	INDEX_DESC	Index를 역순으로 스캔하도록 유도
	INDEX_FFS	Index Fast Full Scan으로 유도
	INDEX_SS	Index Skip Scan으로 유도
조인 순서	ORDERED	FROM절에 나열된 순서대로 조인
	LEADING	LEADING 힌트 괄호에 기술한 순서대로 조인
	SWAP_JOIN_INPUTS	해시조인시, BUILD_INPUT을 명시적으로 선택
조인 방식	USE_NL	NL 조인으로 유도
	USE_MERGE	소프트 머지 조인으로 유도
	USE_HASH	해시 조인으로 유도
	NL_SJ	NL 세미조인으로 유도
	MERGE_SJ	소트 머지 세미조인으로 유도
	HASH_SJ	해시 세미조인으로 유도
서브쿼리 팩토링	MATERIALIZE	WITH문으로 정의한 집합을 물리적으로 생성하도록 유도
	INLINE	WITH문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도
쿼리 변환	MERGE	뷰 머징 유도
	NO_MERGE	뷰 머징 방지
	UNNEST	서브쿼리 Unnesting 유도
	NO_UNNEST	서브쿼리 Unnesting 방지
	PUSH_PRED	조인조건 Pushdown 유도
	NO_PUSH_PRED	조인조건 Pushdown 방지
	USE_CONCAT	OR 또는 IN-List 조건을 OR-Expansion으로 유도
	NO_EXPAND	OR 또는 IN-List 조건을 OR_Expansion 방지
병렬처리	PARALLEL	테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도
	PARELLEL_INDEX	인덱스 스캔을 병렬방식으로 처리하도록 유도
	PQ_DISTRIBUTE	병렬 수행시 데이터 분배방식 결정
기타	APPEND	Direct-Path Insert로 유도
	DRIVING_SITE	DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정
	PUSH_SUBQ	서브쿼리를 가급적 빨리 필터링하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도

---

1.2 SQL 공유 및 재사용

라이브러리 캐시

• 하드파싱을 통해 생성한 내부 프로시저를 반복 재 사용할 수 있도록 캐싱해두는 메모리 공간
• SGA의 구성요소

SGA

• 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어구조를 캐싱하는 메모리 공간

소프트파싱 vs 하드파싱

• 소프트파싱 : 라이브러리 캐시에서 SQL을 찾아 바로 실행단계로 넘어가는 것
• 하드 파싱 : 라리브러리 캐시에서 SQL을 찾지 못해 SQL 최적화 과정을 모두 거치는 것
• 하드파싱은 CPU 자원을 많이 소비함

SQL 최적화 과정은 왜 힘든가?

• 테이블 조인 순서의 경우의 수 = (조인할 테이블 수)!
• 조인 방식, 테이블 스캔 방식, 인덱스 스캔 방식, 어떤 인덱스를 선택할 것인가? ...

최적화 과정에서 SQL 옵티마이저가 사용하는 정보

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 포함)
• CPU 통계, Single Block I/O 속도, Multiblock I/O 속도
• 옵티마이저 관련 파라미터

이름없는 SQL의 문제

• 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다. 
• 하지만 SQL은 이름이 없다. 전체 SQL 텍스트 자체가 이름이다. 
• 한글자라도 달라지면 다른 SQL이기 때문에 다시 하드파싱하고 라이브러리 캐시에 저장해야 한다. 
• IBM DB2 같은 DBMS는 SQL을 프로시저처럼 영구 저장한다. 하지만 오라클, SQL Server 등의 DBMS는 그렇지 않다. 

바인드변수를 사용한 일회성 SQL 최적화

• 바인드 방식을 사용하여 파라미터 Driven 방식으로 SQL을 작성하면 변수의 값이 달라져도 라이브러리 캐시에는 하나의 SQL만 적재되기 때문에 하드파싱으로 인한 CPU 사용률을 감소시킬 수 있다. 

---

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

SQL이 느린 이유

• SQL이 느린 이유는 대부분 디스크 I/O때문이다. 
• I/O 튜닝이 곧 SQL 튜닝이다 

프로세스의 생명주기

I/O Call 속도

• Single Block I/O 기준 평균 10ms. (초당 100블록정도)
• SAN 스토리지는 4~8ms, SSD를 활용하는 스토리지는 1~2ms
• Single Block I/O 방식으로 10000블록을 읽으면, 가장 최신 스토리지에서도 10초 이상 기다려야 함

데이터베이스 저장 구조

테이블 스페이스	세그먼트를 담는 콘테이너 여러개의 데이터파일(디스크상의 물리적 OS 파일)로 구성
세그먼트	테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트 여러개의 익스텐트로 구성 파티션 구조가 아니라면 테이블도 하나의 세그먼트, 인덱스도 하나의 세그먼트 파티션 구조라면 각 파티션이 하나의 세그먼트 LOB 컬럼은 그 자체가 하나의 세그먼트, 자신이 속한 테이블과 다른 별도의 공간에 값을 저장한다.
익스텐트	공간을 확장하는 단위 연속된 여러개의 데이터 블록으로 구성 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당받음 한 익스텐트는 하나의 테이블이 독점. 즉, 한 익스텐트에 저장된 블록은 모두 같은 테이블 블록.  참고) MS-SQL Server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수 있음 파일 경합을 줄이기 위해 가능한 여러 데이터파일로 분산해서 저장 익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아님
블록	사용자가 입력한 레코드를 실제로 저장하는 공간 참고) DB2, SQL Server 등의 DBMS는 블록 대신 페이지라는 용어 사용 한 블록은 하나의 테이블이 독점. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드

 

데이터베이스 저장구조

데이터베이스 저장구조 ERD

 

익스텐트 목록 조회

오라클에서 세그먼트에 할당된 익스텐트 목록을 조회하는 방법이다. 

SELECT extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE owner = USERT
	AND segment_name = 'MY_SEGMENT'
ORDER BY extent_id

 

extent_id번 익스텐트는 file_id번 데이터파일의 block_id번 블록으로부터 연속된 blocks개 블록으로 이루어져있다는 뜻

디스크에 존재하는 데이터 접근법

• DBA(Daba Block Address)를 사용해 데이터 블록에 접근
• DBA : 디스크상에서 몇번째 데이터파일의 몇번째 데이터블록인지 나타내는 주소값
• 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID 를 사용해야함.
• ROWID : DBA + 로우번호(블록 내 순번)
• 테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵 이용. 
• 익스텐트 맵을 통해 각 익스텐트의 첫번째 블록 DBA를 알   수 있음 
• 익스텐트는 연속된 블록 집합이기 때문에 테이블을 스캔할때는 첫번째 블록 뒤에 연속해서 저장된 블록을 읽으면 됨. 

데이터베이스에서 데이터를 읽고 쓰는 단위

• 테이블과 인덱스에 블록 단위로 데이터를 읽고 쓴다
• 오라클은 기본적으로 8KB 크기의 블록 사용

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

시퀀셜 액세스	논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식 인덱스의 리프 블록은 앞뒤를 가리키는 주소값으로 서로 연결되어 있기 때문에 이 주소값으로 인덱스 시퀀셜 액세스 가능 테이블의 경우 세그먼트 헤더에 각 익스텐트의 첫번째 블록 주소값이 저장되어 있는데, 이 주소가 가리키는 블록과 연속된 블록을 읽으면 테이블 시퀀셜 액세스 가능
랜덤 액세스	레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

DB 버퍼 캐시

• 자주 사용하는 데이터(블록)은 DB 버퍼 캐시에 저장해놓는다. 
• 반복적인 I/O call을 줄일 수 있다

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

논리적 I/O	SQL을 처리하는 과정에서 발생한 총 블록 I/O 메모리 I/O + 디스크 I/O 논리적 I/O는 항상 동일하다.  논리적 I/O 횟수는 일반적으로 DB 버퍼 캐시에서 블록을 읽은 횟수와 일치한다. ???? 이해안됨
물리적 I/O	디스크에서 발생한 총 블록 I/O 메모리 I/O는 전기신호인데 디스크 I/O는 액세스 암을 통해 일어나므로 상대적으로 느리다.  물리적 I/O는 버퍼캐시에서 읽은 블록의 수에 따라 달라지기 때문에 실행할때마다 다르다.

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

• 읽은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율
• (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100
  ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
  (1 - 물리적 I/O / 논리적 I/O) * 100
• 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야함
• 물리적 I/O가 성능을 결정하지만 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 함
• BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 효율적인 SQL은 아님

Single Block I/O vs Multiblock I/O

Single Block I/O	한번에 한 블록씩 메모리에 적재하는 방식 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 모두 Single Block I/O 사용
Multiblock I/O	한번에 여러 블록씩 메모리에 적재하는 방식 테이블 전체 스캔할 때 Multiblock I/O 사용 테이블이 클수록 Multiblock I/O 단위도 크면 좋다 보통 OS의 I/O단위는 1MB이므로 최대 1MB만큼 Multiblock I/O할 수 있다.

Table Full Scan vs Index Range Scan

Table Full Scan	테이블 전체를 스캔해서 읽는 방식
Index Range Scan	인덱스를 이용해서 읽는 방식

• Index Range Scan이 항상 좋을까? 
• 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다. 
• 하지만 많은 데이터를 읽을 때는 Multiblock I/O를 사용하는  Table Full Scan을 사용하는 것이 더 효율적일 때가 많다
• 인덱스가 항상 옳은 것은 아니며, Table Full Scan이 항상 나쁜 것도 아니다. 
• 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐, 모든 성능 문제를 인덱스로 해결하려 해선 안된다. 
• 인덱스를 안타서 느린 경우보다 불필요하게 인덱스를 타서 느린 경우가 더 많다.

캐시 탐색 메커니즘

• Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다. 
• 구체적으로 아래 오퍼레이션은 모두 버퍼캐시 탐색 과정을 거친다.
  • 인덱스 루트 블록을 읽을 때
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
  • 테이블 블록을 Full Scan할 때
• 버퍼캐시는 해시 구조로 구성된다. 
• 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스한다. 

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

• 버퍼 캐시에 캐싱된 버퍼 블록은 모두 공유자원이다.
  공유자원에 동시에 접근하려하면 정합성 문제가 생길 수 있기 때문에 한 프로세스씩 순차적으로 접근하도록 구현하기 위해 직렬화 메커니즘이 필요하다. 

캐시버퍼 체인 래치

• 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 바꾸지 못하도록 현재 작업중인 체인에는 Key를 획득한 프로세스만 진입할 수 있다.