Re: low buffer_hit_rate !!!!!

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/12/23
Message-ID: <349f80fc.6143579@www.sigov.si>#1/1

On Mon, 22 Dec 1997 16:00:59 -0500, Joseph Sumalbag <joseph_sumalbag_at_bose.com> wrote:

>Hi Timo .
>A full table scan fills up the cache , and loads the data
>buffers. Thereby if you have a full table scan there is a
>posibility that you should be getting more hits on
>the buffer since the data is already there ...(you did a full
>table scan remember) .....Provided of course that you have
>adequate large buffers to accompany the data and they are not
>aged out ....
>
>Performance will show a higher hit ratio but , since you are
>doing full table scans then the response is still slow ..

I thing this is not exactly the way Oracle behaves. When reading db blocks as result of large table scan Oracle will first load a predetermined number of db blocks into buffers. Then if there is no free buffers in the cache available for subsequent db blocks in the same table scan it will immediately age out those buffers it just filled (remember, they were put in the LRU end of the buffer list)! That way it avoids flushing the entire db buffer cache when performing large table scans.

So if you perform the same large table scan twice it is very unlikely the data will be found in the cache in the second scan and the hit ratio will deffinitely suffer.

>I wonder what you meant when you say that increasing the
>db_block_buffers size wouldn't affect the buffer hit ratio when
>you are doing a full table scan ? Please enlighten me .

Indeed, if you don't have an extremely large amount of memory available and your application performs lots of table scans I belive it is unlikely you'll get much better hit ratio by increasing number of db cache buffers. Of course, there must be some treshold beyond which the hit ratio will start to improve noticebly, it is just a question if you can afford such amount of buffer cache. The best way to find out this is to set init parameter DB_BLOCK_LRU_EXTEND_STATISTICS to a number of buffers you can still afford and after a day of normal db activity to analyze the results of the statistics collected in SYS.X$KCBRBH. That way you can determine if the increased number of buffers will realy help and if maybe even just a subset of this amount of buffers will return similar result. Note however that this kind of statistics collection brings a substantial overhead and that your users may not be too happy during your test.

>What I though was happening is Angelica was not getting the
>sampling correctly ... She should let the database run for a
>while before getting the sample..
>
>She could also be getting the wrong parameter file as she
>restarts the database ...
>
>It wouldn't hurt to check by invoking SQLDBA or server Manager
>and issue the command
>
> SQLDBA> show parameter db_block_buffers
>
>Again I welcome your explanation ... maybe I am missing
>something here ....
>
>Thanks ...
>
>================================================================
>============================
>| Joseph Sumalbag
>|
>| Oracle DBA
>|
>|
>|
>| The opinions expressed above are my own and doesn't

 necessarily                           |


>|reflect the opinion of any of my client company or my employer.                            

>|
>================================================================
>============================

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Dec 23 1997 - 00:00:00 CST