Re: low buffer_hit_rate !!!!!

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

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