03. 옵티마이저의 한계

자동 튜닝 옵티마이저

 
 - 시간에 구애받지 않고 충분한 시간 동안 동적 샘플링을 포함한 여러 기법을 활용해 튜닝을 실시하고 사용자에게 권고안을 제시

 - 자동 튜닝 옵티마이저(Auto Tuning Optimizer)를 '오프라인(offline) 옵티마이저'  <->  일반적인 옵티마이저는 '온라인(Online)' 또는 '런타임(Runtime)'  옵티마이저
 - 통계를 분석하고 SQL 프로파일링(Profiling) 을 실시하며， 액세스 경로 및 SQL 구조 분석을 통해 SQL 튜닝을 실시
 - 튜닝 모드에서 작동하는 이 옵티마이저에게는 한 문장을 튜닝하는 데에 런타임 옵티마이저보다 훨씬 긴 시간이 주어진다. 넉넉한 시간 동안 풍부한 정보를 수집/활용함으로써 데이터 액세스 비용과 카디널리티를 보다 정확하게 계산 

   (Ex) 동적 생플링을 통해 부가적인 정보를 수집하고，심지어 부분적인 실행을 통해 예측치(조인 카디널리티 등)를 검증함으로써 잘못된 정보를 조정히는 테크닉을 사용 
        그렇게 각 SQL 단위로 수집된 프로파일(상관관계 있는 컬럼 간 결합 분포, 조인에 의한 테이블 간 상관관계 동 해당 SQL만을 위한 보조적인 통계정보)을 데이터 덕셔너리에 영구 저장해 런타임 옵티마이저가 참조할 수 있도록 하는 기능도 제공

 -  자동 튜닝 옵티마이저 기능을 활용하려면 'SQL Tuning Advisor' 라 불리는 서버 유틸리티를 이용, SQL 입력하면 내부적으로 자동 튜닝 옵티마이저를 호출해 SQL 분석, 완료되면 SQL 성능을 높이기 위해 사용자가 취해야 할 조치사항들을 보고서 형태로 출력

(1) 부족한 옵티마이징 팩터

옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이정 팩터를 제공히는 것은 결국 사람의 몫
적절한 인덱스도 제공하지 않은 채 옵티마이저가 고성능 실행계획을 수립해 주기를 기대해선 안 된다.
이미 존재하는 길을 찾아줄 뿐 옵티마이저가 없는 길까지 만들어 낼 수는 없는 일 아닌가.

(2) 부정확한통계

정보 비용이. 많은 정보를 수집/보관한다면 그만큼 좋은 결과를 낼 수 있겠지만 현실적으로 100% 정확한 통계를 유지하기는 불가능
현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다보니 실제 데이터와 불일치가 발생

샘플링 방식으로 통계를 수집할 때는 정확성 문제와 더불어 안정성도 고려, 분포가 균등하지 않은 컬럼이라면 수집할 때마다 통계치가 바뀔 수 있어 실행계획을 불안정
통계 수집 주기도 매우 중요, 데이터가 아주 많이 변경되거나 새로 입력됐을 때(2차 가공을 위한 임시 테이블에 흔히 발생) 곧바로 통계를 재수집해 주지 않는다면 옵티마이저가 잘못된 선택

(3) 히스토그램의 한계

부정확한 통계의 연장선으로，
히스토그램 버킷 개수로 254 개까지만 허용, 컬럼의 Distinct Value 개수가 그 이상이면 모든 값 별로 빈도수를 기록해 둘 수 없고, 높이균형(heighl-balanced) 히스토그램을 사용하게 되므로 발생 빈도가 낮은 값들(non-popular value)에 대한 정확한 분포를 반영할 수 없다.
버킷 개수를 많을 수록 정확한 카디널리티를 구하는 데 도움이 되겠지만, 정보를 수집하는 데 시간과 저장 공간 때문에 생기는 어쩔 수 없는 제약사항

(4) 바인드 변수 사용 시 균등분포 가정

아무리 정확한 컬럼 히스토그램을 보유하더라도 , 조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정 (옵티마이저가 가장 극복하기 어려운 난제)
특히, OLTP 환경에선 라이브러리 캐시 부하를 피하기 위해 바인드 변수를 적극 사용하는 것이 필수 권고사항과 상충, 11g에서 적응적 커서 공유 기법을 도입하면서 문제 해결의 시도

(5) 결합 선택도 산정의 어려움

조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.


  "직급이 {부장， 과장， 대리， 사원}의 집합이고 각각 25%의 비중 "
  "전체 사원이 1,000명이고 히스토그램상 '연봉 >= 5000' 조건에 부합하는 사원 비중이 10%"

   select * from 사원 where 직급 = '부장' and 연봉 >= 5000 ;

   - 쿼리 조건에 해당히는 사원 수를 25(=1000XO.25 X 0.1)명으로 추정


   (1)직급과 연봉 상관관계 : 만약 모든 부장의 연봉이 5,000만원 이상이라면 실제 위 쿼리 결과는 250(=1,000XO.25X1)건
   (2)사원 급여와 상여금 간에도 상관관계

카디널리티가 잘못 계산되면 다른 집합과 여러번 조인을 거치는 동안 카디널리티는 점점 더 부정확해지고 궁극적으로 옵티마이저가 잘못된 실행계획을 수립
모든 컬럼간 상관관계와 결합 선택도를 미리 저장은 불가능, 테이블 컬럼이 많을수록 잠재적인 컬럼 조합의 수는 기하급수적으로 증가

대안으로 오라클은 9i부터 동적 샘플링을 통해 해결하려고 시도
소량의 데이터 샘플링을 통해 where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구하는 기능으로서, 동적 샘플링 레벨을 4 이상으로 설정할 때만 작동
11g부터는 사용자가 지정한 컬럼들에 대해 결합 선택도를 미리 수집해 두는 기능을 제공

(6) 비현실적인 가정

(1) Single Block I/O 와 Multiblock I/O 비용을 같게 평가

I/O 비용 모델에서의 비용은 단순히 I/O Call 횟수(논리 또는 물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수)를 의미하며, 옵티마이저가 예상할 때 I/O Call 횟수가 가장 적은 실행계획을 선택
상식적으로 생각해 한 번의 I/O Call로써 한 블록을 읽는 비용이 여러 블록을 읽는 비용보다 낮은 데도 옵티마이저는 이 둘을 구분하지 않고 동일한 하나의 시스템 Call로 간주

(2) 캐싱 효과를 고려하지 않음

다른 세션이나 다른 쿼리문에 의해 데이터 블록들이 이미 버퍼 캐시에 캐싱돼 있을 가능성을 배제
비용을 평가할 때 디스크 I/O Call 횟수만을 더한다, 메모리 자원에 대한 최악의 상황을 가정하고 비용을 산정하는것인데，이는 DW 환경에나 적합한 가정

(3) 비현실적 가정을 보정하기 위한 파마미터

optimizer_index_caching
optimizer_index_cost_adj

(7) 규칙에 의존하는 CBO

부분적으로는 규칙에 의존, 원격(remote) 테이블이나 External 테이블에 대해서는 카디널리티，평균행 길이，블록 수，그리고 각종 인덱스 관련 통계 항목들에 대해 고정된 상수 값을 사용
휴리스틱(Heuristic) 쿼리 변환 : 9i까지 옵티마이저는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 Merging 하려고 시도

알파뱃순 인텍스 선택 규칙

두 대안 인덱스의 예상 비용이 같을 때 알파벳 순에서 앞선 것을 선택, 물리 데이터베이스 설계 단계에서 인덱스 명명 규칙을 정할 때 매우 중요

적어도 Unique한 조건으로 PK 인덱스를 액세스해야 하는 상황에서 이런 일이 발생하지 않게하려면 인덱스 명명 규칙을 PK, X01, X02 와 같은 식으로
예상 비용이 같으면 오브젝트ID가 큰 것을 우선적으로 선택

(8) 하드웨어 성능 특성

기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양과 실제 운영 시스템의 하드웨어 사양이 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이
애플리케이션 특성(I/O 패턴, 부하 정도 등)에 의해서도 하드웨어 성능은 상이

오라클 9i부터 시스템 통계를 수집하는 기능이 도입

동적 실시간 최적화(Dynamic Runtime Optimizations)

 
  - 모든 데이터베이스의 작업 부하는 매우 가변적이고, 하드웨어 성능 특성을 반영한 실행계획을 수립하더라도 쿼리 수행 당시 시스댐 부하 정도에 따라 최적이 아닐 수 있다. 
  - 정적인 통계정보와 옵티마이저 모델로는 이런 한계점을 극복하기 어려우므로 시스템 부하에 따라 실행전략을 동적으로 조정하는 최적화 기법이 도입 

  - 쿼리가 수행되는 시점의 시스템 상태에 따라 하드웨어 리소스(CPU와 메모리)를 적절히 배분 

  - 대표적으로，시스템 부하 정도에 따라 병렬 쿼리의 프로세스 개수를 오라클이 동적으로 조절해 주는 기능
  - 또한 9i부터 PGA 메모리 크기를 자동으로 조절, 10g부터는 SGA를 구성히는 서브 메모리 영역을 자동으로 조절 
  - 이런 기능을 통해 고정된 하드웨어 리소스와 정해진 시간 동안，개별 SQL이 아닌 전체 SQL의 처리량을 극대화 

  - 쿼리 최적화가 단일 SQL문 성능을 최적화하는 데 초점을 맞추는 반면， 동적 실시간 최적회는 수많은 SQL이 동시에 수행되는 환경에서 시스템 전체 최적화를 이루는 데 초점