SQL 튜닝

SQL을 본격적으로 수행하는 과정에서 SQL 최적화를 이해하기 위해 최근에 책을 한 권 샀습니다.
'친절한 SQL 튜닝'이라는 책인데 이에 대해 공부를 해보고 학습 내용을 정리해보고자 합니다.

1.1 💡 SQL 파싱과 최적화

SQL 튜닝에 앞서 옵티마이저가 어떻게 SQL을 처리하고, 서버 프로세스는 데이터를 어떻게 읽고 저장하는지 살펴보자.

두 테이블을 부서번호로 조인하여 사원명으로 정렬하는 로직.

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY E.ENAME

이렇게 원하는 명령을 DB에 질의하는 것을 쿼리라고 하고 위 단어로 배열된 쿼리를 SQL문이라고 한다.
즉, SQL은 구조적이고 집합적이며 선언적인 질의 언어이고, 이를 만드는 과정은 절차적인 과정을 거칠 수 밖에 없다.
이때 이러한 순차적인 과정을 프로시저라고 칭하고
이러한 프로시저를 생성하는 DBMS 내부 엔진으로 SQL 최적화를 수행하는 것이
SQL 옵티마이저이다.

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

SQL 최적화 과정을 세분화하면 다음과 같다.
1. SQL 파싱 : 사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서가 파싱을 진행한다.
SQL 파싱은 다음 3개로 요약.

• 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
• Syntax 체크 : 문법적 오류가 없는지 확인
• Semantic 체크 : 의미상 오류가 없는지 확인

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

3. 로우 소스 생성 : SQL 옵티마이저가 생성한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계.

1.1.4.✍️ SQL 실행계획과 비용

실행계획 : DBMS에도 SQL 실행경로 미리보기.
-> SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로 표현한 것.

그렇다면 특정 실행계획을 선택하는 근거는 무엇인가?
< 테스트용 테이블 >

CREATE table T
AS
SELECT d.no, e.*
FROM scott.emp E
	, (SELECT rownum no FROM dual connect by level <= 1000) d;

SQL※PLUS에서 AutoTrace를 활성화하고 SQL을 실행하면 실행계획을 확인할 수 있다.
SQL을 선택하고 Ctrl + E 키를 누르면 됌.
즉, 옵티마이저는 각 인덱스를 선택할 때 추정되는 비용을 근거로 수행한다.

1.1.5.✍️ 옵티마이저 힌트

수동적으로 효율적인 데이터 액세스 경로를 파악하기 위해 사용하는 기법

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

옵티마이저 힌트를 사용할 떄의 주의 사항은 다음과 같다.
1. 힌트 내 , 금지
2. 스키마명 명시 금지
3. ALIAS를 지정한 테이블명을 사용하기.

1.2 💡 SQL 공유 및 재사용

본 절에서는 소프트 파싱과 하드 파싱의 차이를 설명.

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

소프트 파싱 : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
하드 파싱 : 캐시에서 찾지 못하여 최적화, 로우 소스 생성 단계까지 모두 거치는 것을 하드 파싱이라 함.'

SQL 옵티마이저는 순식간에 수 많은 연산을 수행한다.

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

하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고 짧은 순간에 딕셔너리와 통계 정보를 읽어 각각에 대한 효율성을 판단하는 과정은 결코 가벼울 수 없다.

하드 파싱은 CPU를 많이 소비하는 몇 안되는 작업 중 하나.

이렇게 하드 파싱을 거쳐 생성한 내부 프로시저를 저장하기 위해 라이브러리 캐시가 필요하다.

SQL은 영구적으로 보관되지 않고 모든 텍스트가 이름 역할을 하여 라이브러리 캐시에 저장되고 여러 사용자가 이를 공유하며 재사용하게 된다. 만일 캐시 공간이 부족하면 버려 진 후 다음 번에 실행 시 똑같은 최적화 과정을 거쳐 캐시에 적재된다.

Oracle, MSSQL Server 등에서 SQL을 영구 저장하지 않는 이유는 1개다.
모든 SQL은 변수명만 변해도 새로운 SQL로 처리하기에 모든 SQL을 저장하려면 그만큼 많은 공간이 필요하고 결국 캐싱이라는 의미가 사라지게 된다.

한 예시로 사용자의 로그인 기능이 있는 쇼핑몰의 경우 특가 행사가 있을 때 사람들이 몰리게 될텐데 일반적인 ID를 받는 식으로 DB를 조회한다면 서버에 부화가 될 가능성이 높다.
그렇기에 ID를 파라미터로 받아 재사용하며 처리하는 기능이 더 효율적인텐데, 이를 바로
바인드 변수 라고 칭한다.

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

I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다.
SQL 작업이 느린 이유는 열이면 열 디스크 I/O 때문이다.

OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스(실행 중인 프로그램)는 멈춰있기에 SQL이 느리다. (프로세스는 하나의 CPU를 공유하지만 특정 순간에는 1개의 프로세스만 CPU를 사용할 수 있기 때문이다.)

1.3.2. 데이터베이스 저장 구조.

데이터를 저장하려면 우선 테이블스페이스를 생성해야 한다. 테이블스페이스는 여러 세그먼트를 담는 콘테이너로서 여러 개의 데이터 파일(OS파일)로 구성된다.

세그먼트는 테이블, 인덱스처럼 데이터 저장 공간이 필요한 오브젝트이고 여러 익스텐트로 구성된다.

• ※ (익스텐트는 공간을 확장하는 단위.)

테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할 지를 지정한다.

정리를 한 번 하자면, 다음과 같다.
1. 데이터를 저장하기 위해선 테이블스페이스를 생성해야 함.
2. 데이터 블록 ㄷ 익스텐트 ㄷ 세그먼트 ㄷ 테이블스페이스 순서로 구성되어 있다.
3. 익스텐트 ㄷ 데이터파일 ㄷ 테이블스페이스, 데이터파일 ㄷ 세그먼트 ㄷ 데이터파일

• 데이터 블록이란, 사용자가 입력한 레코드를 실제로 저장하는 공간 (한 블록은 한 테이블이 독점)
• 익스텐트란 연속된 블록들의 집합, 공간을 확장하는 단위
• 세그먼트란 여러 익스텐트로 구성된 하나의 파티션을 의미

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

SELECT segment_type, tablespace_name, extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE owner = USER
AND segment_name = 'MY_SEGMENT'
ORDER BY extent_id;

위 쿼리를 통해 구성된 세그먼트를 DBA_EXTENTS 뷰에서 조회 시 아래와 같은 표가 나온다

SEGMENT_TYPE	TABLESPACE_NAME	EXTENT_ID	FILE_ID	BLOCK_ID	BLOCKS
TABLE	USERS	0	1	1	4
TABLE	USERS	1	1	9	4
TABLE	USERS	2	2	1	4
TABLE	USERS	3	2	5	4
TABLE	USERS	4	2	13	4
TABLE	USERS	5	3	1	4
TABLE	USERS	6	4	9	4
TABLE	USERS	7	5	5	4

이 세그먼트에 할당된 2번 익스텐트는 2번 데이터 파일 1번 블록으로부터 연속된 4개 블록으로 이루어져있다. 바로 뒤에 할당된 3번 익스텐트는 그래서 5번부터 시작한다. 다른 익스텐트들은 직전 인스텐트와 인접하지 않는다.

최종적으로 정리하면 다음과 같아.

• 데이터 블록 (페이지) : 데이터를 읽고 쓰는 단위
• 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트 (테이블, 인덱스, 파티션, LOB 등)
• 테이블스페이스 : 세그먼트를 담는 콘테이너
• 데이터파일 : 디스크 상의 물리적인 OS파일
  ※ 모든 데이터 블록은 디스크 상 자신이 몇 번째 블록인지 나타내는 고유 주소값을 갖는다.
• 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID를 활용.
• 테이블 스캔 시 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용.

테이블과 인덱스는 블록 단위로 데이터를 읽고 쓴다.

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

테이블 또는 인덱스 블록을 액세스(읽는) 방식은 2가지가 있다.
1. Sequential 액세스 : 논리적, 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식.
(인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있음.)

-> 이 주소값에 따라 앞/뒤로 순차적으로 스캔하는 방식

테이블 블록 간에는 서로 논리적으로 연결되어 있지 않다.
Q. 그렇다면 순차적인 방식을 어떻게 선택하는가?
-> 오라클의 경우 익스텐트 목록을 세그멘트 헤더에 맵으로 관리한다.
(익스텐트 맵은 각 익스텐트의 첫 블록 주소 값을 갖는다, 이를 순차적으로 읽으면 Full Table Scan이다.)

2. Random 액세스 : 순서를 따지지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식.

+) SGA 구성요소 중 하나인 라이브러리 캐시와 DB버퍼캐시가 있다.

라이브러리 캐시가 SQL, 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라 한다면,
DB 버퍼캐시는 '데이터 캐시'라고 한다.
-> 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이기 위함.

논리적 블록 I/O : SQL 처리하는 과정에서 발생한 총 블록 I/O
물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O

결국 물리적 I/O는 논리적 I/O 중 캐싱에서 발견하지 못한 경우에 액세스하는 것.

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

논리적 I/O를 줄일 수 있는 방법 : SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 됌.

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