친절한 SQL 튜닝_1장.SQL 처리 과정과 I/O

 

SQL 처리 과정과 I/O

1.1 SQL파싱과 최적화

 

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

SQL은 기본적으로 구조적(structured)이고, 집합적(set-based)이고 선언적인(declarative) 질의 언어이다.

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 ‘SQL 최적화‘라고 한다.

 

1.1.2 SQL 최적화

SQL을 실행하기 전 최적화 과정

1.SQL 파싱

사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서(Parser)가 파싱을 진행한다.

파싱 트리 생성 : SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성

Syntax 체크 : 문법적 오류가 없는지 확인

Semantic 체크 : 의미상 오류가 없는지 확인

2.SQL 최적화

SQL 옵티마이저(Optimizer)는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다.

3.로우 소스 생성

SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계다. 로우 소스 생성기(Row-Source Generator)가 그 역할을 맡는다.

 

1.1.3 SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS의 핵심 엔진이다. 옵티마이저의 최적화 단계를 요약하면 아래와 같다.

1)사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획들을 찾아낸다.

2)데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.

3)최저 비용을 나타내는 실행계획을 선택한다.

 

1.1.4. 실행계획과 비용

DBMS에도 ‘SQL 실행경로 미리보기’ 기능이 있다. 실행계획(Execution Plan)이 바로 그것이다. SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 아래와 같이 트리 구조로 표현한 것이 실행계획이다.

미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

 

1.1.5 옵티마이저 힌트

옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다.

힌트 사용법은 아래와 같다. 주석 기호에 ‘+’ 를 붙이면 된다.

 

1.2 SQL 공유 및 재사용

1.2.1 소프트 파싱 vs. 하드 파싱

SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 ‘라이브러리 캐시(Library Cache)’라고 한다. 라이브러리 캐시는 SGA 구성요소다. SGA(System Global Area)는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.

사용자가 SQL 문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인하다. SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 ‘소프트 파싱(Soft Parsing)’이라 하고, 찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 ‘하드 파싱(Hard Parsing)’이라고 한다.

 

1.2.2 바인드 변수의 중요성

이름없는 SQL 문제

사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다. 컴파일한 상태로 딕셔너리에 저장되며, 사용자가 삭제하지 않는 한 영구적으로 보관된다. 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다.

반면, SQL은 이름이 따로 없다. 전체 SQL 텍스트가 이름 역할을 한다. 딕셔너리에 저장하지도 않는다. 처음 실행할 대 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 대 똑 같은 최적화 과정을 거쳐 캐시에 적재된다.

 

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

1.3.1 SQL이 느린 이유

SQL이 느린 이유는 십중팔구 I/O 때문이다. 구체적으로 말해, 디스크 I/O 때문이다.

프로세스(Process)는 ‘실행 중인 프로그램’이며, 생성(new) 이후 종료(terminated) 전까지 준비(ready)와 실행(running)과 대기(waiting) 상태를 반복한다. 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태(Runnable Queue)로 전환했다가 다시 실행 상태로 전환한다. 여러 프로세스가 하나의 CPU를 공유할 수 있지만. 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 이런 메커니즘이 필요하다. Interrupt 없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면(waiting) 상태에서 I/O가 완료되기를 기다린다. 정해진 OS 함수를 호출(I/O Call)하고 CPU를 반환한 채 알람을 설정하고 대기 큐(Wait Queue)에서 잠을 자는 것이다. 열심히 일해야 할 프로세스가 한가하게 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수밖에 없다.

 

1.3.2 데이터베이스 저장 구조

블록 : 데이터를 읽고 쓰는 단위

익스텐드 : 공간을 확장하는 단위, 연속된 블록 집합

세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)

테이블스페이스 : 세그먼트를 담는 콘테이너

데이터파일 : 디스크 상의 물리적인 OS파일

 

1.3.3 블록 단위 I/O

데이터베이스에서 데이터를 읽고 쓰는 단위는 무엇일까? 파일? 세그먼트? 익스텐트? 블록?

파일 단위(예를 들어, 2GB)로 매번 데이터를 읽고 쓰는 건 상상하기 어렵다. 테이블 세그먼트 단위(예를 들어, 100MB)도 마찬가지다. 익스텐트는 공간을 확장하는 단위라고 했다. 블록이 바로 DBMS가 데이터를 읽고 쓰는 단위다.

데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 심지어 1Byte짜리 컬럼 하나만 읽고 싶어도 블록을 통째로 읽는다.

테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

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

테이블 또는 인덱스 블록을 액세스하는(=읽는) 방식으로는 시퀀셜 액세스와 랜덤 액세스, 두가지가 있다.

첫째, 시퀀셜(Sequential) 액세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 

둘째, 랜덤(Random) 액세스는 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(=touch)하는 방식이다.

 

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

DB 버퍼캐시

앞서 2절에서 공유메모리 SGA 구성요소로서 ‘라이브러리 캐시’를 살펴봤는데, 데이터를 캐싱하는 ‘DB 버퍼캐시’도 SGA의 가장 중요한 구성요소 중 하나다. 라이브러리 캐시가 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 ‘코드 캐시’라고 한다면, DB 버퍼캐시는 ‘데이터 캐시’라고 할 수 있다. 디스크에서 어렵게 읽는 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다.

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

논리적 블록 I/O는 SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말한다.

물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다.

버퍼캐시 히트율

버퍼캐시 효율을 측정하는 데 전통적으로 가장 많이 사용해 온 지표는 버퍼캐시 히트율(Buffer Cache Hit Ratio, 이하 ‘BCHR’)이다. 구하는 공식은 아래와 같다.

BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) * 100

         = ( (논리적 I/O – 물리적 I/O) / 논리적 I/O) * 100

         = ( 1 – (물리적 I/O) / (논리적 I/O) ) * 100

BCHR 공식에서 우리는 중요한 성능 원리를 발견할 수 있다. 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다는 사실이다.

물리적 I/O = 논리적 I/O * (100 – BCHR)

논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

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

캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽는다. I/O Call 할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다. 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식을 ‘Single Block I/O’라고 한다. 여러 블록씩 요청해서 메모리에 적재하는 방식을 ‘Multiblock I/O’라고 한다.

인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용한다. 인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다. 반대로, 많은 데이터를 블록을 읽을 때는 Multiblock I/O 방식이 효율적이다. 그래서 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용한다.

 

1.3.7 Table Full Scan vs. Index Range Scan

테이블에 저장된 데이터를 읽는 방식은 두 가지다. 테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용해서 읽는 방식이다. 전자를 ‘Table Full Scan’이라고 부른다는 것은 주지의 사실이다. 후자는, 제목에 ‘Index Full Scan’이라고 간략히 표현했지만, 보통 ‘인덱스를 이용한 테이블 엑세스’라고 표현하다.

Table Full Scan은 말 그대로 테이블에 속한 블록 ‘전체’를 읽어서 사용자가 원하는 데이터를 찾는 방식이다. 인덱스를 이용한 테이블 액세스는 인덱스에서 ‘일정량’을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지 가리키는 위치 정보다.

 

1.3.8 캐시 탐색 메커니즘

버퍼캐시 탐색 메커니즘을 설명하기에 앞서 버퍼캐시 구조부터 살펴보자. DBMS는 버퍼캐시를 해시 주고로 관리한다.

버퍼캐시에서 블록을 찾을 때 이처럼 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터(Pointer)로 버퍼 블록을 액세스하는 방식을 사용한다. 해시 구조의 특징을 요약하면, 다음과 같다.

-

 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨

- 다른 입력 값(예를 들어, 4와 9)이 동일한 해시 체인(=버킷)에 연결될 수 있음

- 해시 체인 내에서는 정렬이 보장되지 않음

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

버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다. 공유자원은 말 그대로 모두에게 권한이 있기 때문에 누구나 접근할 수 있다. 문제는 하나의 버퍼블록을 두 개 이상 프로세스가 ‘동시에’ 접근하려고 할 때 발생한다. 동시에 접근하면 블록 정합성에 문제가 생길 수 있기 때문이다.

따라서 자원을 공유하는 것처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화(serialization) 메커니즘이 필요하다. 직렬화를 이해하기 쉽게 표현하면, ‘줄 세우기’다. 이런 줄서기가 가능하도록 지원하는 메커니즘이 래치(Latch)다.

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

 

출처 : 친절한 SQL 튜닝 - 조시형 지음