친절한 SQL 튜닝 1장

1장 SQL 처리 과정과 I/O

1.1 SQL 파싱과 최적화

1.1.1 SQL

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

 

1.1.2 SQL 옵티마이저

프로시저를 만들어 내는 DBMS 내부 엔진

1. 후보군 실행계획 탐색
2. 실행계획 예상비용 산정
3. 최저 비용 실행계획 선택

 

1.1.3 SQL 최적화

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 과정

1. SQL 파싱

   1. 파싱 트리 생성
   2. Syntax 체크
   3. Semantic 체크

2. SQL 최적화 : 옵티마이저가 효율적인 실행경로를 선택

3. 로우 소스 생성 : 옵티마이저가 선택한 실행경로를 실행 가능한 코드로 포맷팅 하는 단계

 

1.1.4 실행계획

SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로 표현한 것

 

1.1.5 비용

쿼리를 수행하는 동안 발생할 것으로 예상 I/O 횟수 또는 예상 소요시간

실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이

 

1.1.6 옵티마이저 힌트

SQL데이터 액세스 경로를 정해주어 복잡한 SQL에서의 옵티마이저의 한계 개선

주의사항 )

• 힌트와 힌트 사이에 콤마 사용 불가

  /*+ INDEX(A A_X01) INDEX(B, B_X03) */ --모두 유효
  /*+ INDEX(C), FULL(D) */ --첫 번째 힌트만 유효
  

• FROM절 테이블명 별칭 사용 시 힌트에도 별칭 사용

  SELECT \*+ FULL(EMP) *\ --무효
    FROM EMP E
  

 

1.2 SQL 공유 및 재사용

1.2.1 소프트 파싱 vs 하드 파싱

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

• DB Buffer Cache

• Redo Log Buffer

• Shared Pool

  • Library Cache : 내부 프로시저를 반복 사용할 수 있도록 캐싱해 두는 메모리 공간
  • Data Dictionary Cache

 

실행과정 )

1. 사용자

2. SQL 파싱

3. 캐시 힛

   • O (소프트 파싱) : 실행
   • X (하드 파싱) : 최적화 > 로우 소스 생성 > 실행

 

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

• 오브젝트(테이블, 컬럼, 인덱스) 구조 기본 정보
• 오브젝트 통계
• 시스템 통계
• 옵티마이저 관련 파라미터

 

1.2.2 바인드 변수

SQL은 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 다른 객체 생성 (Case Sensitive)

SQL은 딕셔너리에 저장하지 않는다.

파라미터 Driven 방식으로 SQL을 작성하여 하나의 프로시저를 공유하면서 재사용 (하드파싱 1회)

 

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

디스크 I/O가 SQL 성능을 좌우

 

1.3.1 데이터베이스 저장 구조

테이블스페이스 > 세그먼트 > 익스텐트 > 데이터 블록 > 로우

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

세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하는 것은 아니다.

익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니다.

 

DBA(Data Block Address) : 데이터 블록의 고유 주소값

 

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

• 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
• 랜덤 액세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

 

익스텐트 맵 : 세그먼트에 할당된 익스텐트 목록을 관리하기 위해 세그먼트 헤더에 있는 맵

 

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

자주 읽는 블록에 대한 디스크 접근 시간을 줄이기 데이터 캐싱 사용

데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색

 

• 논리적 I/O : 메모리 I/O와 유사
• 물리적 I/O : 디스크 I/O와 유사

논리적 I/O > 물리적 I/O

 

버퍼캐시 히트율(BCHR) = ( 1 - 물리적I/O / 논리적I/O) * 100

SQL 성능을 향상하려면 논리적 I/O를 줄여야 한다.

BCHR이 높다고 효율적인  SQL은 아니다.

 

1.3.4 Table Full Scan vs Index Range Scan

• Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
• Multiblock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식

Multiblock I/O 방식으로 읽더라도 익슨텐트 경계를 넘지 못한다.

 

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

  • 시퀀셜 액세스 + Multiblock I/O

• Index Range Scan : 인덱스를 이용해서 ROWID로 테이블 레코드를 찾아가는 방식

  • 랜덤 액세스 + Single Block I/O
  • 큰 테이블에서 소량의 데이터를 검색할 때 필수
  • 많은 데이터를 읽을 때는 Table Full Scan보다 불리

인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다.

 

1.3.5 캐시 탐색 매커니즘

1. 해시 알고리즘으로 버퍼 헤더 찾기
2. 포인터로 버퍼 블록 액세스

 

해시 체인 내에서의 정렬 보장 X

 

액세스 직렬화 매커니즘의 필요성 : 하나의 버퍼 블록을 두 개 이상의 프로세스가 동시에 접근하려고 할 때 블록 정합성 문제 위험

• 캐시버퍼 캐시 래치 : 해시 체인 앞에 래치(걸쇠)를 두고, Key를 획득한 프로세스만이 체인에 진입
• 버퍼 Lock : 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제 해결