Re: db_block_buffers

Hi,

Increasing the Buffer Pool does not always mean a better cache hit ratio.

Before commenting, it is impossible to have a 64K Oracle Block in Oracle7, the maximum is 32K and is more probable to be in the order of 2K or 4K. Check the db_block_size parameter from v$parameter.

With regards to the buffer_pool, there are two parameters that can be set, to help in the the sizing of the Buffer Pool. The first in db_block_lru_extended_statistics and the other is db_block_lru_statistics. The first one gives a guidance as to what would happen if the buffer pool is increased, where as the second one suggests what would happen when the buffer_pool is decreased. Check out the Oracle8 Tuning Guide, chapter 14 for a good explanation of these parameters. The logic is the same with Oracle7, just the view name that you query is different. The URL's are (assuming Technet access); http://technet.oracle.com/doc/server73x/STU73/ch8.htm for Oracle7 and http://technet.oracle.com/doc/server.805/a58246/toc.htm for Oracle8 (sorry but technet was slow so hence the pointers).

One of the reasons that increasing the Buffer Pool does not increase the hit ratio is that the SQL statements that you are running against the database are performing full table scans, and as such the data is being loaded into the LRU end of the Buffer Pool and therefore constantly being aged out. With Oracle7, there is little you can do except for rewritting the query to use indexes, or setting the table parameter CACHE to ON. (alter table table_name cached I think).

Rgds
Andrew

BTW - These two parameters are obsolete in Oracle8i.

Bogie wrote:

> I'm trying speed up a Oracle 7 database that has a buffer hit ratio of 62%.
> When I increased the value for db_block_buffer from 4000 to 8000 there was
> little if any difference in the hit ratio. Is there another value I should be
> changing also?. Are the blocks 64k blocks? I am new to Oracle so please excuse
> my ignorance.
> TIA
>
Received on Mon Apr 19 1999 - 23:16:21 CDT