by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Controlling Index Scan Cost




Controlling Index Scan Cost

  • Oracle 성능개선 작업을 하다보면, Index Scan과 Table Full Scan의 비용을 수동으로 제어할 필요성이 생기는데, 이러한 필요성으로 인하여, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ Parameter가 제공됨

optimizer_index_caching

  • OPTIMIZER_INDEX_CACHING parameter는 Index가 Memory에 Caching되어 있을 확률을 의미
  • Default는 0 (즉, Oracle은 Index가 Memory에 Caching되어 있지 않다는 가정하에 Cost를 계산)
  • Index Scan의 경우 Physical Reads보다는 Logical Reads가 발생할 확률이 높으므로 이 값을 조정하여 사용.
    • Index Scan을 통해 읽어들인 Block은 LRU List의 중간위치에 삽입되고, Table Scan을 통해 읽어들인 Block은 대부분 LRU List의 마지막에 삽입되므로 Index Scan을 통해 읽어들인 Block은 Buffer Cache에서 밀려날 확률이 상대적으로 적음
  • 단, Nested Loops Join과 In List Operation의 Cost에만 영향을 준다.
  • (예제) 같은 SQL을 힌트를 통해, Nested Loops Join과 Index Range Scan으로 유도한다. Parameter값을 변경하며 Cost값의 변화를 관찰한다.(page 138~)
    => 해당 Parameter값을 증가할수록, Nested Loops Join단계의 Cost가 줄어드나, Nested Loops Join이 없는 실행계획에는 영향을 미치지 않는다.

optimizer_index_cost_adj

  • OPTIMIZER_INDEX_COST_ADJ parameter는 Index Cost를 조정하는 역할을 함
  • Default는 100(즉, Index Scan Cost를 기본공식 그대로 계산하겠다는 의미)
  • 해당 parameter값을 감소시킬수록, Index Scan이 선택할 확률이 높아짐.
  • 이 parameter의 목적은 Index Scan Cost를 낮춤으로써, Single Block I/O와 MultiBlock I/O Cost간의 불균형을 해소하는 것이다.
  • (예제) 같은 SQL문을 Parameter값을 변경하며 Cost값의 변화를 관찰한다.(page 143~)
    => 해당 Parameter값을 낮추면, Index에 관련된 모든 비용이 줄어든다.

Parameter vs. System Statistics

  • optimizer_index_caching Parameter와 optimizer_index_cost_adj의 최적값은 얼마일까?
    • OLTP System에서 추천되는 값
      • OPTIMIZER_INDEX_CACHING : 80~95 => 약 90%정도는 Memory에서 Caching
      • OPTIMIZER_INDEX_COST_ADJ : 5~10 => Single Block I/O가 Multi Block I/O Cost에 비해 1/10~1/20이라고 가정
  • System Statistics의 값 sreadtim값과 mreadtim이 두 Parameter를 대신한다.
  • System Statistics의 특징(1장 참고)
    • 실제 System 성능에 기반하여 Single Block I/O와 Multi Block I/O의 Cost를 계산
    • OPTIMIZER_INDEX_COST_ADJ Parameter가 Single Block I/O Cost를 낮추는 대신 System Statistics는 Multi Block I/O Cost를 높임
  • DB_FILE_MULTIBLOCK_READ_COUNT가 커질수록 Multi Block I/O의 Cost가 줄어드나, 이로인해 Table Scan의 Cost가 변경되어버리는 부작용을 가짐. 하지만 System Statistics의 mbrc값이 존재하면, 해당 Parameter는 Cost 계산에 사용되지 않음

Bug5578791

/* page 148~ 참조*/
  • Nested Loops Join의 Cost를 줄이고자 OPTIMIZER_INDEX_CACHING Parameter를 사용하는 경우, OPTIMIZER_INDEX_COST_ADJ Parameter를 같이 사용하면 오히려 Cost가 증가할 수 있다.
  • Oracle 9i R2에서부터 발생가능하며, 11g에서 해결됨.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.