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부터 시스템 통계를 수집하는 기능이 도입

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

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

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

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