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

최근에 친절한 SQL 튜닝이라는 책으로 사내 스터디를 진행하게 되었는데, 이번에는 간단하게 내용을 정리하고 조만간 깃허브 블로그로 이전 후에 다시 한 번 정리를 해야할 것 같습니다. 아직 1장이지만 걱정했던 것보단 책이 비유와 함께 설명을 쉽게 해줘서 잘 읽히고 있어 다행이네요...

SQL 처리 과정

SQL은 구조적, 집합적, 선언적 질의 언어로 사용자가 작성한 SQL은 옵티마이저를 통해 최적의 실행 계획으로 프로시저로 작성된다.

SQL 최적화

최적화 = 파싱 + 최적화 + 로우 소스 생성

1. 파싱 : 사용자로부터 전달 받은 SQL을 SQL 파서가 파싱을 진행한다.
   • 파싱 트리 생성 : SQL 문의 개별 구성 요소 분석 및 파싱 트리 생성
   • Syntax 체크 : 문법상의 오류 확인
   • Semantic 체크 : 의미상의 오류 확인 (존재하지 않는 테이블이나 컬럼을 사용하는지 혹은 권한이 부족한지)
2. 최적화 : 옵티마이저가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 실행 경로를 생성해서 비교한 후 가장 효율적인 하나를 선택
   • 쿼리를 실행하기 위한 실행 계획 후보군 선정
   • 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행 계획의 예상 비용 산정
   • 최적 비용을 나타내는 실행 계획 선택
3. 로우 소스 생성 : 로우 소스 생성기가 실행 경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계

실행 계획과 비용

예상 실행 계획을 통해 옵티마이저가 선택한 실행 계획이 어떻게 작성되었는지 확인할 수 있고, 해당 경로와 비용을 근거로 적합한 테이블과 인덱스를 결정할 수 있다.

실행 비용은 쿼리 수행 시간 동안 발생할 것으로 예상 되는 I/O 횟수 및 예상 소요 시간으로 어디까지나 예상치이기 때문에 실측과는 차이가 존재한다.

옵티마이저 힌트

SQL 옵티마이저가 항상 최선의 선택을 하는 것은 아니고, 복잡한 SQL일수록 실수가 발생할 가능성이 높기 때문에 개발자가 직접 옵티마이저 힌트를 사용해 통계 정보에 담을 수 없는 데이터나 업무 특성 등을 활용해 더 효율적인 액세스 경로를 선택할 수 있다.

소프트 파싱과 하드 파싱

라이브러리 캐시 : 옵티마이저가 최적화를 통해 생성한 내부 프로시저를 캐싱해두는 공간

1. SQL 파싱
2. 파싱된 SQL이 라이브러리 캐시에 존재하는지 확인
3. 존재하는 경우(cache-hit) 해당 로우 소스 재사용, 존재하지 않는 경우(cache-miss) 로우 소스 생성 과정 수행

cache-hit의 경우를 소프트 파싱, cache-miss의 경우를 하드 파싱이라고 한다.

하드 파싱의 경우 옵티마이저가 쿼리를 최적화 하기 위해 아래와 같은 정보들을 사용해 다량의 CPU 연산을 처리해야 한다.

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

I/O 작업에 비하면 큰 작업은 아니지만, 적은 연산은 아니기에 캐싱 전략을 사용해 효율적으로 처리한다.