[SQL] SQL 최적화, 옵티마이저

본 내용은 조시형님의 '친절한 SQL 튜닝' 책을 읽고 이해한 내용을 바탕으로 합니다.

[목표]

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

SQL 최적화

SQL 최적화?

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

과정

1. SQL 파싱

• 파싱 트리 생성
• syntax 체크(문법적 오류 체크-사용할 수 없는 키워드 사용? 순서 올바른가? 누락된 키워드가 있는가?)
• semantic 체크(의미상 오류 체크-존재하지 않는 테이블, 컬럼 사용? 오브젝트에 대한 권한 존재?)

2. SQL 최적화

• 옵티마이저가 다양한 실행경로를 생성하여 비교 후 가장 효율적인 것 선택한다.
• 옵티마이저는 데이터베이스 성능을 결정하는 가장 핵심적인 엔진!

3. 로우 소스 생성

• 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계.
  
  
  
  
  
  
  
  
  
  
  
  

SQL 옵티마이저

SQL 옵티마이저는 자동차 내비게이션과 비슷하다.
경로 검색하고 이동 경로 미리 확인하는 것은 DBMS의 실행계획(Execution Plan)과 비교할 수 있다.

내비게이션이 선택한 경로가 마음에 안들면 검색모드를 변경하거나 경유지 추가해서 운전자가 원하는 경로로 바꾸는 것처럼, 개발자가 직접 힌트를 주어 실행계획을 변경할 수 있다.

또 내비게이션의 예상시각이 실제와 다를 수 있는 것처럼, cost도 예측치이기 때문에 실제와 차이가 날 수 있다.

이렇듯 SQL 옵티마이저는 (가장 효율적으로 수행 가능한) 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심엔진이다!

=> SQL을 실행하는데, 이를 가장 빠르고 효율적으로 수행할 최적의 경로를 생성해준다.

옵티마이저의 최적화 단계

1. 후보 실행 계획들을 찾기
2. 각 실행계획의 예상비용 산정하기
3. 최저 cost의 실행계획 선택
   이 때 cost는 쿼리 수행동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간.
   
   
   
   

힌트

SQL이 복잡할수록 실수할 가능성이 크다.
택시 기사님들이 경험으로 더 빠른 길을 아는 것처럼 개발자가 직접 더 효율적인 경로를 찾아낼 수도 있다. 이와 같이 개발자는 옵티마이저 힌트를 이용해서 데이터 액세스 경로를 직접 바꿀 수 있다.
이를 힌트라고 부르는데 힌트를 명시하는 방법은 다음과 같다.
/*+ (힌트명) */

힌트 사용 시 주의점

• 힌트 안에 인자를 나열할 땐 콤마를 사용할 수 있지만 힌트와 힌트 사이에 사용할 수 없다.
  • /*+ INDEX(테이블 인덱스명) INDEX(테이블, 인덱스명)*/
• 테이블 지정할 때 스키마명까지 명시하면 안된다.
  • 스키마? 특정 사용자가 생성한 모든 오브젝트들. 즉, 스키마≒오너
• FROM절 테이블명 옆에 ALIAS지정했다면 힌트에도 반드시 ALIAS 사용해야한다.
  
  
  
  
  
  
  
  
  
  
  
  

자주 사용하는 힌트목록

출처

최적화 목표

• /*+ALL_LOWS */ : 전체 처리속도 최적화
• /*+FIRST_ROWS(N) */ : 최초 N건 응답속도 최적화
  
  
  
  
  

액세스 방식

• /*+FULL */ : 인덱스 타지말고 바로 테이블 풀스캔으로 접근해라
• /*+INDEX */ : 인덱스를 타라
• /*+INDEX_DESC */ : 인덱스를 ORDER BY DESC 역순으로 타라
  (시간, 결과값 등 최근인 것 혹은 MAX값 구할 때 좋음)
• /*+INDEX_FFS */ : INDEX FAST FULL SCAN으로 타라
• /*+INDEX_SS */ : INDEX SKIP SCAN
  
  
  
  

조인순서

• /*+ORDERED */ : FROM절에 나열된 테이블 순서대로 조인해라
• /*+LEADING */ : 내가 힌트절에 열거한 테이블 순서대로 조인해라
  • EX: /*+ LEADING (A B C)*/ -- A,B,C 순서대로 조인하세요
• /*+SWAP_JOIN_INPUTS */ : 해시조인의 경우, BUILD INPUT를 명시적으로 선택
  • EX: /*+ SWAP_JOIN_INPUTS(A)*/ --해시조인의경우 BUILD INPUT과 PROBE에 대한 순서를 정할 수 있다. 
    
    
    
    

조인방식

• /*+USE_NL */ :NL(NESTED LOOP - 중첩루프)방식 조인 유도
• /*+USE_MERGE */ : 소트머지 조인으로 유도
• /*+USE_HASH */ : 해시조인으로 유도
• /*+NL_SJ */ : NL SEMI조인으로 유도
• /*+MERGE_SJ */ : 소트머지 세미조인으로 유도
• /*+HASH_SJ */ : 해시 세미조인으로 유도
  
  
  
  

서브쿼리팩토링

• /*+MATERIALIZE */ : WITH문으로 정의한 집합을 물리적으로 생성하도록 유도 
  • EX) WITH /*+ MATERIALIZE*/ T AS (SELECT ...)
• /*+INLINE */ : WITH문으로 정의한 집합을 물리적으로 생성하지않고 INLINE 처리하도록 유도
  • EX) WITH /*+ INLINE*/ T AS (SELECT ...)
    
    
    
    

쿼리변환

• /*+ MEERGE */ : 뷰 머징 유도
• /*+NO_MERGE */ : 뷰 머징 방지
• /*+UNNEST */ : 서브쿼리 UNNESTING 유도
• /*+NO_UNNEST */ : 서브쿼리 UNNESTING 방지
• /*+PUSH_PRED */ : 조인조건 PUSHDOWN 유도
• /*+NO_PUSH_PRED */ : 조인조건 PUSHDOWN 방지
• /*+USE_CONCAT */ : OR 또는 IN-LIST조건을 OR-EXPANSION으로 유도
• /*+NO_EXPAND */ : OR 또는 IN-LIST 조건에 대한 OR-EXPANSION방지
  
  
  
  

병렬처리

• /*+PARALLEL */ : 테이블 스캔,  DML 병렬방식으로 처리하도록 할때 사용.. 단일 대형 테이블의 접근시 정말 많이 쓴다.
  • EX) /*+ PARALLEL(T1 4)*/
• /*+PARALLEL_INDEX */ : 인덱스 스캔을 병렬방식으로 처리하도록 유도
• /*+PQ_DISTRIBUTE */ : 병렬수행시 데이터 분배방식 결정
  • EX) PQ_DISTRIBUTE(T1 HASH(--BUILD INPUT) HASH(--PROBE TABLE))
    
    
    
     

그외 기타

• /*+APPEND*/ : DIRECT PATH INSERT유도로  INSERT  문에 주로 많이 쓴다
• /*+DRIVING_SITE */ : DB LINK REMOTE쿼리에 대한 최적화 및 실행 주체 지정 (LOCAL 또는  REMOTE)
• /*+PUSH_SUBQ */ : 서브쿼리를 가급적 빨리 필터링하도록 유도
• /*+NO_PUSH_SUBQ */ : 서브쿼리를 가급적 늦게 필터링 하도록 유도