Advantage of Caching / Pinning/ Keeping an Oracle Table in Buffer Pool

advertisements

_____________________________________________________________________________________________________________________

Oracle provides a feature to keep objects in the Buffer pool. This feature allows you to keep the frequently accessed table in memory itself which will provide the faster access of the table. The object will be kept under the KEEP pool and the purpose of the keep pool is to accommodate / cache the small objects like look up tables.

In general the server process has to bring data blocks for the table from the disk to the memory (buffer pool) which requires an overhead. Moreover the object gets removed from the memory whenever the object is not getting used or oracle needs room for another table in the buffer pool. If you keep the frequently accessed objects in the memory the above mentioned overhead can be reduced.

Keep Buffer is also working in First In First Out mode. If Oracle has more objects to keep in the KEEP buffer, automatically it removes the older objects from the keep buffer.  DB_KEEP_CACHE_SIZE is the parameter which determines the size of the Keep Buffer Pool.

Syntax to keep an object in KEEP Pool

SQL> alter table emp storage (buffer_pool keep);

Table altered.

How to remove a table/object from Keep Buffer Pool?

SQL> alter table emp storage (buffer_pool default);

Table altered.

Can we keep a function / procedure in Keep Pool?

We cannot keep an object without storage. For function and procedures only the definitions are stored in the data dictionary and it doesn’t have the storage clause.

SQL> create or replace function listNum (a number) return number is

  2  begin

  3  return 1;

  4  end;

  5  /

Function created.

SQL> alter function listNum storage (buffer_pool keep);

alter function listNum storage (buffer_pool keep)

                       *

ERROR at line 1:

ORA-00922: missing or invalid option

Keep PLSQL Objects in Buffer Pool

_____________________________________________________________________________________________________________________

Website Stats