Once the tables and indexes have been loaded into the KEEP buffer pool, the buffer_pool_keep parameter must be increased by the total number of blocks in the migrated tables.
The following script will total the number of blocks that the KEEP pool requires, insuring 100 percent data caching. The script adds 20 percent to the total to allow for growth in the cached objects. The DBA should run this script frequently to make sure the KEEP pool always has a DBHR of 100 percent.
This script outputs the Oracle parameter that resizes the KEEP pool for the next restart of the Oracle instance. The parameter is placed in the init.ora file. Oracle10g deprecates buffer_pool_keep and it cannot be modified with an ALTER SYSTEM command.
Now, the database can be bounced and the parameter change will take effect.
The KEEP pool is an excellent storage location for small-table, full-table scans. It can also be a good place to store data blocks from frequently used segments that consume a lot of block space in the data buffers. These blocks are usually found within small reference tables that are accessed through an index and do not appear in the full-table scan report.
The x$bh internal view is the only window into the internals of the Oracle database buffers. The view contains much detailed information about the internal operations of the data buffer pools. Both the number of objects in a specific type and the number of touches for that object type can be counted in the x$bh table. It can even be used to create a snapshot of all the data blocks in the buffer.
The hot_buffers.sql query shown below utilizes the x$bh view to identify all the objects that reside in blocks averaging over five touches and occupying over twenty blocks in the cache. It finds tables and indexes that are referenced frequently and are good candidates for inclusion in the KEEP pool.
The hot_buffers.sql script will only run on Oracle8i and subsequent versions. This is because the tch column was not added until Oracle 8.1.6.
The output from the hot_buffers.sql script is shown next. It identifies the active objects within the data buffers based on the number of data blocks and the number of touches.
Type Name BLOCKS BUFFERS AVG_TOUCHES
------- ------------------------- ---------- -------- -----------
TABLE PAGE 104 107 44
TABLE SUBSCRIPTION 192 22 52
INDEX SEQ_KEY_IDX 40 34 47
TABLE SEC_SESSIONS 80 172 70
TABLE SEC_BROWSER_PROPERTIES 80 81 58
TABLE EC_USER_SESSIONS 96 97 77
INDEX SYS_C008245 32 29 270
The DBA must now decide whether the hot objects are to be segregated into the KEEP pool. In general, there should be enough RAM available to store the entire table or index. Using the example, if consideration is given to adding the page table to the KEEP pool, 104 blocks would have to be added to the Oracle buffer_pool_keep parameter.
The results from this script will differ every time it is executed because the data buffers are dynamic, and data storage is transient. Some DBAs schedule this script as often as every minute, if they need to see exactly what is occurring inside the data buffers.