[SQLD] 옵티마이저와 실행계획

1. 옵티마이저(Optimizer)란?

• 사용자의 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다.
• 최적의 실행 방법을 실행계획(Execution Plan)이라고 한다.
• 다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것이 옵티마이저의 역할이다.
• 최적의 실행 방법을 결하는 방식에 따라 규칙기반 옵티마이저(RBO, Rule Based Optimizer)와 비용기반 옵티마이저(CBO, Cost Based Optimizer)로 구분할 수 있다.

1-1. 규칙기반 옵티마이저(RBO, Rule Based Optimizer)

• 규칙(우선 순위)을 가지고 실행계획을 생성한다.
• 해당 SQL문에서 이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행계획을 생성한다.

• 조인 순서 결정 시 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준

  • 양쪽 테이블에 인덱스가 모두 존재하는 경우
    -> 우선 순위가 높은 테이블을 선행 테이블(Driving Table)로 선택한다.

  • 한쪽에만 인덱스가 존재하는 경우
    -> 인덱스가 없는 테이블을 선행 테이블로 선택한다.

  • 모두 인덱스가 존재하지 않는 경우
    -> FROM절의 뒤에 나열된 테이블을 선행 테이블로 선택한다.

  • 우선순위가 동일하는 경우
    -> FROM 절에 나열된 테이블의 역순으로 선행 테이블을 선택한다.

1-2. 비용기반 옵티마이저(CBO, Cost Based Optimizer)

• SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.

• 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미한다.

• 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성되어 있다.

  질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈이다.

  대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈이다.
  - 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해서 생성된다.
  - 대안 계획의 생성이 너무 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린 수 있다.

  비용 예측기 : 대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈이다.
  - 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다.
  - 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확해야 한다.
  

• 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다.

• 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다.

• 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점이 있다.

2. 실행계획(Execution Plan)

• SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다.
• 구성하는 요소에는 조인 순서(Join Order), 조인 기법(Join Method), 액세스 기법(Access Method), 최적화 정보(Optimization Information), 연산(Operation) 등이 있다.

2-1. 실행계획 구성 요소

• 조인 순서(Join Order) : 조인작업을 수행할 때 참조하는 테이블의 순서이다.
• 조인 기법(Join Method) : 두 개의 테이블을 조인할 때 사용할 수 있는 방법이다.
• 액세스 기법(Access Method) : 하나의 테이블을 액세스할 때 사용할 수 있는 방법이다.
• 최적화 정보(Optimization Information) : 각 단계마다 예상되는 비용 사항을 표시한 것이다.
  • Cost : 상대적인 비용 정보
  • Card(Cardinality) : 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집합의 건수
  • Bytes : 결과 집합이 차지하는 메모리 양
• 연산(Operation) : 여러 가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업이다.

3. SQL 처리 흐름도(Access Flow Diagram)

• SQL의 내부적인 처리 절차를 시각적으로 표현한 도표(실행계획 시각화)이다.
• 조인 순서, 액세스 기법, 조인 기법 등을 표현할 수 있다.
• 액세스 건수, 조인 시도 건수, 테이블 액세스 건수, 성공 건수 등을 표시할 수 있다.