Re: low buffer_hit_rate !!!!!

Joseph Sumalbag wrote:
>
> Timo Haatainen wrote:
> >
> > Angelica Rivas wrote:
> > >
> > > Hello to everybody!
> > >
> > > I have increased 300% the db_block_buffer parameter in the initSID.ora
> > > file but the buffer_hit_rate is still too low, I have made biger 3
> > > times (1500 each time) and restart the database, but it never has
> > > moved from the low number.
> > >
> > > My statistics says between 70 to 80 percent daily...
> > >
> > > What can I do to increase this ratio? Is There another hide parameter
> > > involved in this ratio?
> > >
> > > Thanks in advance, I will be so grateful...
> > > Regards...
> > >
> > > Angelica Rivas
> >
> > Hi Angelica,
> > is is possible that there is program(s) which are making full table
> > scans frequently? In such case the buffer hit ratio may be low and
> > increading the cache woun't help.
> > Check to see statistic 'Table scans (long tables)' if it is increasing.
> > If you have OEM you can quite simply identify those sessions.
> >
> > Hope this helps.
> > --
> >
> > Timo Haatainen
> > Carelcomp Forest Oy
>
> Timo,
> Hi Timo .

Hi Joseph,
here are some comments. Please email me further postings of this thread.

> 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 ....

The data from a full table scan is loaded to the end of the LRU list of buffer cache and therefore is aged out first. I think there is some parameter with which you can tell Oracle which full table scans are treated as long tables and which are short tables. Anyway, this is the mechanism to prevent 'bad selects' to flush the whole buffer cache.

>
> Performance will show a higher hit ratio but , since you are
> doing full table scans then the response is still slow ..
>
> 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 .
>

When scanning a long table Oracle writes n bytes to the end of the LRU list of cache, reads n bytes more, flushes n bytes from cache to have space in cache (these are flushed from the end of the list), writes n bytes to cache, reads another n bytes, .... As a result the full table scan uses only n bytes from SGA. I've seen this in real life. We had a (kind of test) db where buffer hit ratio was real bad (it was something 30% only!). There was a program which 'full table scanned' one table quite frequently (once a minute or something). We rewrote the query, created new indexes and did nothing else. The effect was that hit ratio increased near to 100%.

> 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                           |

-- 

Timo Haatainen
Carelcomp Forest Oy