I want to mark a certain table and index to be cached in a specific Buffer pool but I don't know how, and what is the benefit of this work?
-thanks in advance
PM: Are there many buffer pools in Oracle architecture?!
With Oracle 8.1 and above you have the following buffer pools
The default buffer pool in for "normal" data block processing ... data blocks
being moved to the LRU list and read from there... If you happen to do a
large table scan, the data in the LRU may be flushed out to make room for
the data blocks from the table scan. This will cause the "normal" data blocks
to have to be reloaded in the LRU the next time their needed. (expensive processing ... potentially causing physical IO's)
You can use the recycle pool for data blocks that you don't want to affect the "normal" operations.
KEEP pool ... data blocks that you want to ensure stay in the buffer pool and not get aged out (still can be aged out, but not likely)..
sql> alter table hr.jobs storage (buffer_pool keep);