옵티마이저 원리

1. 옵티마이저

• 옵티마이저는 사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리경로를 선택해 주는 DBMS의 핵심엔진이다. 규칙기반 옵티마이저와 비용기반 옵티마이저가 있는데 오라클은 규칙기반 옵티마이저를 더 이상 지원을 하지 않는다.
• 규칙기반 옵티마이저(RBO)는 미리 정해 놓은 우선순위에 따라 액세스 경로를 평가하고 실행계획을 선택한다. 따라서 예측이 가능하고 일관성 있는 실행계획을 수립하지만 다양한 통계정보를 활용하지 못하기 때문에 CBO보다는 효율적이지 못하다
• 비용기반 옵티마이저(CBO)는 비용을 기반으로 최적화를 수행하는데 여러 통계정보를 기초로 예상 비용을 산정해서 가장 낮은 총비용을 실행계획으로 정한다. 
• 옵티마이저 모드로 선택할 수 있는 값은 5가지로 시스템, 세션, 쿼리 레벨 별로 변경 가능하다. RULE은 RBO 모드를 선택하는 것이고 ALL_ROWS는 쿼리 최종 결과집합을 끝까지 Fetch 하는 기준으로 가장 적게 사용하는 실행계획을 선택, FIRST_ROWS는 전체 결과 집합 중 일부 로우만 Fetch 하다가 멈추는 것을 전제로 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택, FIRST_ROWS_N은 사용자가 처음 N개 로우만 Fetch 하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택, CHOOSE는 액세스 되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO의 ALL_ROWS 모드를 어느 테이블에도 통계정보가 없으면 RBO를 선택한다. 
• 애플리케이션 특성상 확실히 FIRST_ROWS가 적합하다는 판단이 서지 않는다면 ALL_ROWS를 기본 모드로 선택하고, 필요한 쿼리 또는 세션 레벨에서 FIRST_ROWS 모드로 전환하는 것을 추천한다.

2. 옵티마이저 행동에 영향을 미치는 요소

• 결과가 같더라도 SQL을 어떤 형태로 작성했고 어떤 연산자를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있다.
• SQL을 동일하게 작성했다고 하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 어떻게 구성했는지에 따라 실행계획이 달라진다.
• PK, FK, Check, Not Null 같은 제약 설정에 따라 옵티마이저가 실행계획을 추가 또는 생략등을 할 수 있다.
• 옵티마이저 힌트는 문법적으로 맞지 않거나 잘못된 참조 사용, 의미적으로 맞지 않거나 불가능한 사용, 버그가 아니라면 아주 강력하게 실행계획을 변경 시킬 수 있다.
• 통계정보에 바탕하여 총 비용이 재일 적게 드는 실행계획을 선택함으로 통계정보 또한 중요하다.
• 옵티마이저의 관련 파라미터나 DBMS 버전과 종류에 따라서도 실행계획이 달라진다.

3. 옵티마이저의 한계

• 사용자가 올바르지 않은 옵티마이징 팩터를 제공하거나 부정확한 통계를 수집하면 잘못된 선택을 할 가능성이 높다. 또한 히스토리그램의 버킷 개수가 254개만 허용되기 때문에 한계점이 명확하다. 
• 바인드 변수 사용 시 균등분포 가정하에 실행계획을 만들며 조건절 칼럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.  또한 Single Block I/O 와 Multi Block I/O 비용을 같게 평가하며 비용은 단순히 I/O Call 횟수를 의미한다. 
• CBO라고 하더라도 통계를 사용하여 비용을 계산하긴 하지만 부분적으로는 RBO 규칙에 의존한다.

4. 통계정보 1

• 옵티마이저가 참조하는 통계정보는 테이블, 인덱스, 컬럼(히스토그램), 시스템 통계를 참조한다.

5. 카디널리티

•  선택도는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율이다. 선택도를 가지고 카디널리티를 구해 비용을 구하고 인덱스 사용 여부, 조인 순서와 방법 등을 결정하기 때문에 선택도는 최적의 실행계획을 수립하는데 중요한 요인이다.
• 선택도 = 조건절에서 요청한 값 범위 / 전체 값 범위 이다.
• 카디널리티는 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수이다. 
• 카디널리티 = 총 로우 수 * 선택도이다.

6. 히스토그램

• 도수분포 히스토그램은 값별로 빈도수를 저장하는 히스토그램이다. 사용자가 요청한 버킷 개수가 칼럼이 가진 값의 수보다 많거나 같은때 사용되며, 최대 254개의 버킷만 허용하며 그 이상이면 도수분포 히스토그램은 사용할 수 없다.
• 높이균형 히스토그램은 컬럼이 가진 값의 수보다 적은 버킷을 요청할 때 만들어지며 버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 값의 담당한다. 값의 수가 254개를 넘으면 무조건 높이균형 히스토그램이 만들어진다.

7. 비용

• 오라클 옵티마이저가 사용하는 비용 모델은 I/O 비용 모델과 CPU 비용 모델이 있고 I/O 비용 모델은 예상되는 I/O 요청 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가하고 CPU 비용 모델은 I/O 비용 모델 + 시간 개념음 더해 비용을 산정한다.
• I/O 비용 모델은 Single Block I/O 와 Multi Block I/O 비용을 같게 평가하고 캐싱 효과를 전혀 고려하지 않기 때문에 optimizer_index_cost_adj 파리미터를 조정하여 인덱스 탐색 비용을 조정할 수 있고 optimizer_index_caching 파라미터를 설정하여 NL 조인에서 inner 쪽으로 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 것이다. optimizer_index_caching 값이 높을수록 옵티마이저는 인덱스를 이용한 NL 조인을 선호하게 된다.
• CPU 비용 모델에서는 Single Block I/O과 Multi Block I/O의 요청 횟수/소요시간, 쿼리 수행에 필요한 CPU 사이클 수, 초당 처리할 수 있는 CPU 사이클 수를 가지고 비용을 계산한다.

8. 통계정보 2

• 통계정보를 수집할 때 고려해야 할 사항은 부하가 없는 시간대에 가능한 빠르게 수집을 해야 하고 가능한 적은 양의 데이터를 읽어야 하며 전수 검사할 때의 통계치에 근접할 정도로 정확해야 하고 매번 통계치가 바뀌지 않게 안정적이어야 한다. 또한 통계정보 변화 때문에 성능에 심각한 문제가 발생할 수도 있기 때문에 가장 안정적이었던 최근 통계정보를 항상 백업해 두어야 한다.
• 통계를 수집할 필요가 없는 오프젝트에 대해서는 Lock 옵션으로 통계정보를 고정할 수도 있고 일부 핵심 프로그램에 대해선 힌트를 사용해 실행계획을 고정시킬 수도 있다. 
• 통계정보 수집을 위해서 dbms_stats 패키지를 사용하자.
• 칼럼 히스토그램이 있으면 더 나은 실행계획을 수립하는 데 도움이 되지만 비용과 관리가 어렵기 때문에 컬럼 데이터 분포가 균일하거나 Unique 하고 항상 등치조건으로만 검색되는 칼럼이나 바인드 변수로 검색되는 칼럼에는 히스토리그램이 불필요하다.

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 2 -