Re: Buffer Cache performance

In article <7vhi89$38c$1_at_duke.telepac.pt>,   "Julio Gosmaalho" <jgosmaalho_at_yahoo.com> wrote:
> Hi guys.
>
> In a Oracle 7.3 DB because of its slow performance i ran several
scripts and
> I found that the buffer hit ratio is the worst value ( about 50%). All
the
> other values ,redo, rollback segment, latch contention data
dictionary, etc
> were ok (except library cache that needs a little adjustment - I
think...).
>
> So I increased the init.ora parameter from 200 DB_BLOCK_BUFFERS from
200 to
> 30.000, gradually - the memory of the NT Server allows that.
>
> RESULT - I only saw a benefit of 3% on the eficiency of the Buffer
Cache
> !!!!!!!
>
> Is true that if the applications use full table scans, the buffer
> cache isnt used ?

No, data from full table scan does go into the buffer cache. But it goes into the least recently used end of it, so it is the first data to be replaced when other data needs to be read in.

>
> The strange thing is that in another site we have the same
applications,
> same machine, with the same database and now the same parameters and
the
> performance is good.

Compare the two databases and check that you are not missing any indexes. If a needed index is missing then Oracle will do a full table scan instead.

>
> Do you have any idea about it ??

If both have exactly them same indexes, then see if your slow database is regularly running some statement that the other is not running. (e.g. look in v$sqlarea). If it is then use explain plan to find out if it is doing a full table scan. And if it is look at the where clause and try work if an index could be created to prevent it from doing a full table scan.

> TIA
>
> CC

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 31 1999 - 18:05:55 CST