Skip to content

Latest commit

 

History

History
274 lines (200 loc) · 14.5 KB

README.md

File metadata and controls

274 lines (200 loc) · 14.5 KB

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

  • 옵티마이저가 SQL을 어떻게 처리하는지
  • 서버 프로세스는 데이터를 어떻게 읽고 저장하는지

1.1 SQL 파싱과 최적화

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

  • SQL(Structed Query Language: 구조적 질의 언어)
    • 구조적(structed)
    • 집합적(set-based)
    • 선언적(declarative)

SQL은 원하는 결과집합을 구조적, 집합적으로 선언하는데 이 때 결과집합을 만드는 과정에서 절차적인 프로시저가 필요하다.

프로시저(Procedure)란 어떤 업무를 수행하기 위한 절차이다. 즉, 프로세스를 절차적으로 기술해 놓은 것

프로시저는 DBMS 내부 엔진이 만들어내고 DBMS 내부 엔진을 옵티마이저라고 한다. 사용자 --> (SQL) --> 옵티마이저 --> (실행계획) --> 프로시저

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

1.1.2 SQL 최적화

SQL의 최적화 과정은 다음과 같다.

  1. SQL 파싱

    SQL Parser가 파싱을 진행한다.

    • 파싱 트리 생성: SQL 문을 이루는 개별 구성요소를 분석
    • Syntax 체크: 문법 오류 체크
      • 예시1) 사용할 수 없는 키워드 사용
    • Semantic 체크: 의미상 오류가 없는지 확인
      • 예시1) 존재하지 않는 테이블 또는 컬럼을 사용하는지
      • 예시2) 오브젝트에 대한 권한이 있는지

  2. SQL 최적화

    • 옵티마이저의 역할
    • 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성 후 가장 효율적인 하나를 선택

  3. 로우 소스 생성

    • 로우 소스 생성기(Row-Source Generator)의 역할
    • SQL 최적화 단계에서 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅

1.1.3 SQL 옵티마이저

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

  1. 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾는다.
  2. Data Dictionary에 미리 수집해 둔 오브젝트 통계 및 시스템 정보를 이용해 각 실행계획의 예상비용을 산정
  3. 그 중 최저비용인 실행계획을 선택한다.

1.1.4 실행계획과 비용

네비게이션의 기능과 같이 여러 경로를 검색해 미리 확인하는 것과 비슷하다고 볼 수 있다.

  • 옵티마이저는 비용(Cost)을 보고 선택한다.
  • I/O 횟수 또는 예상 소요시간을 기반으로 산정
  • 어디까지나 통계정보를 활용한 예측값이기 때문에 실측치가 아니다.

DBMS에도 'SQL 실행경로 미리보기' 기능이 있다.

1.1.5 옵티마이저 힌트

네비게이션이 추천하는 길이 항상 최선이 아닐 때도 있다.

운전자의 경험을 활용해 더 빨리 목적지에 도착할 수 있는 것 처럼 개발자가 직접 더 효율적인 액세스 경로를 찾아내 데이터 액세스 경로를 바꿀 수 있다.

이럴 때 옵티마이저 힌트를 이용한다.

  • 힌트 사용법
    • 주석 기호에 +를 붙인다. 
      SELECT /*+ INDEX(A 고객_PK) */
    고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID = '00001'
    • 주의사항 
      /*+ INDEX(A A_X01) INDEX(B, B_X03) */ ->  모두 유효
/*+ INDEX(C), FULL(D) */ -> 첫 번째 힌트만 유효

옵티마이저에게 특정 부분은 개발자가 직접 선택하고, 나머지는 옵티마이저에게 맡기는 식으로 지시를 할 수 있다.

  1. 액세스 방식만 지정
  2. 액세스 방식, 조인 방식과 순서 모두 지정

자주 사용하는 힌트 목록

1.2 SQL 공유 및 재사용

  • 소프트 파싱과 하드 파싱의 차이점 설명

1.2.1 소프트 파싱 vs 하드 파싱

  • Soft Parsing
    1. SQL 파싱
    2. 캐시에 존재
    3. 곧바로 실행
  • Hard Parsing
    1. SQL 파싱
    2. 캐시에 존재하지 않음
    3. 최적화
    4. 로우 소스 생성

SQL 최적화 과정에서 어떤 조인을 사용할지 순서는 어떻게 할지만 계산해도 굉장히 많은 경우의 수가 계산이 된다. 이 과정에서 옵티마이저가 사용하는 정보는 다음과 같다.

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

짧은 시간에 이 많은 정보를 참조해 효율성을 판단하는 과정은 가볍지 않고 굉장히 무겁다. 이렇게 어려운 작업을 통해 생성된 프로시저를 한 번 사용 후 버린다면 굉장히 비효율적이기 때문에 Library Cache를 사용한다.

SQL 최적화 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 Library Cache라고 한다.

Library Cache는 SGA의 구성요소이다.

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

1.2.2 바인드 변수의 중요성

이름없는 SQL 문제

SQL은 사용자 정의 함수/프로시저, 트리거, 피키지 등과 다르게 이름이 없고 SQL 자체가 이름이다.

SQL의 텍스트가 조금이라도 변경되면 SQL ID도 변한다. SQL ID도 SQL의 전체 텍스트를 간략히 표현하기 위한 내부 함수를 이용해 생성한 값이다.

즉, SQL 전체 텍스트와 SQL ID는 1:1 대응 관계이다.

공유 가능 SQL

SELECT * FROM emp WHERE empno = 7900;

select * from EMP where EMPNO = 7900;

select * from emp where empno = 7900;

같은 결과여도 위에서 말했듯이 SQL 텍스트값 자체가 ID이기 때문에 위 3개의 SQL은 모두 다르다.

실행할 때 각각 최적화를 진행하고 Library Cache에서 별도 공간을 사용한다.

String sql = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";

위와 같은 쿼리를 소스에 작성했다고 하자. 대용량 트래픽으로 여러 loginId값이 들어오면 라이브러리 캐시에 발생하는 여러 종류의 경합 때문에 부하가 생긴다. 이유는 로그인 할 때마다 DBMS 내부 프로시저를 하나씩 생성해서 캐시에 적재하게 되는 셈이기 때문이다.

  • 올바른 방법 (파라미터 Driven 방식)

    create procedure LOGIN (login_id in varchar2) { ... }

    바인드 변수를 사용해서 처리하게 되면 라이브러리 캐시에는 로그인 관련해서 여러개가 아닌 하나의 SQL만 발견된다.

    SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

    이렇게 캐싱된 SQL을 여러 유저가 공유하면서 재사용 할 수 있다.

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

  • I/O에 대한 이해
  • 데이터 저장 구조, 디스크 및 메모리에서 데이터를 읽는 메커니즘

1.3.1 SQL이 느린 이유

결론적으로는 디스크 I/O 문제이다. OS 또는 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다.

Process는 생성(new) 이후 종료(terminated) 전까지 준비(ready)와 실행(running)과 대기(waiting) 상태를 반복한다.

I/O가 많이 발생하면 프로세스는 interrupt에 의해 수시로 실행 준비 상태(Runnable Queue)로 전환했다가 다시 실행 상태로 전환하는 과정에서 대기 상태가 많아지고 결국 느려지게 된다.

1.3.2 데이터베이스 저장 구조

데이터를 저장하기 위해서는 세그먼트를 담는 컨테이너인 테이블스페이스를 생성해야 한다. 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다.

  • 세그먼트: 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트이다.
    • 테이블, 인덱스, 파티션, LOB 등등
    • 세그먼트는 여러 익스텐트로 구성된다.
  • 블록: 데이터를 읽고 쓰는 단위
  • 익스텐트: 공간을 확장하는 단위, 연속된 블록 집합
    • 공간이 부족해지면 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다.
    • 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록
    • 익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니다. (파일 경합을 줄이기 위해 여러 데이터파일로 분산해서 저장하기 때문)
  • 테이블스페이스: 세그먼트를 담는 컨테이너
  • 데이터 파일: 디스크 상의 물리적인 OS파일

DBA(Data Block Address): 디스크 상에서 데이터 블록이 몇번 데이터 파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값

1.3.3 블록 단위 I/O

블록은 DBMS가 데이터를 읽고 쓰는 단위이다.

오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기위해 8KB를 읽는다.

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

테이블 또는 인덱스 블록을 액세스하는 방식으로 두 가지가 있다.

  • 시퀀셜(Sequential) 액세스: 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식

    • 인덱스의 리프블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있다
    • 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식
    • 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리
    • 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽는 경우 Full Table Scan이라 한다.

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

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

DB 버퍼캐시

자주 읽는 블록을 매번 디스크에서 읽는 것은 비효울적이기 때문에 SGA의 구성요소 중 하나인 DB 버퍼캐시(데이터 캐시)를 사용한다.

버퍼 캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.

Library Cache는 SQL과 실행계획, 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라고 부른다.

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

  • 논리적 I/O: SQL을 처리하는 과정에 발생한 총 블록 I/O
    • 버퍼 캐시를 경유하는 경우가 이에 속한다.
    • SQL을 수행하면서 읽은 총 블록 I/O
    • 논리적 I/O는 항상 일정하게 발생
  • 물리적 I/O: 디스크에서 발생한 총 블록 I/O
    • 버퍼 캐시에서 찾지 못해 디스크를 액세스하는 경우
    • 디스크에서 읽은 블록 I/O

버퍼캐시 히트율 (Buffer Cache Hit Ratio:BHCR)

BHCR = (1 - (물리적 I/O) / (논리적 I/O)) * 100

  • SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.
  • 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 SQL 튜닝이다.

Single Block I/O vs Multiblock I/O

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

  • Single Block I/O: I/O Call을 할 때, 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
    • 인덱스는 소량 데이터를 읽을 때 주로 사용
  • Multiblock I/O: 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
    • 테이블 전체를 스캔할 때 이 방식을 사용
    • 단위를 크게 설정하면 성능이 좋아지는 이유
    • I/O Call을 할 때 디스크 상에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능
    • 배치처리와 비슷한 개념

1.3.7 Table Full Scan vs. Index Range Scan

테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용해서 읽는 방식.

  • Table Full Scan
  • Index Range Scan: 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
    • ROWID는 테이블 레코드가 디스크 상에 어디 저장되었는지 가리키는 위치정보

많은 데이터를 읽을 때 인덱스를 이용하는데 성능이 느린 경우

인덱스는 Single Block I/O 방식으로 디스크 블록을 읽기 때문에 캐시에서 데이터를 찾지 못하면 레코드 하나를 읽기위해 I/O가 발생하는 메커니즘이기 때문이다. 결국 각 블록을 단 한번 읽는 Table Full Scan보다 훨씬 불리하다.

인덱스는 항상 옳은 것도 아니고 Table Full Scan이 항상 나쁜 것도 아니다.

1.3.8 캐시 탐색 메커니즘

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

버퍼캐시 탐색 과정을 거치는 경우

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

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

해시 구조의 특징

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

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

버퍼캐시는 SGA의 구성요소이므로 캐싱된 버퍼블록은 모두 공유자원이다.

공유자원이라해도 내부에서는 한 프로세스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화 메커니즘이 필요하다.

같이 사용하는 것처럼 보이지만, 특정 순간에는 하나의 프로세스만 사용할 수 있고 프로세스는 자원을 위해 줄을 서서 기다려야 한다.

이런 줄서기가 가능하도록 지원하는 메커니즘이 래치(Latch)이다.

캐시버퍼 체인뿐만 아니라 같은 블록에 접근 버퍼블록 자체에도 직렬화 메커니즘이 존재하는데 버퍼 Lock을 사용한다.