Setting BUFFER_POOL

[tovohery]

My oracle 8i database was running on win2000 with 1CPU and 2Go RAM.
The SGA was about 1700000000
This db_block_size = 8192
db_block_buffer = 170000

I have one table about 5 millions of rows (the Average row size 159,02) and I want to keep it in memory. What should i do to set the BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE in the parameter file with these values.

Regards

[stecal]

This is a good reference on the subject:

http://www.jlcomp.demon.co.uk/faq/pin_table.html

[vikram_gaba]

Hi,

You can edit initSID.ora and add/edit the foll parameters
buffer_pool_keep = 65536 ( for example)
buffer_pool_keep = (buffers:65536, lru_latches:1)

shutdown and restart

issue the command (let's assume tablename is 'x')

alter table x storage ( buffer_pool keep);

now to load the entire table do a fts by issuing:
select * from x

if the entire table does not load increase the buffer_pool_keep value in your init file. Ensure buffer_pool_keep does not exceed
db_buffer_cache

Good luck,
Vikram