This is what "they" are telling me. If I go from a hit ratio on the buffer cache from 98% to 99%, I have inversely lowered by direct disk reads from 2% to 1%, thereby cutting them in half. 1 is 1/2 of 2. See the reasoning? Please help.
cache hit ratio = 100 * (1- PIO/LIO)
And in order to do so, you either
a) decreased physical IO (PIO) - in which case this is good (Logical - LIO stayed the same, you decreased physical IO). Say you went from
pio = 20,000, lio = 1,000,000 -> hit ratio = 98%
pio = 10,000, lio = 1,000,000 -> hit ratio = 99%
I agree, good. But I'd be looking for the decrease in pio and the constant LIO reading - not a cache hit move
b) did not decrease physical IO - you INCREASED LIO, this is bad.
pio = 20,000, lio = 1,000,000 -> hit ratio = 98%
pio = 20,000, lio = 2,000,000 -> hit ratio = 99%
I'd be really upset at you right now.
c) increased both PIO and LIO
pio = 20,000, lio = 1,000,000 -> hit ratio = 98%
pio = 40,000, lio = 4,000,000 -> hit ratio = 99%
now I'd be really upset - more than before.
I don't see any way to say "by increasing the hit ratio, we decreased PIO". PIO could
a) go up
b) go down
c) stay the same!!!
Want a really good hit ratio, do what this developer might want to do (use rule hint to make everything INDEX HAPPY)
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>
we went from a 50% hit: pio = 23380 lio = 59350
to a 98% hit: pio = 1154555 lio = 121367981
for the same query - affecting their desired "high hit ratio", as the expense of overall performance.
What you want to do is NOT get the buffer cache hit ratio to "N", you want to do less work. When you are doing the least amount of work you can - you know now the hit ratio for your database - which will vary widely (warehouse, reporting, transactional - all different).
The hit ratio is like a finger print, it is a metric. When the metric moves - something has changed. It moving from 98 to 99% could mean one of three things:
a) things are better
b) things are much worse
c) things haven't really changed at all as far as anyone is concerned.
So, moving the metric - big deal.
Doing LESS WORK - priceless.
Do you see the flaw in the reasoning now?
It is not "a => b" therefore "b => a" situation.
it is true that if you lower disk reads from 2% to 1% of the hit ratio, that the hit ratio will go from 98% to 99%.
It is NOT true that if you increase the hit ratio from 98% to 99% that the physical IO will have had to of gone down. You might have introduced a wickedly BAD query into the system (see link) and achieved this "goal"