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

친절한SQL튜닝 정리

 

1. SQL 처리 과정과 I/O

---

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

• SQL은 원하는 결과 집합을 구조적, 집합적으로 선언하지만, 결과 집합을 만드는 과정은 절차적이다.
• 즉, 프로시저가 필요하다.
• SQL 옵티마이저는 이러한 프로시저를 만드는 DBMS 내부 엔진이다.
• 옵티마이저가 프로그래밍을 대신 해주는 셈이다.
• SQL 최적화는 DBMS 내부에서 프로시저를 작성하고 컴파일해 실행 가능한 상태로 만드는 전 과정이다.

 

SQL 최적화

1. SQL 파싱
   • 파싱트리 생성
   • Syntax 체크
   • Semantic 체크
2. SQL 최적화
   • 옵티마이저가 하는 역할이다.
   • SQL 옵티마이저가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로, 다양한 실행경로를 생성해 비교 후 가장 효율적인 하나를 선택한다. -> 가장 적은 비용의 실행경로
3. 로우 소스 생성
   • 로우 소스 생성기(Row-Source Generator)가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계다.

 

SQL 옵티마이저

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

최적화 단계 요약

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

 

실행계획과 비용

• DBMS에는 SQL 실행경로 미리보기 기능이 있다.
• 실행계획이란 SQL 옵티마이저가 생성한 처리 절차를 사용자가 확인할 수 있게 트리구조로 표현한 것이다.
• 미리보기 기능을 통해 자신이 작성한 SQL 테이블이 어떤 테이블과 인덱스를 스캔하는지 확인할 수 있고, 예상과 다른 방식으로 처리되면 실행경로를 변경할 수 있다.
  
  
• 옵티마이저가 특정 실행계획을 선택하는 근거는 비용이다.
• 비용(Cost)은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.
• 그러나 SQL 실행계획에 표시되는 Cost는 예상치다.
  옵티마이저가 여러 통계정보를 활용해 계산한 값이다.
• 즉, 실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 난다.

 

옵티마이저 힌트

• 통계정보에 담을 수 없는 데이터 또는 업무 특성을 활용해 개발자가 직접 더 효율적인 액세스 경로를 찾아낼 수 있다.
• 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다.
• 힌트 사용법 : p. 24
• 옵티마이저가 알아서 판단하도록 하거나, 절대 다른 방식을 선택하지 못하도록 힌트를 빈틈없이 정할 수 있다.
• 어떤 방식이 옳은지는 애플리케이션에 따라 다르다.
• 옵티마이저가 가끔 실수해도 별 문제 없는 시스템이 있는 반면, 작은 실수가 기업에 큰 손실을 끼치는 시스템도 있다.
  이런 경우 옵티마이저의 자율적 판단에 맡기기는 힘들다.

 

SQL 공유 및 재사용

---

소프트 파싱 vs 하드 파싱

• 라이브러리 캐시는 SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간이다.
• SGA(System Global Area)는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어구조를 캐싱하는 메모리 공간인데, 라이브러리 캐시가 SGA의 구성요소다.
• 사용자가 SQL 문을 전달하면, DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 있는지 확인한다.
• 소프트 파싱은 SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것이다.
• 하드 파싱은 찾는데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것이다.

 

라이브러리 캐시 사용 이유

• 데이터베이스에서 처리 과정은 주로 I/O 작업에 집중되는데, 그중 하드파싱은 CPU를 많이 소비하는 몇 안되는 작업이다.
• 어려운(hard) 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버린다면 매우 비효율적이다.
• 이것이 라이브러리 캐시가 필요한 이유다.

 

리터럴 변수

SELECT * FROM TABLE1 WHERE ID = "daykim"

• 위의 WHERE 절에서 처럼 비교되는 값이 상수값으로 직접 선언된 변수

 

바인드 변수

SELECT * FROM TABLE1 WHERE ID = #{userId}

• 위의 WHERE 절에서 처럼 비교되는 값이 바인드 변수 형태로 사용하는 경우의 변수
• 해당 변수 자리는 파라미터로 넘겨지는 값들로 대체된다.

 

바인드 변수의 중요성

• SQL은 함수/프로시저, 트리거, 패키지와 달리 이름이 따로 없다.
• SQL 텍스트 전체가 이름 역할을 한다. 딕셔너리에 저장하지도 않는다.
• 처음 실행할 때 최적화 과정 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재해 여러 사용자가 공유하며 재사용한다.
• 캐시 공간이 부족하면 버려졌다 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다.
  
  
• SQL 자체가 이름이기 때문에, 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조다.
• 일회성 또는 무효화된 SQL까지 모두 저장하려면 많은 공간이 필요하고, SQL 찾는 속도도 그만큼 느려진다.
• 일부 DBMS가 SQL을 영구 저장하지 않는 이유다.
  
  
• 라이브러리 캐시에서 SQL을 찾는데 사용하는 키 값은 SQL문 그 자체다.
• 따라서 프로시저의 내부 처리 루틴이 모두 같다면, 프로시저가 여러개 생성되는 것이 아닌 하나를 공유하며 재사용할 수 있도록 해야한다.
• 예시는 p.33
• 이를 위해 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 바인드 변수가 바로 그것이다.

 

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

---

SQL이 느린 이유

• 거의 디스크 I/O 때문이다.
• OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 일하지 않는다.
• 열심히 일해야하는 프로세스가 쉬고 있으니 I/O가 많으면 성능이 느려진다.

 

데이터베이스 저장구조

• 데이터를 저장하기 위해선 테이블스페이스를 생성해야 한다.
• 테이블스페이스 : 세그먼트를 담는 컨테이너로, 여러개의 데이터파일(디스크 상의 물리저거 OS 파일)로 구성된다.
• 세그먼트
  • 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트다.
  • 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다.
  • 여러 익스텐트로 구성된다.
• 익스텐트
  • 공간을 확장하는 단위
  • 테이블이나 인덱스에 데이터를 입력하다 공간이 부족하면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다.
  • 연속된 블록들의 집합
  • 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다.
  • 한 블록은 하나의 테이블이 독점한다.
  • 한 익스텐트도 하나의 테이블이 독점한다. 즉, 한 익스텐트에 담긴 블록은 모두 같은 테이블이다.
• 블록 :  데이터를 읽고 쓰는 단위
• 데이터파일 : 디스크 상의 물리적인 OS 파일
• 앞들과 데이터파일 간의 관계
  • 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.
  • 하나의 테이블스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터파일로 분산해 저장한다.
  • p.40

 

블록 단위 I/O

• 클라우드에 위치한 문서는 파일 단위로 저장하고, 읽는다.
• DBMS는 블록 단위로 데이터를 읽고 쓴다.
• 테이블 뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

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

• 시퀀셜 액세스
  • 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다.
  • 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼있다.
  • 이 주소값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이다.
• 랜덤 액세스
  • 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.

 

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

p.50

• 논리적 블록 I/O
  • SQL을 처리하는 과정에 발생한 총 블록 I/O
  • 일반적으로 메모리상의 버퍼 캐시를 경유하므로, 메모리 I/O가 곧 논리적 I/O라고 생각해도 무방하다.
• 물리적 블록 I/O
  • 디스크에서 발생한 총 블록 I/O
  • SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할때만 디스크를 액세스한다.
    따라서 논리적 블록 I/O 중 일부를 물리적으로 I/O한다.
• 메모리 I/O는 전기적 신호고, 디스크 I/O는 액세스 Arm을 통해 물리적 작용이 일어나서 메모리 I/O에 비해 상당히 느리다.

 

왜 논리적 I/O인가?

*** p.52 여긴 뭔가 이해 된듯 아닌듯 ***

• 물리적 I/O는 SQL을 실행할 때마다 다르다.
  연속해서 실행하면, DB 버퍼캐시에서 해당 테이블 블록의 점유율이 높아지기 때문이다.
• 한참 후에 다시 실행하면 반대로 물리적 I/O가 늘어난다.
  DB 버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.

 

버퍼캐시 히트율

• 버퍼캐시 히트율을 버퍼캐시 효율을 측정하는데 전통적으로 가장 많이 사용해온 지표다.

BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) * 100
           = ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
           = (1 - (물리적 I/O) / (논리적 I/O)) * 100

• 공식에서 알 수 있듯이 BCHR은 읽은 전체 블록 중 물리적인 디스크 I/O를 수반하지 않고, 곧바로 메모리에서 찾은 비율이다.

공식을 조금 변형하면,

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

• 위 공식에서 알 수 있듯 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야한다.
  
  
• BCHR 공식을 이루는 물리적 I/O는 통제 불가능한 외생변수다.
• 반면, 논리적 I/O는 통제 가능한 내생변수다.
• SQL을 튜닝해 논리적 I/O를 줄이면 물리적 I/O도 줄고, 그만큼 성능도 향상된다.
• BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 효율적인 SQL을 의미하진 않는다.
• 같은 블록을 비효율적으로 반복해서 읽으면 BCHR이 높아지기 때문이다.

 

Single Block I/O vs Multiblock I/O

• 비용, 기술적인 한계 때문에 전체 데이터 중 일부만 캐시에 적재해 읽을 수 있다.
• 캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼 캐시로 적재하고 읽는다.

Single Block I/O

• 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
• 인덱스를 이용할 때 기본적으로 Single Block I/O 방식을 사용한다.
  인덱스는 소량 데이터를 읽을 때 주로 사용하므로, 이 방식이 효율적이다ㅏ.

Multi Block I/O

• 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
• 많은 데이터를 읽을 때 Multi Block I/O 방식이 효율적이다.
  그래서 테이블 전체를 스캔할 때 이 방식을 사용한다.
• 테이블이 클수록 Multiblock I/O 단위가 커야 프로세스가 잠자는 횟수가 줄어든다.
• Multiblock I/O는 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할 때
  디스크상에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재한다.

 

Table Full Scan vs. Index Range Scan

테이블에 저장된 데이터 읽는 방식

1. Table Full Scan

• 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터 찾는 방식

2. Index Range Scan

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

 

• Table Full Scan을 피해야 한다는 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다.
• 한 번에 많은 데이터를 처리하는 집계용 SQL과 배치 프로그램이 특히 그렇다.

인덱스를 사용하는데 성능이 느린 이유

Table Full Scan

• 시퀀셜 액세스와 Multiblock I/O 방식으로 블록을 읽는다.
• 한 블록에 속한 모든 레코드를 한 번에 읽어들인다.
• 캐시에서 못 찾으면, 한 번의 수면(I/O Call)을 통해 인접한 수십 ~ 수백 개 블록을 한꺼번에 I/O 하는 매커니즘이다.
• 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다

 

• 시퀀셜 액세스와 Multiblock I/O가 아무리 좋아도, 수십 ~ 수백 건의 소량 데이터를 찾을 때 수백만 ~ 수천만 건 데이터를 스캔하는 것은 비효율적이다.
  • 큰 테이블에서 소량 데이터를 검색할 때 반드시 인덱스를 이용해야 한다.

 

Index Range Scan

• 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.
• 캐시에서 블록을 못 찾으면, 레코드 하나를 읽기 위해 매번 잠드는 I/O 매커니즘이다.
• 따라서 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다.
• 이 방식을 사용하는 SQL은 슽토리지 스캔 성능이 수십배 좋아져도 성능이 조금 밖에 좋아지지 않는다.
• 게다가 이 방식은 읽었던 블록을 반복해서 읽는 비효율이 있다.
  각 블록을 단 한번 읽는 Table Full Scan 보다 훨씬 불리하다.

 

*** 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일뿐, 모든 성능 문제를 인덱스로 해결하려 해선 안된다.

 

캐시 탐색 메커니즘

• Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼 캐시를 경유한다. p.63
• 버퍼캐시에서 블록을 찾을 때, 해시 알고리즘으로 버퍼 헤더를 찾ㅈ고, 거기서 얻은 포인터로 버퍼 블록을 액세스하는 방식을 사용한다.

해시 구조 특징

• p.63
• 같은 입력 값은 항상 동일한 해시 체인에 연결됨.
• 다른 입력값이 동일한 해시 체인에 연결될 수 있음.
• 해시 체인 내에선는 정렬이 보장되지 않음

 

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

• 버퍼캐시는 SGA 구성요소로 버퍼 캐시에 캐싱된 버퍼블록은 모두 공유자원이다.
• 공유자원은 모두에게 권한이 있어 누구나 접근 가능하다.
• 그러나 하나의 버퍼블록을 두 개 이상의 프로세스가 동시에 접근시, 블록 정합성에 문제가 생길 수 있다.
• 자원을 공유하는 것처럼 보여도, 내부에선 한 프로세스씩 순차적으로 접근하도록 구현하기 위해 직렬화(serialization) 매커니즘이 필요하다.
• 특중 순간에 한 프로세스만 사용하고, 다른 프로세스가 줄서서 기다리는 것이 가능하도록 지원하는 매커니즘이 래치(Latch)다.
  • 대량의 데이터를 읽을 때, 모든 블록에 대한 해시 체인을 탐색한다.
  • DBA(Data Block Address)를 해시 함수에 입력하고, 거기서 반환된 값으로 스캔해야 할 해시 체인을 찾는다.
  • 스캔하는 동안 다른 프로세스가 체인 구조를 변경하면 곤란하다.
  • 이를 막기 위해 해시 체인 래치가 존재한다.
• 빠른 데이터베이스를 구현하려면 버퍼캐시 히트율을 높여야 하지만, 래치에 의한 경합이 발생하면 캐시 I/O도 생각만큼 빠르지 않을 수 있다.
• 캐시 버퍼 체인 뿐 아니라 버퍼블록 자체에도 직렬화 메커니즘인 버퍼 Lock이 존재한다.
  • 읽고자 하는 블록을 찾으면, 캐시 버퍼 체인 래치를 곧바로 해제해야 기다리는 프로세스들이 작업을 재개할 수 있다.
  • 그러나 래치 해제 상태로 버퍼 블록 데이터를 읽고 쓰는 도중, 후행 프로세스가 같은 블록에 접근해 데이터를 읽고쓰면, 데이터 정합성에 문제가 발생할 수 있다.
  • 이를 방지하기 위해 오라클은 버퍼 Lock을 사용한다.
  • 캐시버퍼 체인 래치를 해제하기 전, 버퍼 헤더에 Lock을 설정해 버퍼 블록 자체에 대한 직렬화 문제를 해결한 것이다.
• 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야한다.