10g System statistics - single and multi

Hello,

I've been diagnosing some mis-used full table scans vs index access.

Part of my troubleshooting involved trying to use system statistics to force oracle to consider full table scans more expensive.

What i've discovered was something very strange. When I had both values to 1, my cost of a FTS would be ~1400. When I set multi to 2, the cost would go DOWN to ~1050. When I set it to 3, it would go back to ~1400.

This puzzled me a lot and I played arround with the values.

What I've discovered is that when SINGLE >=3D MULTI, then oracle uses N * SINGLE for full table scans some formula independant from the value of MULTI.

In a way, this makes sense, as it is not possible to have 1 read to be slower then MANY reads. But, this is an average value, thus it is quite possible (and likelly on many systems) that multi-block reads are faster then single block reads.

However Oracle doesn't handle this case at all. I find this to be a severe limitation. What do you think ?

This is 10.1.0.4 RAC/ASM test environment.

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

--
http://www.freelists.org/webpage/oracle-l