Saturday, February 28, 2009

How to keep a Table in Buffer Pool

To cache an oracle table in memory, there is a special type of buffer pool called the KEEP buffer pool. Its significance is that we can keep the frequently accessed tables in memory so that oracle does not have to frequently read the data blocks for that table from disk to memory and hence saves the over-head involved and hence leads to improvement in performance

Consider the scenario where we have a table and it is accessed frequently. Now due to cyclic usage of buffer pool, when the buffer pool gets filled up the table gets removed from buffer pool to make room for another table.Now by keeping the table in KEEP buffer pool,the table remains longer in buffer pool thus avoiding the overhead of reading the blocks frequently from the disk to sga.

However the keep buffer pool is also used in cyclic fashion(FIFO) and hence if you cache lots of table then the earlier cached tables would be removed.

To cache a table in buffer pool->

alter table <owner>.<tablename>storage (buffer_pool keep);