Buffer Size

Beware the Incorrect Hit Ratio formula

There are 2 different hit ratio formulas being distributed at the moment

   Hit Ratio = Logical Reads / (Logical Reads + Physical Reads)

        and ...........

   Hit Ratio = Logical Reads - Physical Reads / Logical Reads

Both give incredibly different results. I believe sites that use bstat/estat and use the first formula are doing the wrong thing and may have hit ratio problems which their hit ratio won't indicate........

  Consider our Full Table Scan Test I performed

  There was 4000 physical reads and 5000 logical reads

  Formula 1 = 5000 / 9000 = 60% approx (the DBA guide says 60% aint too bad

                                          therefore Full table scans are good)

  Formula 2 = 1000 / 5000 = 20% (Not so good - actually terrible)

What value is the Hit Ratio anyway?

Our extensive testing has indicated that even with similar Hit Ratios (using the correct formula), there can be a dramatic difference in response times... In fact, long running jobs can improve by as much as 50% and OLTP generally improve by up to 15% when the buffer cache is increased from 4 Meg to 16 Meg even though the hit ratios are almost identical.Enlarging the Buffer Cache upwards continues to provide better response times, even when the hit ratio remains the same.

Sure there may a site out there that only uses the same piece of data once, but there is more to the buffer cache than hit ratio. Look at the bstat/estat figures, and the elapsed and CPU times to see what I mean.

This concept that reducing the buffer cache also reduces CPU time as the CPU waits for Disk I/O. ... How can this be? If you run TKPROF and check all times during a benchmark, you will see that overall, more CPU time is used per query. 100 users use 150 seconds CPU compared to 130 seconds with a larger buffer cache. So what if the CPU has to momentarily wait for disk I/O...... the disk I/O uses CPU and then the same buffer cache activity occurs anyway. Every user gets worse response and the overall CPU usage is higher with a smaller cache.

The only way I could see how more CPU could be used with a larger cache is if a long search takes place in the buffer to see if the data exists. This searching appears to be extremely efficient if you check the bstat/estat figures. Obviously if paging and swapping is occurring because of insufficient memory, the CPU activity will increase... this goes without saying.....

Mark Received on Mon May 24 1993 - 02:27:50 CEST