[SQLP 친절한 SQL 튜닝 정리] 1장- SQL 처리 과정과 I/O 정리

SQLP를 준비하며 "친절한 sql 튜닝" 책 1장 SQL 처리과정과 I/O 부분을 정리한 내용 입니다.
친절한SQL튜닝 도서

1장- SQL 처리 과정과 I/O

1.1 SQL 파싱과 최적화

SQL은 구조적, 집합적, 선언적 질의 언어다.
 
SQL 최적화

• DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정
• SQL 최적화 세부 과정
  SQL 파싱, SQL 최적화, SQL 로우 소스 생성 3단계로 이루어져 있다.
  • 1) SQL 파싱
    • Syntax 체크 : SQL 문장의 문법 오류 검증
    • Semantic 체크 : 오브젝트 권한, 존재하지 않는 테이블/컬럼을 사용하는지 등을 검증
    • 위 단계가 끝난 후 SQL문을 이루는 개별 구성 요소를 분석 해 파싱 트리 형태로 변형하여 옵티마이저에게 넘겨짐.
  • 2) SQL 최적화
    • 미리 수집한 시스템 및 오브젝트 통계 정보를 바탕으로 SQL 옵티마이저가 효율적인 실행경로를 선택
    • 데이터베이스 성능을 결정하는 가장 핵심적인 엔진
  • 3) 로우 소스 생성
    • SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계
      • 위와 같은 작업을 하는게 로우 소스 생성기의 역할

옵티마이저

• 사용자가 원하는 작업을 가장 효율적으로 수행 할 수 있도록 최적의 실행경로를 선택해주는 DBMS의 핵심엔진
• 최적화 단계
  • 사용자가 SQL 실행 > 옵티마이저가 데이터 딕셔너리에 미리 수집해둔 오브젝트 및 시스템 통계 정보를 이용해 각 실행계획의 예상 비용 산정 > 최저 비용(Cost)의 실행계획 선택
• 옵티마이저 수집 정보
  • 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
  • 오브젝트 통계 : 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
  • 시스템 통계 : CPU 속도, Single BLock I/O, Multiblock I/O 속도 등
  • 옵티마이저 관련 파라미터

옵티마이저 판단 기준

• 옵티마이저가 최적의 실행계획을 판단하는 근거는 비용(Cost)
• 비용(Cost)란? 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요 시간을 표현한 값

 

1.2 SQL 공유 및 재사용

SGA 구성 요소

SGA(System Global Area)

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

라이브러리 캐시

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

SQL 실행 과정

SQL 실행 과정

1. 사용자가 쿼리를 실행
2. SQL 파싱
3. 라이브러리 캐시에 파싱된 SQL이 존재하는지 확인
   • SQL 캐시에 존재하면 캐시에서 SQL을 찾아 곧바로 실행 -> 소프트 파싱
   • SQL 캐시에 존재하지 않으면 최적화 과정을 거쳐 실행 -> 하드 파싱

왜 하드파싱일까?

• 옵티마이저가 최적의 실행경로를 도출하기 위해 무수히 많은 연산과정을 거치기 때문에 하드하다고 표현

 
SQL에서 바인드 변수의 중요성

• SQL은 "SQL문 자체"가 KEY 값이다
  
  • 띄어쓰기, 대/소문자 등 SQL 문자체가 달라지면 오라클은 다른 쿼리라고 인식한다.
• 바인드 변수 처리 되지 않으면 모든 쿼리는 "하드 파싱" 된다.
• 바인드 변수를 사용하면 하드파싱은 최초 한번만 일어나고, 라이브러리캐시에 캐싱된 SQL을 공유하며 재사용 한다.

-- 아래 쿼리는 ORACLE에서 다르다고 인식
SELECT * FROM TEMP
select * FROM temp
sELECT * FROM temp

 
 

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

1.3.1 SQL이 느린 이유

• SQL이 느린 이유는 디스크 I/O 때문
• 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있다.
• 디스크에서 데이터를 읽을 때 CPU를 OS에 반환하고 I/O가 완료되기 까지 다른 프로세스는 waiting 상태가 되기 때문에 느리다.

1.3.2 데이터 저장 구조

• 테이블 스페이스 :세그먼트를 담는 콘테이너, 여러 개의 데이터 파일(디스크 상 물리적 OS 파일)로 구성
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
• 익스텐트 : 공간을 확장하는 단위, 연속된 블록들의 집합(단, 익스텐트끼리 연속된 공간은 아님)
                  한 익스텐트에 담긴 블록은 모두 같은 테이블 블록
• 블록 : 데이터를 읽고 쓰는 단위, 데이터를 실제로 저장하는 공간, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드
• DBA(Data Block Address) : 디스크 상에서 몇 번 데이터 파일에 몇 번째 블록인지를 나타내는 자신만의 고유주소 값.

 

1.3.4 시퀀셜 엑세스 VS 랜덤 엑세스

시퀀셜 엑세스

• 위 그림에 실선에 해당
• 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방법
• 인덱스 Index Full Scan에 해당
  • 인덱스는 리프 블록의 앞 뒤를 가르키는 주소가 존재하므로 순차적으로 스캔이 가능
• 테이블 Full Table Scan에 해당
  • 블록간에는 서로 논리적인 연결 고리가 없기 때문에, 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵(map)으로 관리하고 있다.
  • 인스턴트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖기 때문에, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽는다.

랜덤 엑세스

• 위 그림에 점선에 해당
• 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식
• 인덱스를 통해 Range Scan하게 되면 랜덤 엑세스를 하게 된다.

 

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

DB 버퍼 캐시

• SGA 구성요소로, 디스크에서 읽은 데이터 블록을 캐싱(데이터 캐시)해 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있게 도와주는 메모리 캐시
• DB 버퍼 캐시에 데이터 블록이 없으면 디스크에서 읽어 버퍼 캐시에 적재 한다.

논리적 I/O(=메모리 I/O)

• SQL을 처리하는 과정에서 메모리 버퍼캐시에서 발생 한 총 블록 I/O
• 디스크에서 읽고 버퍼 캐시에 적재한 다음에 읽기 때문에 디스크 I/O도 포함되어 있는 값

물리적 I/O

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

 

실행계획에서의 블록 수 보는 방법

• DB 버퍼캐시에서 읽은 총 블록 갯수 : Query + Current 
• 디스크에서 물리적으로 읽은 블록 갯수 : Disk
• 디스크에서 읽은 다음에 버퍼캐시에 적재 해서 읽으므로 Query + Current에 Disk 값이 포함되어 있음

 

1.3.6 Single Block I/O VS MultiBlock I/O

Single Block I/O

• 한 번에 한 블록씩 요청해서 DB 버퍼 캐시 메모리에 적재하는 방식
• 대상
  • 인덱스 루트 블록 읽을 때
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

Multi Block I/O

• 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
• 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능
• 한번에 많은 데이터를 읽으므로 프로세스가 waiting하는 횟수가 감소 해 대량 데이터를 읽을 때 효율적(Table Full Scan)
• 단 익스텐트 경계를 넘지 못함
  • ex) 한 익스텐트에 20개의 블록이 담겨 있고 MultiBlock I/O 단위가 8일 때, 세번째 I/O Call에서는 4개 블록만 읽음.

1.3.7 Table Full Scan VS Index Range Scan

Table Full Scan

• 시퀀셜 엑세스와 Multi Block I/O 방식으로 디스크 블록을 읽음
• 한 블록에 속한 모든 레코드를 한번에 읽어 들이고, 캐시에서 못찾으면 " 한 번의 수면(I/O Call)"을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O 하는 메커니즘

Index Range Scan

• 랜덤 엑세스와 Single Block I/O 방식으로 디스크 블록을 읽음
• 캐시에서 블록을 못 찾으면, "레코드 하나를 읽기 위해 매번 잠을 자는 I/O" 메커니즘

 

1.3.8 캐시 탐색 메커니즘

• Direct Path I/O 를 제외한 모든 블록 I/O 는 메모리 버퍼캐시를 경유한다.
• 버퍼캐시는 해시 구조로 관리 되며, 해시 알고리즘으로 버퍼 헤더를 통해 얻은 포인터로 버퍼 블록을 엑세스 한다

버퍼캐시 탐색 과정을 거치는 오퍼레이션

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

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

• 버퍼캐시는 SGA 구성요소 이므로 공유 자원이다.
• 하나의 블록을 두 개 이상 프로세스가 "동시에" 접근할때 블록 접합성 문제가 발생할 수 있기 때문에, 순차적으로 접근하기 위한 직렬화 메커니즘이 필요

캐시버퍼 체인 래치

• 대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인 탐색을 한다.
• 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경할 수 없도록 체인 앞쪽에 좌물쇠를 건다.
• 자물쇠를 열 수 있는 키(Key)를 획득한 프로세스만이 체인으로 진입 가능

 

---