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

SQL 처리 과정과 I/O 

1. SQL 파싱과 최적화 

2. SQL 공유 및 재사용

3. 데이터저장 구조 및 I/O 매커니즘

1. SQL 파싱과 최적화 

SQL은 Structured Query Language의 줄임말이다. 말 그대로 구조적 질의 언어다. 

SQL은 기본적으로 구조적이고 집합적이고 선언적인 질의 언어다. 원하는 결과집합을 구조적, 집합적으로 선언하지만 그 겨롸집합을 만드는 과정은 절차적이다 그렇기 때문에 프로시저가 필요하며 그 프로시저를 만들어 내는 DBMS 내부엔진이 바로 옵티마이저이다. 이 옵티마이저가 프로그래밍을 대신해 주는 셈이다. 

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

SQL 최적화 

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

   파싱트리를 생성하고 문법검사 -> 의미검사(?) -> 권한검사와 같은 것을 한다 

SQL  최적화 : 옵티마이저가 역할을 맡고 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 

                  하나를 선택한다. 데이터베이스의 성능을 결정하는 가장 핵심적인 엔진이다.

로우 소스 생성 : 로우(Row??) 소스 생성기가 그 역할을 맡고 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계이다.

SQL 옵티마이저 

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터엑세스 경로를 선택해 주는 DBMS 핵심 엔진이다. 

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

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

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

옵티마이저는 네비게이션에서 길을 찾아주는 역할을 하며 비용을 근거로 인덱스 사용 유무와 쿼리 수행방법을 결정한다. 

SQL을 최적화 할 때 옵티마이저가 사용하는 정보 

- 테이블, 컬럼, 인덱스 구조에 관한 정보

- 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 컬럼 통계

- 시스템 통계 : CPU 속도 Single Block I/O 속도, Multi Block I/O 속도 

- 옵티마이저 관련 파라미터 

2. SQL 공유 및 재사용

라이브러리 캐시 : 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간 

SGA(System Global Area) : 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다. 

소프트 파싱 : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것

하드 파싱 : 캐시에서 찾는 데 실패해서 최적화 및 로우소스생성 단계까지 모두 거치는 것 

라이브러리 캐시가 필요한 이유 

데이터 베이스에서 이루어지는 처리과정의 대부분은 I/O 과정에 집중되는 반면 하드 파싱은 CPU를 많이 소비하는 몇 안되는 작업 중 하나 

이렇게 어렵게 작업을 거처 생성한 내부 프로시저를 한 번만 사용하고 버린다면 비효율적이다. 

이름없는 SQL 문제 

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

DBMS에서 수행되는 SQL이 모두 완성된 SQL이 아니며 개발과정에서 수시로 변경이 일어날 수 있다 일회성(Ad-hoc) SQL도 있고 무효화된 SQL 까지 모두 저장해서 쓰려면 많은 공간이 필요하게 된다. 그만큼 SQL을 찾는 속도도 느려지므로 오라클, SQL Server 같은 DBMS가 SQL을 영구 저장하지 않는 쪽을 택했다. 

프로시저에서 파라미터를 전달 받아 실행하는 경우가 추천되며 이런 경우 바인드 변수만 변경되며 프로시저 하나를 공유하면서 재사용할 수 있다. 

3. 데이터저장 구조 및 I/O 매커니즘

프로세스의 생명주기 

프로세스는 실행 중인 프로그램이며 생성 이후 종료 전까지 준비와 실행 대기를 반복한다. 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비상태로 전환 했다가 다시 실행 상태로 전환한다 

열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 waiting 상태에서 I/O가 완료되기를 기다린다. 이렇게 기다리는 시간이 생기게 되면 SQL이 느려지게 된다. 이 때를 디스크 I/O 라고 하고 이것은 SQL의 성능에 큰 영향을 끼친다. 

데이터베이스의 저장 구조 

가장 큰 개념으로 테이블 스페이스가 있으며 테이블 스페이스는 여러개의 데이터파일로 구성된다. 또 테이블 스페이스는 세그먼트들을 포함한다. 세그먼트는 테이블, 인덱스 처럼 데이터 저장공간이 필요한 오브젝트다. 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다. 

세그먼트는 여러 익스텐트들로 구성된다. 테이블도 하나의 세그먼트이며 인덱스(테이블??)도 하나의 세그먼트이다. 

익스텐트는 연속된 블록들의 집합이기도 하다. 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. SQL 서버는 블록대신 페이지 라는 용어를 사용한다. 

블록(페이지) : 데이터를 읽고 쓰는 단위

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

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

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

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

블록이 DBMS가 데이터를 읽고 쓰는 단위다. 

??? 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 

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

시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로

                    서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이다. 

   각 익스텐트의 첫 번째 블록뒤에 연속해서 저장된 블록을 순서대로 읽으면 그것이 Full Table Scan 이다. 

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

DB 버퍼 캐시 : 앞의 라이브러리 캐시가 프로시저 실행 계획과 같은 코드 캐시라고 한다면 DB 버퍼 캐시는 데이터 캐시라고 할 수 있다. 

                   디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는데 목적이 있다. 

                   서버 프로세스와 데이터 파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다. 

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

                논리적 I/O와 메모리 I/O가 같은 으미는 아니지만 같다고 생각해도 무방하다. 

                아무리 여러번 실행해도 매번 읽는 블록 수는 같으며 SQL을 수생하면서 읽은 총 블록 I/O가 논리적 I/O이다. 

물리적 I/O : 디스크에서 발생한 총 블록 I/O를 말한다. DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O이다. 

버퍼캐시 히트율 (BCHR)

- 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다. 

- 실제 SQL 성능을 향상하려면 물리적 I/O(분자)가 아닌 논리적 I/O(분모)를 줄여야 한다.

- SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 되며 논리적 I/O는 항상 일정하게 발생하지는 통제가능한 내생변수다. 

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

버퍼캐시 히트율의 주의할 점 

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

Single Block I/O

- 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식 

- 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록모 Single Block I/O 방식 사용 

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

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

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

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

Multi Block I/O 

- 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식 

- 많은 데이터 블록을 읽을 때 효율적이며 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식 사용 

Table Full Scan

- 시퀀설 엑세스와 Multiblock I/O 방식으로 디스크 블록을 읽어 들인다. 

- 한 블록에 속한 레코드를 한번에 읽어 들이고 캐시에서 못 찾으면 한번의 수면(I/O Call)을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O하는 방식 

Index Range Scan 

- 큰 테이블에서 데이터를 소량 검색할 때는 인덱스를 꼭 이용해야 한다.  

- 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.

- 캐시에서 블록을 못 찾으면 레코드 하나를 읽기 위해 매번 프로세스 waiting에서 대기하게 된다. 하나의 레코드를 읽기 위해 블록을 계속 읽게되는 것은 성능 저하의 우려가 있다. 

- 많은 데이터를 읽을 때는 Table Full Scan이 유리하다.  

성능문제는 인덱스로 해결하지 말고 데이터 결과 집합이 많으면 Full Table Scan이 Index Scan 보다 유리하다. 

캐시 탐색 매커니즘 

버퍼 캐시 탐색 과정을 거치는 경우 

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

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

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

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

- 테이블 블록을 Full Scan 할 때 

내부구조 

- 버퍼캐시를 해시 구조로 관리한다. 

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

- 버퍼블록은 공유자원이다. 즉 모두에서 권한이 있기 때문에 누구나 접근할 수 있다. 문제는 두 개 이상의 프로세스가 동시에 접근하려고 할 때 발생한다. 

- 공유자원이라도 내부에서는 한 프로세스식 접근하도록 직렬화 매커니즘이 필요하다. 이런 직렬화를 실현하기 위해 DBMS는 동시성 제어를 위한 Lock을 제공한다. 

- 프로세스가 줄을 서서 기다리는 것을 가능하도록 지워너하는 매커니즘이 래치(Latch)이다. 

- 오라클은 버퍼 헤더에 버퍼 Lock을 설정함으로써 버퍼 블록 자체에 대한 직렬화 문제를 해결하고 있다 (SQL Server 는??)