Re: low buffer_hit_rate !!!!!

>

This could also depend on the size of the db and amount of users/activity.1500 blocks really isn't that big for a large db. We have some >200000 for db_block_buffers. Are all of the buffers used? I have a script at work that determines this, although I can't remember what exactly it does right now. I'll look on Fri. I know it looks at sys.x$bh, I think select state, count(*) group by state, but don't hold me to it.

If they're not full, then you're just reading a lot of different data. If they are,
then increase it. Also, heed previous advise from other replys re: sampling for statistics & table scans.

Mitch
"I speak for myself, unless you're a lawyer. Then I speak for noone."

 [...deletia...]

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


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

> > |

> > ================================================================

> > ============================