OPTIMIZER_INDEX_CACHING vs OPTIMIZER_INDEX_COST_ADJ

• Default : optimizer_index_caching=0, optimizer_index_cost_adj=100
  
  
• optimizer_index_caching : Buffer Cache에서 인덱스 블럭을 찾을 가능성 설정
  1. optimizer_index_caching = 0 : Buffer Cache에서 인덱스 블럭을 찾을 가능성은 0%
  2. optimizer_index_caching = 100 : Buffer Cache에서 인덱스 블럭을 찾을 가능성은 100%
     
     
• optimizer_index_cost_adj : 인덱스 액세스 비용 계산
  1. optimizer_index_cost_adj = 100 : 인덱스 액세스 비용을 보통의 비용대로 계산
  2. optimizer_index_cost_adj = 10 : 인덱스 액세스 비용의 1/10로 평가 하여 액서스 비용을 적게 사용.
  3. 이 값이 작음 : 인덱스를 경유한 테이블 액세스(? Single-Block I/O) 비용이 더 적게 평가되므로 더 많은 query가 인덱스 스캔을 수행
  4. 이 값이 큼 : 인덱스를 경유하지 않는 Full Tabe Scan(Multiblock I/O)의 액세스 비용이 더 적게 평가되어 Full Table Scan의 가능성이 커짐
     
     
• 주의 사항 : 두 파라미터 값을 alter session 을 통해 변경 후 plan 및 trace 확인 후 적절 값을 찾은 후 운영 시스템에 적용
  1. Optimizer의 예상 : Nested Loop join이나 In-List iterator access path가 나올 경우 참조테이블의 데이터를 매번 디스크에서 읽어온다는 가정을 하고 비용을 계산
  2. 실제 : DB Buffer Cache에 존재하는 블록을 읽을 확률이 더 높기 때문에 예상보다 훨씬 적은 수의 디스크 I/O가 발생하는 것이 일반적
     1. 이 파라미터의 적용여부에 따라 Nested Loop Join으로 풀려야 하는 query가 Sort Merge Join이나 Hash Join으로 선택될 가능성이 높아지게 수 있음
  3. 예상과 실제가 다른 이유 : optimizer의 Nested Loop join이나 In-List iterator에 대한 비용 산정은 가장 최악의 상황을 가정한 것
     1. nested loop join이나 In-List iteratior 수행시의 예측치와 실측치 간의 차이를 보정하기 위해 OPTIMIZER_INDEX_CACHING와 OPTIMIZER_INDEX_COST_ADJ 값의 조정이 필요함
        
        
• 적용 사례 : 기본값보다는 두 개의 파라미터를 통해 기존 sql 보다는 성능이 개선 됨
         (http://kr.forums.oracle.com/forums/thread.jspa?messageID=3703341&#3703341)
  
  
  
• 테스트
  • optimizer_index_cost_adj = 100(default), optimizer_index_caching 값을 조정
    
    그림 1) OIC와 실행 시간

-* optimizer_index_caching = 0(default), optimizer_index_cost_adj 값을 조정

그림 2) OICA와 실행 시간

-* optimizer_index_caching, optimizer_index_cost_adj 값을 조정

그림 3) OIC & OICA 와 실행 시간

문서에 대하여

• 최초작성자 : 박혜은
• 최초작성일 : 2009년 11월 19일
• 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.