메뉴 건너뛰기

bysql.net

3. 옵티마이저의 한계

2011.04.19 22:32

멋진넘 조회 수:7855

03.업티마이저의 한계

자동 튜닝 옵티마이저(Automatic Tuning Optimizer)
- Offline Optimizer라고 불림
- 'SQL Tuning Advisor'라고 불리는 서버 유틸리티를 이용하면 된다.
- Oracle Package 활용하는 방법(http://www.gurubee.net/pages/viewpage.action?pageId=6259450) 참조
- 기타 Tool(SQLGate...)에서도 동일(유사)한 기능들 제공함(별도 설명)

(1)부족한 옵티마이징 팩터
- Garbage In, Garbage Out!!!

(2)부정확한 통계
- 현실적인 어려움으로 100% 정확안 통계유지의 어려움
- 제약으로 인행 샘플링 방식으로 통계를 수집 ▶ 실제 데이터와 불일치 발생

(3)히스토그램의 한계
- 부정확한 통계의 연장으로, 히스토그램 버킷 개수로 254개까지만 허용된다는 점
  이로 인해 컬럼의 Distinct Value 개수가 그 이상이면 도느 값별로 빈도수를 기록할 수 없다
 
(4)Bind 변수 사용시 균등분포 가정(★★★)
- 조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산한다.
  이는 옵티마이저가 가장 극복하기 어려운 난제임
  eg)SELECT * FROM DEPAMENTS WHERE DEPARTMENT_ID = ?(OR :V0001)
- Bind Variable Peeking 및 적응적 커서 공유(Adaptive Cursor Sharing)으로 실마리가 보이는 듯하나
  아직 완벽하게 문제를 해결했다고 보기 어렵다.
  (http://www.gurubee.net/pages/viewpage.action?pageId=4948370) 참조

(5)결합 선택도 산정의 어려움
- SELECT * FROM 사원 WHERE 직급 = '부장' AND 연봉 >= 5000;
  조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.
  즉, Case By Case를 산정하기가 힘듬
- 해결방안 2가지
  Oracle 9i의 동적 샘플링, 11g의 기능 6절 5항에서 별도 언급

(6)비현실적인 가정
- 동일한 비용산정 : Single Block I/O와 Multiblock I/O-
- 다른 세션이나 쿼리문에 의하여 버퍼캐시에 이미 저장된 데이터 블록은 없다고 가정한다.
  DW 환경에서 적합한 가정이다.
- 8i에서는 2개의 파라메터를 사용한다.(7절에서 설명 예정)
 
  ●optimizer_index_caching,
  이 파라메터는 Nested Loops 조인이나 IN-List 탐침으로 수행되어 인덱스가 반복해서 랜덤 액세스를 할 때
  인덱스 블록들이 버퍼에 캐쉬되어 있을 확률을 나타낸다. 이것은 곧 랜덤 액세스에 대한 부하가 감소되는 것을
  의미하므로 옵티마이져가 Nested Loops 조인이나 IN-List 탐침으로 실행계획을 수립하는 경향이 증가한다.
  기본값은 0으로 지정되어 있다.
  예를 들어 100으로 설정할 경우 SQL 조건에 따라 읽어들여야 하는 인덱스 블록이 100% DB Buffer Cache에서
  찾아질 것을 가정하는 것이고 0으로 설정할 경우 Buffer Cache에서 찾아질 가능성이 0이라고 가정하는 것입니다.
  만약 랜덤 액세스가 많이 발생하는 환경에서 반복 액세스가 일어나는 인덱스가 적은 블록을 가지고 있거나
  특정한 범위를 주로 액세스하여 재사용률이 매우 높다면 이 값을 증가시켜 주는 것이 좋다.
  그러나 이것은 비용의 조정을 의미하기 때문에 옵티마이저에게 커다란 영향을 미칠 수 있으므로 주의하여야 한다.

  ●optimizer_index_cost_adj
  이 파라미터는 인덱스 탐색비용에 대한 평가를 조정하기 위해 사용하는 것으로서,
  디폴트 설정값 100은 인덱스에 대한 액세스 비용이 보통의 비용모델에 근거해서 평가될 것이고,
  10으로 설정한다면 보통의 인덱스 액세스 비용의 1/10로 평가될 것임을 의미함.
  따라서 이 값이 작을수록 인덱스를 경유한 테이블 액세스(a Single-Block I/O) 비용이 더 적게 평가될 것이고,
  클수록 인덱스를 경유하지 않는 Full Tabe Scan(a Multiblock I/O)의 액세스 비용이 더 적게 평가됨.
  바꾸어 말하면, 이 파라미터는 Single-Block I/O와 Multiblock I/O에 대한 상대적인 평가로 해석할 수 있고,
  따라서 테이블 데이터 블록이 평균적으로 얼마만큼 캐싱되어 있는지에 대한 의미로 해석할 수도 있음.

  OPTIMIZER_INDEX_CACHING과 OPTIMIZER_INDEX_COST_ADJ 요약
  => 기본값인 OPTIMIZER_INDEX_CACHING = 0과 OPTIMIZER_INDEX_COST_ADJ = 100 :
     이들은 일반적으로 데이터 웨어하우스/보고용 시스템에 적합.
  => OPTIMIZER_INDEX_CACHING = 90과 OPTIMIZER_INDEX_COST_ADJ = 25 :
     이들은 일반적으로 트랜잭션/OLTP 시스템에 적합
  (http://ndba.egloos.com/2821627) 참고

(7)규칙에 의존하는 CBO
- 아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다.
- 예를 들어, DBLink를 사용한 원격 테이블이나 External 테이블(File 외부테이블)에 대해서는
  카디널리티, 평균 행 길이, 블록 수, 그리고 각종 인덱스 관련 통계항목들에 대해 고정된 상수 값을 사용
- 1절 '(4)옵티마이저 모드'에서 설명함
  옵티마이저 모드를 FIRST_ROWS로 설정했을 때 ORDER BY 소트를 대체할 인덱스가 있으면
  무조건 인덱스를 사용하는 것도 좋은 예(P42, P196, P367)
- 다음 장(4장)에서 설명할 Heuristic 쿼리 변환도 규칙에 의존하는 CBO
  9i까지 옵티마이저는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 Merging하려고 시도
  ▶10g에선 대부분 쿼리 변환이 비용기반(Cost Based)로 개선됨
  ※Heuristic 쿼리 변환
    사전적 의미에서 Heuristic은 문제 해결에 있어 그 노력을 최소화하기 위해 사용되는 과정으로 정의됨
    Heuristic 쿼리 변환은 옵티마이저가 성능을 향상기키기 위해 Costing(비용계산) 과정을 생략하고
    특정 Rule을 적용하여 최적화를 진행하겠다는 의미임

 
  ▣알파벳순 인덱스 선택규칙
  CREATE TABLE T
  AS
  SELECT ROWNUM A, ROWNUM B FROM DUAL
  CONNECT BY LEVEL <= 10000;
 
  CREATE INDEX T_X01 ON T(A);
 
  CREATE INDEX T_X02 ON T(B);
 
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T');

  SELECT * FROM T WHERE A= 1 AND B = 1;
  -------------------------------------------------------------------------------------
  | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |       |     1 |     8 |     2   (0)| 00:00:01 |
  |*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     8 |     2   (0)| 00:00:01 |
  |*  2 |   INDEX RANGE SCAN          | T_X01 |     1 |       |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     1 - filter("B"=1)
     2 - access("A"=1)

  위와 같은 조건절을 만났을 때 T_X01, T_X02 둘 중 어느 것을 선택하든 쿼리 수행 비용은 같다.
  이때 옵티마이저가 T_X01 인덱스를 선택한 판단 기준은, 허무하게도 인덱스명의 알파벳 순이다.

  ALTER INDEX T_X01 RENAME TO T_X03;
  SELECT * FROM T WHERE A= 1 AND B = 1;
  -------------------------------------------------------------------------------------
  | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |       |     1 |     8 |     2   (0)| 00:00:01 |
  |*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     8 |     2   (0)| 00:00:01 |
  |*  2 |   INDEX RANGE SCAN          | T_X02 |     1 |       |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     1 - filter("A"=1)
     2 - access("B"=1)
    
  이로 인한 CBO 옵티마이저의 발생되는 문제점
  eg)테이블명_PK, 테이블명_N01, 테이블명_N02와 같은 식의 인덱스 명명 규칙
     ORDER_PK  : 고객번호 + 주문일자
     ORDER_N01 : 고객번호 + 배송일자
     ▶위의 상황에선 인덱스의 예쌍비용이 똑같을 수 있다
    
     WHERE 고객번호 = :cost_no
       AND 주문일자 = :ord_dt
     ▶ORDER_PK 인덱스를 사용해야 하나, ORDER_N01를 사용할 가능성이 높음
       엄청난 Filter 처리 및 테이블 Random Access 유발
       인덱스 명명 규칙을 신중하게 결정하라!!! 
    

(8)하드웨어 성능 특성 
- 하드웨어의 사양 및 어플리케이션 특성에 따라 실행계획이 달라질 수 있다.