[친절한 SQL 튜닝 스터디] SQL 처리 과정과 I/O

친절한 SQL 튜닝 스터디를 시작합니다.

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

SQL 파싱과 최적화

SQL 최적화란 무엇인가

SQL 이란?

• SQL은 Structured Query Language 라고 구조적 질의 언어.
• 구조적이고 집합적이고 선언적인 질의 언어

쿼리를 실행하면서 원하는 결과를 만들때, 절차를 만들고 그 절차대로 결과를 만든다.
여기서 특정한 업무를 수행하기 위한 절차를 '프로시저'라 한다.

쿼리를 실행할 때, 그 프로시저를 만들어내는 DBMS 내부 엔진이 'SQL 옵티마이저' 이다.

SQL이라는 질의 언어를 옵티마이저에게 던져주면 그 옵티마이저가 프로그래밍을 대신 해준다고 보면다. DBMS 내부에서 옵티마이저가 프로시저를 작성하고 컴파일 후 실행항 가능한 상태로 만드는 모든 과정을 'SQL 최적화'라고 한다.

SQL 최적화 과정

1. SQL 파싱
2. SQL 최적화
3. 로우 소스 생성

1. SQL 파싱

쿼리를 받으면 SQL 파서가 파싱을 진행.

1. 파싱트리 생성 : SQL문을 이루는 개별 구성요소 분석후 파싱 트리 생성
2. syntax 체크 : 문법 오류가 있는지 확인
3. semantic 체크 : 의미상 오류가 없는지 확인

2. SQL 최적화

옵티마이저가 미리 수집한 시스템, 오브젝트 통계 정보를 최적의 실행계획을 선택한다.

3. 로우 소스 생성

옵티마이저가 선택한 실행계획을 프로시저로 포멧팅 하는 단계.
그 역할을 로우 소스 생성기(Row- Source Generator)가 한다.

옵티마이저

사용자가 원하는 작업의 가장 효율적인 길을 선택해주는 엔진이다.

옵티마이저의 최적화 단계

1. 사용자의 쿼리 수행에 사용될 실행계획 후보군을 선정
2. 데이터 딕셔너리에 수집해둔 시스템, 오브젝트 통계 정보를 이용해 실행계획들의 예산비용 산정
3. 최저 비용 실행 계획 선택

실행계획 (Execution Plan)

DBMS에서 실행계획을 확인하면 옵티마이저가 생성한 처리절차를 확인할 수 있다.
옵티마이저는 실행계획을 비용을 기준으로 선택한다.

'비용(Cost)'은 쿼리를 수행하는동안 발생하는 I/O 횟수 및 예상 소요시간이다. (또는 CPU 점유율로 나타나기도 함.)

옵티마이저 힌트

개발자가 직접 데이터 엑세스 경로를 변경할 수 있다.
그 경로는 '옵티마이저 힌트' 를 이용해서 변경할 수 있다.

데이터 엑세스 경로는 Index scan, Full scan 등 말 그대로 데이터 엑세스 하는 방법

아래와 같이 쿼리문에 주석 기호에 +를 붙이면 된다.

SELECT /*+ INDEX (A 고객_PK) */
    고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID = '0000000008'

주의사항

• FROM에 ALIAS를 지정하면 힌트에도 ALIAS 꼭 사용.
• 테이블 지정시 스키마명 명시❌
• 힌트 나열할때 , 사용하면 안됨

/*+ INDEX(A A_X01) INDEX(B, B_X03) */ -> 유효 (힌트 인자는 , 사용가능)
/*+ INDEX(A A_X01), INDEX(B B_X03) */ -> 첫번째 인자만 유효.
/*+ FULL(SCOTT.EMP) */ -> 스키마명 사용하면 안됨. (무효)
SELECT /*+ FULL(EMP) */ FROM EMP E -> ALIAS 사용해야 함. (무효)

-- 주석은 실수의 위험이 있어서 선호하지 않음.

옵티마이저 힌트는 언제 사용하는가

애플리케이션 환경에 따라 최적의 방식이 달라질수 있다. 만약 옵티마이저의 실수에 큰 피해가 생기는 시스템이라면 빈틈없이 옵티마이저 힌트를 기술하는 것이 좋다.

SQL 공유 및 재사용

소프트파싱과 하드파싱 차이점과, 바인드 변수의 중요성을 알아보자

소프트파싱과 하드파싱

앞의 SQL 최적화를 마치면, 만들어진 내부 프로시저를 재사용 할 수 있도록 '라이브러리 캐시'에 캐싱한다. 라이브러리 캐시는 SGA 구성요소이다.

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

DBMS는 쿼리를 파싱한 후 라이브러리 캐시에 관련 프로시저가 있는지 확인한다.
캐시에서 프로시저를 찾으면 바로 실행하지만 없다면 최적화를 진행한다.

• SQL을 캐시에서 찾아 바로 실행 단계로 가면 '소프트 파싱(Soft Parsing)'
• SQL을 캐시에서 찾는것을 실패하고 최적화 단계를 모두 거치면 '하드 파싱 (Hard Parsing)'

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

쿼리를 실행하는데 옵티마이저는 엄청나게 수많은 연산을 거쳐 실행계획을 도출해낸다.
이 최적화 과정은 CPU를 많이 소비하는 무거운 작업이다. 이 작업을 매 실행마다 비효율적인 작업이기 때문에 라이브러리 캐시를 사용하여 이미 만든 프로시저를 사용해야 하는 것이다.

옵티마이저가 SQL 최적화에 사용하는 정보들 (책 내용 인용)

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
• 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
• 옵티마이저 관련 파라미터

바인드 변수의 중요성

함수나 프로시저, 트리거, 패키지 등은 생성할 때 이름을 갖는다. 하지만 SQL은 이름이 없고 전체 SQL 텍스트가 이름 역할을 하고 딕셔너리에 저장도 안됨. 라이브러리 캐시에 이름없는 SQL 텍스트가 캐싱되어 있는데 상수값이 달라지면 다른 SQL로 판단하여 하드파싱한다.

하지만 바인드 변수를 사용하면 SQL을 변경하지 않고 라이브러리 캐시의 내부 프로시저를 그대로 사용할 수 있다.

바인드 변수는 아래와 같이 ? 에 파라미터로 값을 전달하는 변수이다.
SELECT * FROM EMP WHERE EMPNO = ?

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

I/O 튜닝이 곧 SQL 튜닝!

SQL이 느린이유

디스크 I/O 하는동안 프로세스가 잠을 자기 때문! 잠자는 시간을 줄여야 한다!

데이터베이스 저장 구조

)

출처

• 구루비 : http://www.gurubee.net/lecture/2163
• 오라클 : https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch3.htm
• 테이블 스페이스 : 세그먼트를 담는 컨테이너
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트들. (각 세그먼트는 테이블, 인덱스, 파티션, LOB 등을 담는다.)
• 익스텐트 : 공간을 확장하는 단위. 테이블 또는 인덱스에 데이터를 입력하다가 공간이 부족해지면 테이블 스페이스로부터 공간을 할당 받는다.
• 블록 (페이지) : 데이터를 읽고 쓰는 단.위 사용자가 입력한 레코드가 실제로 저장되는 공간.
  한 플록에 저장된 레코드는 모두 같은 테이블 레코드이다.

세그먼트에 할당된 익스텐드들은 분산될 가능성이 아주 크다.
DBMS가 파일 경합을 줄이기 위해 데이터를 가능한 여러 데이터파일로 분산저장함.

아래는 각 요소들의 관계 ERD이다.

데이터를 읽고 쓰는 단위

DBMS는 블록 단위로 데이터를 읽고 쓴다. 레코드 하나만 읽는다고 해도 블록을 통째로 읽는다.
오라클은 기본 블록 단위가 8KB인데 1Byte 레코드를 읽기 위해 8KB를 읽는다.

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

시퀄셜 액세스

논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
오라클은 세그먼트에 할당된 익스텐드 목록을 세그먼트 헤더에 맵으로 관리함. 익스텐트 맵은 각 익스텐트의 첫번째 블록 주소를 가지고 있기 때문에, 익스텐트 첫번째 블록부터 하나하나 읽으면 Full Table Scan이 됨. Full Table Scan 하려면 시퀀셜 엑세스 해버렷!

랜덤 액세스

논리적, 물리적 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식.

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

DB 버퍼캐시

캐시가 있는 이유. => RAM이 있는 이유와 비슷함.
매번 오래걸리는 디스크를 읽는 것은 비효율적이다. 그래서 데이터 캐싱 매커니즘은 필수이다.

• SGA 구성요소 중에 DB 버퍼캐시도 중요한 요소
• DB 버퍼캐시는 데이터 캐시이다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해서 같은 블록에 대한 반복 I/O Call을 줄일 수 있다.
• 버퍼 캐시는 공유 메모리 영역이므로 다른 프로세스도 좋다.

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

논리적 I/O

• SQL을 처리하는 과정중 발생하는 총 블록 I/O를 말한다.
• 메모리 I/O + Direct I/O = 논리적 I/O.

물리적 I/O

• 디스크에서 발생한 총 블록 I/O를 말한다.
• 버퍼캐시에서 못 찾은 블록만 디스크 액세스 하는 I/O이다.
• 메모리 I/O에 비해 1만배 느리다. 디스크 경합 생기면 더 느리다.

버퍼캐시 히트율

버퍼캐시 효율을 측정하는데 전통적인 방법 : BCHR

BCHR    = (캐시에서 찾은 블록 수 / 총 읽은 블록 수) * 100
        = ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
        = (1 - 물리적 I/O / 논리적 I/O) * 100

물리적 I/O가 성능을 결정하지만, 실제로 SQL 성능을 향상시키려면 논리적 I/O을 줄여야 한다.

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

물리적 I/O는 BCHR에 의해 결정되고 BCHR은 시스템 상황에 따라 달라지므로, 물리적 I/O는 결국 시스템 상황에 따라 결정됨.
즉 논리 I/O를 줄여야 성능을 높일 수 있다.

-- BCHR이 70%라고 가정

-- 첫번째 논리적 I/O는 10000개
물리적 I/O = 10000 * (100 - 70)% = 3000

-- 두번째 논리적 I/O는 100개
물리적 I/O = 100 * (100 - 70)% = 300

논리적 I/O 줄이는 방법

SQL을 튜닝해서 읽는 총 블록 갯수를 줄이면 된다.
논리적 I/O을 줄임으로서 물리적 I/O를 줄이는 것이 SQL 튜닝이다.

• 물리적 I/O 를 줄이려면 메모리를 증설해서 db 버퍼캐시를 늘리는 방법도 있습니다.

BCHR을 높이는 방법은 같은 블록을 자주 사용하게 하면 버퍼캐시를 사용하기 때문에 높아진다.
같은 블록을 자주 사용하게 해서 BCHR을 높이고 논리적 I/O를 줄여 물리적 I/O도 함께 줄이는 것이 SQL 튜닝이다.

일단 내가 생각한 결론

• 논리적 I/O를 줄여야 물리적 I/O를 줄일 수 있는데, 논리적 I/O를 줄이려면 메모리에서 읽는 총 블록의 갯수를 줄여야한다.
• 총 블록의 갯수는 쓸데없고 무의미하게 부르는 블록의 갯수를 줄이는 것.
• BCHR이 높다고 좋은것은 아니다. 블록의 반복 호출이 많아질수록 BCHR이 높아지는데, 이것은 블록의 무의미한 반복 호출이 많다고 볼 수도 있다. 그러면 다른 유효한 블록의 호출이 느려질 수 있다.
• 비효율적이고 반복적인 논리적 I/O를 줄이자
• 유효하게 자주 I/O되는 블록을 버퍼캐시에 적절히 점유율을 확보하여 속도를 올리자.

Single Block I/O vs Mulit Block I/O

Single Block I/O

• 디스크 I/O 한번에 한 블록씩 요청해서 메모리에 적재하는 방식
• 인덱스와 테이블 블록 모두 이 방식을 사용
• 인덱스는 소량의 데이터를 읽어서 이 방식이 효율적임.

Mulit Block I/O

• 한번에 여러 블록을 요청해서 메모리에 적재하는 방식
• 대상 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 방식
• 테이블 전체를 스캔(Full Scan)할때 좋은 방식 (I/O 할때 프로세스 Sleep을 줄여줌)

Mulit Block Read Count

DBMS가 Mulit Block I/O 수행하면서 한번에 가져올수 있는 블록의 총 갯수이다. (책에서는 손수레로 표현)

• 오라클의 블록 단위 : 8kb
• 최대로 설정할 수 있는 Mulit Block Read Count : 128
• 8kb * 128 = 1024kb = 1Mb
• 즉, Mulit Block I/O 수행하며 가져올 수 있는 최대 크기 : 1Mb

Mulit Block I/O 는 익스텐트를 벗어나지 못한다.

• 1개의 익스텐트의 블록 수 : 20개로 가정
• Mulit Block Read Count : 8 이라고 가정
• 한개의 익스텐트 처음부터 읽었을때 읽는 블록
  • 1번째 Mulit Block I/O : 1 ~ 8
  • 2번째 Mulit Block I/O : 9 ~ 16
  • 3번째 Mulit Block I/O는? : 16 ~ 20 (24가 아님)

즉, MulitBlock I/O는 익스텐트 경계를 넘을 수 없다.

Single Block I/O와 Mulit Block I/O는 섞어 쓴다.

익스텐트 안의 블록을 전체 읽어야 하는데, 만약 일부가 버퍼캐시에 있다면?
버퍼캐시에서 읽을 블록을 확인하고, 듬성듬성 읽게 되는 블록이 있다면 Single Block I/O를 사용하는게 적절하다.

• 아래는 익스텐트이다. 아래의 1~10까지의 번호는 블록의 번호이다.
• 괄호친 블록의 번호는 버퍼캐시에 있다.
• M : MulitBlock I/O
• B : 버퍼캐시
• S : Single Block I/O

1	2	3	4	(5)	6	(7)	8	9	10
M	M	M	M	B	S	B	M	M	M

• 1 ~ 4번까지: Mulit Block I/O
• 5번: 버퍼캐시로 메모리 I/O
• 6번: Single Block I/O
• 7번: 버퍼캐시로 메모리 I/O
• 8~10번: Mulit Block I/O

Table Full Scan vs Index Range Scan

Table Full Scan

• 테이블 전체를 스캔해서 읽는 방식
• 다량의 데이터를 읽을 때 좋은 방식
• 시퀀셜 액세스와 Mulit Block I/O 방식을 이용하여 디스크 블록을 읽는다.

Index Range Scan

• 인덱스를 이용해서 읽는 방식
• 소량의 특정 데이터를 검색할 때 좋은 방식
• 인덱스에서 일정량을 스캔해서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
• 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.

ROWID는 테이블 레코드가 디스크상에 어디에 저장 되어있는지 가리키는 위치정보

Table Full Scan이 오히려 Index Range Scan보다 좋은 경우가 있을 수 있다.
한번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램은 Table Full Scan이 좋을 수 있음.
조인을 포함한 SQL이면 '해시조인' 선택해주면 된다.

왜 인덱스가 느릴까?

• Single Block I/O와 Mulit Block I/O
• Index Range Scan와 Table Full Scan

많은 데이터를 읽으려고 할때 인덱스를 이용하면 Table Full Scan이 유리함. => 읽으려는 데이터 양의 차이.

많은 양의 데이터를 Index Scan하면 Single Block I/O를 엄청 많이 하기 때문에 I/O가 많아지는 것은 속도가 느려지는 것이다.

캐시 탐색 메커니즘

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

버퍼캐시 탐색과정

아래 오퍼레이션은 모두 버퍼캐시 탐색과정을 거침

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소정보로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할 때

버퍼캐시에서 블록을 찾을 때, 해시 알고리즘으로 버퍼 헤더를 찾음. 헤더에서 얻은 포인터로 버퍼 블록을 액세스 함.

해시 구조의 특징 (Hash를 알면 패스)

• 같은 입력값은 항상 동일한 해시 체인에 연결됨.
• 다른 입력값이 동일한 해시 체인에 연결될 수 있음.
• 해시 체인 내에는 정렬이 보장되지 않음.

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

• 버퍼캐시는 SGA라서 버퍼캐시에 캐싱된 버퍼블록은 공유 자원임.
• 버퍼캐시는 누구나 접근 가능
• 버퍼캐시 내 버퍼블록을 2개 이상 프로세스가 동시 접근하려고 하면 블록 정합성에 문제가 생김.
• 직렬화(serialization) 매커니즘을 사용하여 순차 접근하도록 구현
• 직렬화가 가능하도록 지원하는 매커니즘이 래치(Latch)이다.

캐시버퍼 체인 래치

• 해시체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는걸 막기 위해 체인래치가 존재함.
• 체인에 접근하기 위해 프로세스가 래치 Key를 획득해야만 해시 체인에 접근할 수 있다.
• 캐시 히트율을 올려도 이 체인 래치의 경합 때문에 생각보다 빠르지 않을 수 있다.

버퍼캐시에 작동하는 래치

• 캐시버퍼 체인 래치
• 캐시버퍼 LRU 체인 래치

버퍼 Lock

• 버퍼 블록에 존재하는 직렬화 매커니즘이다.
• 래치 해제한 상태로 버퍼블록 사용 도중, 후행 프로세스가 같은 블록에 접근할 경우 정합성의 문제가 생김. 이를 방지하기 위한 매커니즘이다.
• 캐시버퍼 체인래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼 블록 자체에 대한 직렬화 문제 해결

직렬화 매커니즘에 의한 캐시 경합을 줄이려면 SQL 튜님을 통해서 쿼리의 "논리적 I/O" 자체를 줄여야 한다.