SQL 튜닝

---

SQL 파싱과 최적화

SQL 최적화란?

• SQL은 Structured Query Language의 줄임말이다.

• SQL은 기본적으로 구조적이고, 집합적이고 선언적인 질의 언어이다.

• 원하는 결과집합을 구조적, 집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적이다.

• 따라서 프로시저가 필요한데, 그런 프로시저를 만들어 내는 DBMS 내부 엔진이 SQL 옵티마이저이다.

• 즉, 옵티마이저가 프로그래밍을 대신 해주는 셈이다.

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

※프로시저: DB에 대한 일련의 작업을 정리한 절차

SQL 최적화 과정

1. SQL 파싱: 사용자로 부터 전달받은 SQL을 파싱

   • 파싱 트리 생성: SQL문을 이루는 개별 구성요소를 분석해 파싱 트리 생성
   • Syntax 체크: 문법적 오류 체크
   • Semantic 체크: 의미상 오류 체크 ex) 존재하지 않는 테이블 사용 여부

2. SQL 최적화

   • 옵티마이저가 맡는 역할
   • SQL 옵티마이저는 미리 수집합 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행 경로를 생성해서 비교한 후 가장 효율적인 하나를 선택
   • 데이터베이스 성능을 결정하는 가장 핵심적인 엔진

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

SQL 옵티마이저

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

• SQL 옵티마이저가 항상 최적 경로를 선택하는 것은 아니다. 한계 존재

• 따라서 개발자가 직접 옵티마이저 힌트를 이용해 효율적인 엑세스 경로를 선택할 수 있다.

SGA(System Global Area)

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

• SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 라이브러리 캐시라고 한다.

• 정리하자면 라이브러리 캐시는 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 코드 캐시

소프트 파싱 VS 하드 파싱

• 사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지 확인한다.

• 캐시에서 찾으면 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거친다.

• SQL을 캐시에서 찾아 곧바로 실행 단계로 넘어가는 것을 소프트 파싱, 찾는데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 하드 파싱이라고 한다.

• SQL을 최적화는 꽤 어렵고 무거운 작업이다. 옵티마이저는 SQL 최적화를 위해 테이블, 칼럼, 인덱스 구조에 관한 기본 정보와 오브젝트 통계 및 시스템 통계까지 고려해야 한다. 이렇게 생성한 프로시저를 라이브러리 캐시에 캐싱한다.

바인드 변수의 필요성

• 오라클, SQL Server와 같은 DBMS에서 SQL은 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 재사용한다.

• 라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값은 SQL문 그 자체이다. 따라서 아래는 모두 다른 SQL이다. 의미적으로는 모두 같지만, 실행할 때 각각 최적화를 진행(하드 파싱)하고 라이브러리 캐시에 각각 적재된다.
  
  - SQL 예시(리터럴)
  
  - 내부 프로시저 예시
  

• 위 방식은 매우 비효율적이다. 따라서 바인드 변수를 사용하여 파라미터 Driven 방식으로 SQL을 작성하여 하나의 프로시저를 공유하면서 재사용하는 것이 마땅하다.
  
  - SQL 예시(바인드변수)
  
  - 내부 프로시저 예시
  

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

데이터베이스 저장구조

테이블스페이스

• 데이터베이스에서 데이터는 테이블스페이스에 저장된다.

• 테이블스페이스는 세그먼트를 담는 컨테이너로서, 여러 개의 데이터파일(디스크 상의 물리적인 OS파일)로 구성된다.

세그먼트

• 테이블스페이스는 세그먼트들로 구성된다. ex) 테이블, 인덱스, 파티션, LOB

익스텐트

• 익스텐트는 공간을 확장하는 단위이다. 세그먼트 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다.

• 하나의 익스텐트는 하나의 테이블이 독점한다.

• 이때 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다. 이를 통해 파일 경합을 줄일 수 있다.

블록

• 익스텐트는 연속된 블록(페이지)의 집합이다. 따라서 연속된 여러 개의 데이터 블록으로 구성된다. 이때 데이터 블록들은 인접한 연속된 공간에 위치한다.

• 한 익스텐트에 담긴 블록은 모두 같은 테이블의 블록이다.

• 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. 하나의 블록은 하나의 테이블이 독점한다.

• 모든 데이터 블록은 DBA(Data Block Address)라는 고유 주소값을 갖는다.

• 정리

  • 데이터 파일: 디스크 상의 물리적인 OS파일
  • 테이블스페이스: 세그먼트를 담는 컨테이너
  • 세그먼트: 데이터 저장공간이 필요한 오브젝트
  • 익스텐트: 공간을 확장하는 단위, 연속된 블록 집합
  • 블록: 데이터를 읽고 쓰는 단위

블록 단위 I/O

• DBMS는 블록 단위로 데이터를 읽고 쓴다.

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

• 테이블 또는 인덱스 블록을 엑세스(읽는)하는 방식에는 시퀀셜 액세스와 랜덤 액세스가 있다.

• 시퀀셜 액세스: 논리적, 물리적으로 연결된 순서에 따라 차례로 블록을 읽는 방식
  • 세그먼트에 할당된 익스텐트 목록은 세그먼트 헤더에 맵으로 관리한다.
  • 익스텐트 맵은 각 인스텐트의 첫번째 블록 주소 값(DBA)을 갖는다.
  • 익스텐트는 연속된 블록 집합이므로 테이블을 스캔할 때는 첫번째 블록 뒤에 연속해서 저장된 블록을 읽으면 된다.(Full Table Scan)
  • 그림에서 실선이 여기에 해당

• 랜덤 액세스(점선): 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식
  • 그림에서 점선이 여기에 해당

DB 버퍼 캐시

• SQL을 수행하는 과정에서 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적
• 이를 방지하기 위해 DB 버퍼 캐시를 사용(SGA 구성요소 중 하나)
• DB 버퍼 캐시는 디스크에서 읽은 데이터 블록을 캐싱하는 데이터 캐시
• DB 버퍼 캐시를 통해 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.
• 서버 프로세스는 데이터 블록을 읽을 때 Direct Path I/O를 제외한 모든 블록 I/O는 버퍼캐시부터 탐색
• 버퍼 캐시는 공유메모리 영역이므로 다른 프로세스에서도 접근 가능
• DBMS는 버퍼캐시를 해시 구조로 관리

논리적 I/O VS 물리적 I/O

• 논리적 블록 I/O는 SQL을 수행하면서 읽은 총 블록 I/O

• 일반적으로 모든 블록은 DB 버퍼 캐시를 경유해서 읽으므로 논리적 I/O 횟수는 DB 버퍼캐시에서 블록을 읽은 횟수와 일치

• 물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O

• 버퍼캐시에서 블록을 찾지 못했을 때만 디스크를 액세스하므로 물리적 I/O는 논리적 I/O의 일부

버퍼캐시 히트율

• 버퍼캐시 효율을 측정하는 전통적인 지표

※BCHR: Buffer Cache Hit Ratio

• 애플리케이션 특성에 따라 다르지만, 일반적으로 시스템 레벨에서 평균 99% 히트율을 달성해야 한다.

• 물리적 I/O가 성능을 결정하지만 실제 SQL 성능을 향상하기 위해서는 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.

• BCHR 공식을 변형하면 물리적 I/O = 논리적 I/O x (100% - BCHR)와 같다. 여기에서 BCHR은 통제 불가능한 외생변수(DB 버퍼 캐시 용량 문제)이므로 SQL의 성능을 높이기 위해서는 논리적 I/O를 줄여야 한다.

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

Single Block I/O VS Multiblock I/O

• 메모리 캐시가 클수록 좋지만, 데이터 모두 캐시에 적재할 수는 없다(비용적, 기술적 한계)

• 캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼 캐시로 적재한 후 읽는다.

• I/O Call 할 때 한번에 한 블록씩 요청해서 메모리에 적재하는 방식을 Single Block I/O, 한번에 여러 블록씩 요청해서 메모리에 적재하는 방식을 Multiblock I/O라고 한다.

• Multiblock I/O는 디스크 상에 인접한 블록(같은 익스텐트에 속한 블록)들을 한번에 읽어 캐시에 미리 적재

• 인덱스를 이용할 때 같이 소량의 데이터를 읽을 때는 Single Block I/O가 효율적

• 테이블 전체 스캔 같이 많은 데이터 블록을 읽을 때는 Multiblock I/O가 효율적

Table Full Scan VS Index Range Scan

• Table Full Scan: 테이블 전체를 스캔해서 읽는 방식

  • 테이블에 속한 블록 전체 스캔

• Index Range Scan: 인덱스를 이용한 테이블 액세스

  • 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식

    ※ROWID: 테이블 레코드가 디스크상 저장된 위치 정보

• 인덱스 스캔은 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록 액세스

• 따라서 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다.

• 정리하자면, 인덱스 사용이 SQL 튜닝의 핵심이지만, 성능 문제를 인덱스로만 해결하려 해서는 안된다.

캐시 탐색 메커니즘

• 위에서 설명했듯, Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.
  • 인덱스 루트 블록 읽을 때
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
  • 테이블 블록을 Full Scan 할 때

• 버퍼캐시에서 블록을 찾을 때 위 그림처럼 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 엑세스하는 방식을 사용한다.

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

• 버퍼캐시는 공유자원으로 하나의 버퍼블록에 2개 이상 프로세스가 동시에 접근하려고 할 때 동시성 문제 발생

• 따라서 내부에서 한 프로세스씩 순차적으로 접근하도록 직렬화 메커니즘 필요

• 공유캐시에 대해 한 프로세스만 사용할 수 있도록 줄세우는 메커니즘을 래치(Latch)라고 한다.

• SGA를 구성하는 서브 캐시마다 별도의 채치가 존재하는데, 버퍼캐시에는 캐시버퍼 체인 래치, 캐시 버퍼 LRU 체인 래치 등이 작동한다.

• 캐시버퍼 체인 래치는 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하지 못하도록 key를 획득한 프로세스만 체인에 진입하도록 구현

• 캐시버퍼 체인 래치를 통해 특정 체인에 대한 구조적 접근을 보호

• 하지만 캐시버퍼 체인 래치가 블록의 내용이나 상태에는 관여하지 않음

• 캐시 버퍼 체인 래치는 읽고자 하는 블록을 찾는 즉시 해제되므로 후행 프로세스가 동일한 블록에 접근하여 데이터를 변경한다면 데이터 정합성에 문제 발생

• 이를 방지하기 위해 버퍼 Lock을 통해 버퍼 블록 자체에도 직렬화 메커니즘 적용

• 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정하여 버퍼블록 자체에 대해 직렬화

• 직렬화 메커니즘을 사용하면 경합에 의해 성능 저하가 발생할 수 있다.

• 직렬화 메커니즘에 의한 캐시 경합을 줄이려면 SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 한다.