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에서 해결됨.