Controlling Index Scan Cost (by jongmali)[2009.04.30]
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에 관련된 모든 비용이 줄어든다.
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가 증가할 수 있다.