SQL 파싱과 최적화

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

원하는 결과집합을 구조적, 집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적일 수밖에 없다. 즉, 프로시저가 필요한데, 그런 프로시저를 만들어 내는 DBMS 내부 엔진이 바로 SQL 옵티마이저다.

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

SQL 최적화

SQL을 실행하기 전 최적화 과정을 세분화하면 아래와 같다.

  1. SQL 파싱
    • 파싱 트리 생성, Syntax 체크, Semantic 체크
  2. SQL 최적화
    • SQL 옵티마이저가 최적의 데이터 액세스 경로를 선택한다.
  3. 로우 소스 생성
    • SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계

SQL 옵티마이저

옵티마이저의 최적화 단계 요약

  1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다.
  2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상 비용을 산정한다.
  3. 최저 비용을 나타내는 실행계획을 선택한다.

옵티마이저는 순식간에 엄청나게 많은 연산을 한다. 고로 Hard 하다.

옵티마이저가 사용하는 정보

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

실행계획과 비용

T 테이블에 통계정보를 수집하는 명령어

exec dbms_stats.gather_table_stats(user, 't');

AutoTrace를 활성화하고 SQL을 실행하면 실행계획을 확인할 수 있다.

set autotrace traceonly exp;

옵티마이저 힌트

기왕에 힌트를 쓸 거면, 빝틈없이 기술해야 한다.

SELECT /*+ LEADING(A) USE_NL(B) INDEX(A (주문일자)) INDEX(B 고객_PX) */
  A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
FROM 주문 A, 고객 B
WHERE A.주문일자 = :ORD_DT
AND A.고객ID = B.고객ID

SQL 공유 및 재사용

소프트 파싱 vs 하드 파싱

SGA

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

라이브러리 캐시

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

소프트 파싱

  • SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것

하드 파싱

  • 찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것

바인드 변수의 중요성

라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값은 'SQL 문 그 자체'이다.

하드파싱이 최초 한 번만 일어나도록 바인드 변수를 사용하자.

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

SQL이 느린 이유

디스크 I/O 때문이다.

OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 CPU를 반환한 채 알람을 설정하고 대기 큐에서 잠을 잔다.

최신 스토리지에서도 전반적으로 I/O 튜닝이 안 된 시스템이라면, 수많은 프로세스에 의해 동시다발적으로 발생하는 I/O Call 때문에 디스크 경합이 심해지고 그만큼 대기 시간도 늘어난다.

데이터베이스 저장 구조

테이블 스페이스

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

세그먼트

  • 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트다.
  • 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다.
  • 여러 익스텐트로 구성된다.

익스텐트

  • 공간을 확장하는 단위다. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다.
  • 연속된 블록들의 집합니다.

블록

  • 익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다.

데이터파일

  • 세그먼트에 할당된 익스텐트들은 서로 다른 데이터파일에 위치할 가능성이 높다.
  • 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터파일로 분산해서 저장하기 때문이다.

DBA

  • 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값
  • 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID(DBA + 블록 내 순번)를 이용한다.

블록 단위 I/O

특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.

오라클은 기본적으로 8KB 크기의 블록을 사용한다. (2KB, 4KB, 16KB 사용 가능)

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

오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다.

시퀀셜 액세스

  • 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식

랜덤 액세스

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

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

DB 버퍼캐시

  • 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.
  • 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.
  • 버퍼캐시 사이즈 확인: show sga

논리적 I/O

  • SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O

물리적 I/O

  • 버퍼캐시에서 블록을 찾지 못해 디스크에서 발생한 총 블록 I/O

버퍼캐시 히트율

  • BCHR = ((논리적 I/O - 물리적 I/O) / 논리적 I/O) x 100
  • BCHR이 높다고 해서 효율적인 SQL을 의미하지 않는다. 같은 블록을 비효율적으로 반복해서 읽으면 BCHR이 높아지기 때문이다.

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

Single Block I/O vs Multiblock I/O

데이터를 모두 캐시에 적재할 수는 없다.

Single Block I/O

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

Multiblock I/O

  • 특정 블록을 읽으려고 I/O Call 할 때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능

  • '인접한' 블록은 같은 익스텐트에 속한 블록이다. (익스텐트 경계를 넘지 못함)

  • 프로세스가 잠자는 횟수를 줄이고 성능을 높일 수 있다.

  • 테이블을 Full Scan 할 때 Multiblock I/O 단위를 크게 설정하면 성능이 좋아진다.

  • DBMS 블록 사이즈가 얼마건 간에 OS 단에서는 보통 1MB 단위로 I/O를 수행한다.(OS마다 다름)

  • show parameter db_file_multiblock_read_count

alter session set db_file_multiblock_read_count = 128;

Table Full Scan vs Index Range Scan

Table Full Scan은 피해야 한다는 많은 개발자의 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다.

Table Full Scan

  • 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식
  • 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다.
  • 한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램에 유리
  • 상당수가 Table Full Scan으로 유도하면 성능이 빨라진다. 조인을 포함한 SQL이면, 조인 메소드로 해시 조인을 선택해주면 된다.

Index Range Scan

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

캐시 탐색 메커니즘

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

해시 구조의 특징

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

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

  • 버퍼블록에 두 개 이상 프로세스가 '동시에' 접근하면 블록 정합성에 문제가 생길 수 있다.
  • 따라서, 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화(줄 세우기) 메커니즘이 필요하다.

SGA를 구성하는 서브 캐시마다 별도의 래치가 존재한다.

래치에 의한 경합이 생길 수 있기 때문에 캐시 I/O도 생각만큼 빠르지 않을 수 있다.

캐시버퍼 체인뿐만 아니라 버퍼블록 자체에도 직렬화 메커니즘이 존재한다. 바로 '버퍼 Lock'이다.

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

캐시버퍼 체인 래치

  • 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 일을 막기 위해 해시 체인 래치가 존재한다.
  • 체인 앞쪽에 자물쇠가 있고 자물쇠를 열 수 있는 키를 획득한 프로세스만이 체인으로 진입할 수 있다.

버퍼 Lock

  • 래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는 도중에 후행 프로세스가 하필 같은 블록에 접근해서 데이터를 읽고 쓴다면 데이터 정합성에 문제가 생길 수 있는데 이를 방지하기 위해 오라클은 버퍼 Lock을 사용한다.
  • 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제를 해결한다.
저작자표시

'DB' 카테고리의 다른 글

[친절한 SQL 튜닝] 조인 튜닝  (0) 2020.09.03
[친절한 SQL 튜닝] 인덱스 튜닝  (0) 2020.09.03
[친절한 SQL 튜닝] 인덱스 기본  (0) 2020.09.03

+ 따끈한 최근 게시물

티스토리툴바