Oracle Optimizer

 

옵티마이저의 기본 기능

 

1. RBO와 CBO의 차이

RBO (Rule Based Optimizer) 규칙 기반 옵티마이저

•  Oracle 9i까지 사용하던 방식으로 Oracle 10g서 부터는 지원되지 않습니다.

RBO의 장점

1. 기능이 한정적이고 쉽기 때문에 배우기가 쉽다.
2. 규칙을 외어버리면 튜닝이 쉽다.
3. 오래전부터 사용된 기능이기 때문에 기존 지식을 사용 할 수 있다.
4. 운영중에 SQL 실행계획이 거의 변하는 일이 없기 때문에 액세스 패스가 변경되어 성능이 떨어지는 장애가 발생하지 않는다.

RBO의 단점

1. 데이터 변동을 따라가지 못한다.
2. SQL문 작성시 항목순서나 인덱스 만드는 순서가 중요하기 때문에 코드의 수정이나 인덱스 관리의 문제가 원인이 되어 성능에 영향을 미치는 경우가 많다.
3. 버전이 올라가면서 강화된 옵티마이저의 여러 기능 혜택을 받을수 없다.

CBO (Cost Based Optimizer) 비용 기반 옵티마이저

• 내부적으로 통계정보를 참조하며 액세스 패스를 유연하게 최적화하는 기능.
• CBO는 ANALYZE(통계수집)을 할 필요가 있으며, 통계를 최저한만 수집해서는 적절한 실행 계획을 얻지 못한다.

 

2. SQL문 처리에서 옵티마이저의 역할

 SQL문의 흐름

• SQL문의 처리에는 소프트 파스와 하드 파스 두가지 방법이 있다.
• 옵티마이저가 관여하는 경우와 관여하지 않는 경우로 나뉜다.

 소프트 파스

• 실행한 SQL문은 파서에 의해 파스(Parse) 된다.
• 파서는 SQL문을 컴포넌트 단위로 분해하여 문법적으로 틀리지 않았는지를 확인.
• SGA에 캐시 되어 있는지를 조사. 캐시되어 있는 경우 SQL문을 즉시 실행가능

 하드 파스

• SGA에 캐시가 없으면 SQL은 처음 실행되었거나 SGA에서 캐시 아웃된 상태. 이럴때 하드 파스 (Hard Parse)가 일어난다.
• 하드 파스의 결과물로 옵티마이저가 ‘쿼리 실행 계획’을 만듬.
• 실행 계획을 로우 소스 생성기가 받은후, 필요한 데이터 구조를 생성.
• 로우 소스 생성기의 결과물을 이용해 SQL을 실행.
• DDL이나 UPDATE의 경우 실행 단계에서 작업이 완료되나, SELECT는 이후 Fetch 처리를 수행.

 

3. 옵티마이저가 수행하는 쿼리

1) 옵티마이저가 가장 먼저 실행하는 처리는 쿼리의 변환(변형)

• • 작성된 SQL문을 다양한 형태로 변형한 후, 선택 가능한 실행 계획을 늘릭 위해 수행.

2) 에스티메이터가 변환된 쿼리를 이용해 기초 비용을 예측하고 플랜 생성기가 실행 계획의 생성한 후, 다시 에스티메이터가 비용을 계산한다.

• •  계획의 비용을 예측할때에는 데이터 딕셔너리에 보관된 옵티마이저 통계를 이용.
  •  통계를 사전에 수집하지 않은 경우에는 다이내믹 샘플리을 수행하거나, 내부의 기본값이 사용되는 경우도 있음.

※ 옵티마이저 통계에 포함된 정보

테이블 통계

• 건수
• 블록 수
• 평균 행 길이

칼럼 통계

• NDV(Number of distinct Value): 칼럼값의 종류 (Distinct count)
• 칼럼 내의 NULL 수
• 데이터 분포 (최댓값/최소값/히스토그램)

인덱스 통계

• 리프 블록 수
• 레벨 (트리의 깊이)
• 클러스터링 팩터

시스템 통계

• I/O 성능
• CPU 성능