Wednesday, September 23, 2009

Why Oracle is Not Aware of Space Below HWM?

Just thinking aloud....

High water mark (HWM)is the boundary between ever used and never used space in a segment. Oracle always reads up to the HWM during full table scan, no matter if the space is empty.

Why CBO doesn't know this fact?

It should just be a matter of adding yet another statistic in the arsenal of CBO and should be gathered up during stat gathering job?

Or am I missing something here....


Unknown said...

the FTS cost is calculated as

FTS cost = Blocks below HWM / MBRC * MREADTIM / SREADTIM

thus HWM is indirectly in the formula

Fahd Mirza said...

Thanks for your comment.

In the formula of FTS Cost, there is no mention of *empty* blocks below HWM.