[Database] 옵티마이저

parsing

• soft parsing
  • 입력받은 sql이 이전에 실행된 적 있는지 메모리를 검사하고, 실행한 기록이 있으면 기존에 실행했던 방식으로 실행하는 것
• hard parsing
  • 입력받은 sql이 이전에 실행된 적 없다면 어떤방식으로 처리할 것인지 실행계획을 세운 후 실행하는 것

SQL 처리 과정

• SQL 입력 > 문법 검사 > SHARED POOL 안에 동일 SQL 실행 기록이 있는지 확인
• 있음
• 그냥 실행
• 없음
• 객체 및 컬럼에 대한 접근 권한 검사
• 옵티마이저 모드에 따른 최적의 실행 계획 수립
• SHARED POOL에 실행계획 저장
• 실행

옵티마이저

• 가장 효율적인 방법으로 SQL을 실행할 수 있도록 실행 계획을 세우는 DBMS의 핵심 엔진

규칙 기반 옵티마이저

• RBO : Rule Based Optimizer
• 처리 방식의 우선순위를 정해 놓고 우선순위가 앞서는 방식을 채택
  • ex) primary key로 조회가 full table scan 조회보다 우선순위가 높다
• 우선순위가 정해져 있으므로 쿼리 실행 방식을 미리 예측할 수 있음
• 오라클 10g 버전 이후부터는 지원하지 않음
• Hint와 Hash Join을 사용할수 없다는 단점을 갖는다.

비용 기반 옵티마이저

• CBO : Cost Based Optimizer
• 각 실행 계획별 비용을 계산하여 최소의 비용을 사용하는 실행 계획을 채택

옵티마이저의 구성 요소

• Query Transformer
  • parsing 과정을 거친 쿼리를 좀 더 나은 실행계획을 갖는 SQL로 변형하는 작업을 수행
• Estimator
  • Query Transformer로부터 받은 SQL을 수행하는데 드는 총 비용을 계산
• Plan Generator
  • Estimator로 계산된 값들을 토대로 여러 실행 계획들을 비교하여 최종 실행 계획을 선발

옵티마이저 모드

• CHOOSE
  • 규칙 기반 옵티마이저를 사용할 것인지 비용 기반 옵티마이저를 사용할 것인지를 설정
  • 현재는 거의 사용하지 않음
• FIRST_ROWS
  • 첫 건을 출력하는 데 걸리는 시간이 최소인 실행 계획을 채택하는 설정
  • 규칙 기반과 비용 기반 옵티마이저의 혼용 형태, 비권장
• FIRST_ROWS_n
  • n건을 출력하는 데 걸리는 시간이 최소인 실행 계획을 채택하는 설정
  • FIRST_ROWS를 보완한 형태
• ALL_ROWS
  • 모든 데이터를 출력하는데 걸리는 시간이 최소인 실행 계획을 채택하는 설정
  • ORACLE 10g 버전 이후로 이 설정이 디폴트

옵티마이저 파라미터

• OPTIMIZER_MODE
  • 어떤 방식의 옵티마이저를 사용할지 설정
• OPTIMIZER_DYNAMIC_SAMPLING
  • SQL PARSING 작업을 할 때 통계 정보가 없는 객체들을 자동으로 통계 정보를 수집할지 여부를 설정
• OPTIM_PEEK_USER_BIND
  • 바인드 변수를 사용한 SQL의 실행 계획을 파라미터가 다른 동일한 SQL을 수행했을 때 그대로 사용하는 것을 BIND PEEKING이라 하는데 이것을 사용할지 여부를 설정

통계 정보

• 실행 계획을 선택할 때 참고하는 자료
• 테이블, 칼럼, 인덱스, 시스템 등 각각의 값이나 평균 수치 정보들을 나타냄
• 적절한 시기에 갱신해주어야 참고자료로서 효용가치가 생김

통계 정보 생성 방법

• ANALYZE 명령문
• DBMS_STATS 패키지
  • 오라클에서 제공하는 방식
  • 병렬로 통계 정보 수집 가능
  • 파티션에 대한 글로벌 통계 수집 가능
  • 권장하는 방식