1. What is the keep pool?
The keep pool is part of the buffer cache and, therefore, a buffer area for Oracle blocks.
Each object can only use either the keep pool or the default pool. You cannot make a differentiation such as 'All blocks with data with certain properties go to the keep pool, all other data goes to the default pool'.
Along with the default pool and the keep pool, you can also set up a recycle pool. The idea here is to avoid saving data in the default pool that is only to be accessed once because this data could displace data that is accessed many times.
The recycle pool plays practically no role in the SAP environment and therefore is not further explained.
2. When should I use the keep pool?
It can be useful to use a keep pool if the blocks of certain segments (tables, indexes) are not to be replaced by the blocks of other segments.
To access their data, Oracle blocks must be read from disk into the buffer cache. Usually, the blocks of all segments and segment types (tables, indexes, rollbacks, LOBs and so on) are stored in the default pool. If the buffer is completely full and new blocks that were not buffered yet must be stored, buffered blocks are replaced in accordance with an internal Oracle algorithm (which is, basically, a least-recently-used algorithm).
The Oracle strategy of removing from the buffer those blocks that have not been accessed for a long time is normally useful. In certain situations, however, this may lead to longer access times if blocks that have not been accessed for a long time and are no longer buffered have to be read from disk. If the access times are long AND if the transaction or the report is very time-critical AND if ALL other causes of poor performance can be excluded, you can use keep pool buffering. For example, the tables and indexes of call center transactions may be good candidates for keep pool buffering since extremely fast response times are usually required in this case and the queried data may not be buffered.
3. When should I not use the keep pool?
It is not useful to set up a keep pool if you have not first excluded all other options for fine-tuning. You should first answer some questions, for example:
- Are there bad statements that cause too many buffer displacements due to massive I/O operations?
- Is the I/O performance insufficient?
- Does the system use an incorrect access path for the long-running statement?
- Are the indexes that are used in the correct access path corrupt?
- Could the long DB runtime of the statement be due to other reasons than I/O operations?
...
As long as you can answer any one of these questions with "yes", you should not use the keep pool since the problem might be corrected with another tuning methods.
4. What are the advantages and disadvantages of using a keep pool?
The main advantage is that the blocks of certain tables and indexes cannot be replaced by the blocks of other segments and that you can achieve permanent buffering of the main memory if you choose a sufficient size for the keep pool.
The main disadvantage is the additional space requirement for the main memory (which may be massive). In the worst case scenario, the additional main memory requirements are the total of the sizes of the tables and indexes to be stored in the keep pool. When the objects buffered in the keep pool grow in size, the demand on the main memory may even increase further.
5. Which steps do I have to take to buffer objects in the keep pool?
You must install (if there is no more space) or make available the additionally required main memory on the database server.
Setting up the keep pool
You must set the following parameters in the Oracle parameter file:
- Either (Oracle 8 and Oracle 9; static SGA)
db_block_buffers = <current value + additional space for the
Keep Pool; unit is 'Oracle blocks'>
db_block_lru_latches = <current value + 2> (ommit on Oracle 9)
buffer_pool_keep = (buffers:<space for the Keep Pool; unit is
'Oracle blocks'>, lru_latches:2)
- Or (Oracle 9 only; dynamic SGA)
db_keep_cache_size = <space for the keep pool; unit is bytes>
When you use db_keep_cache_size, you must use db_cache_size instead of db_block_buffers to set the size of the default pool. Unlike db_block_buffers, db_cache_size does NOT contain the additional memory for the keep pool.
"LRU latches" can be ignored.
Defining the tables and indexes to be stored in the keep pool.
To estimate the gain in performance resulting from the keep pool buffering of the individual objects, you must perform a detailed analysis of the distribution of all the disk reads during the query to be tuned. You can do this using the V$SEGSTAT (as of Oracle 9) or an oradebug trace.
For each table or index whose blocks are to be buffered in the keep pool, define the following:
alter table <table name> storage (buffer_pool keep);
alter index <index name> storage (buffer_pool keep);
Objects the are not changed remain in the default pool.
CAUTION: If an object is dropped and recreated, it must be moved into the keep pool again. On the other hand, a rebuild of an index retains the attribute of the keep pool buffering.
A priori filling of the keep pool directly after DB start (optional)
To fill the keep pool as quickly as possible, you can use an SQL script or an ABAP program to read the table/index blocks buffered by the keep pool directly after the DB start.
- Tables
Select * from <TABLE>;
- Indexes:
Select /*+ INDEX("<TABLE> "<INDEX>") */ <Indexfields separated by comma> from "<TABLE>";
6. Which steps do I have to take to buffer objects in the default pool?
For each table and index whose blocks are to be buffered again in the default pool, define the following:
alter table <table name> storage (buffer_pool default);
alter index <index name> storage (buffer_pool default);
7. How can I check which objects are buffered in the keep pool?
The dba_tables, dba_indexes and dba_segments views have a BUFFER_POOL column that can contain the values 'KEEP' or 'DEFAULT'.
Select segment_name, bytes from dba_segments where buffer_pool='KEEP';
this statement returns the names and sizes of the individual objects.
Select count(*), sum(bytes) from dba_segments where buffer_pool='KEEP';
this statement returns the number and total size of the objects.
8. How can I monitor the cache quality of the keep pool?
At database level:
With the v$buffer_pool_statistics view:
- Use the following formula to determine the buffer quality in percent:
= 100 * (1 - physical reads/(block gets + consistent gets))
select
name "BUFFER POOL",
100*(1-physical_reads/(db_block_gets+consistent_gets)) Quality
from v$buffer_pool_statistics;
- Number of physical reads
After a longer "warm-up" time, the number of physical reads should no longer increase significantly.
- Use the following statement to determine how many blocks are occupied in total by keep pool objects (that is, is the keep pool filled?):
SELECT COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh, dba_segments s
WHERE o.data_object_id = bh.objd
AND o.owner=s.owner and o.object_name=s.segment_name
AND s.buffer_pool='KEEP';
If the value returned is smaller than the size of the keep pool, free space exists.
9. How can I estimate the size of the keep pool I need to configure?
An exact estimate is difficult since Oracle does not provide any statistics about how often certain blocks of an object have been re-read from disk.
An estimate on application side is also difficult since a statement such as '10% of the table is being accessed' does not tell you whether the 10% portion of the relevant records is spread over 10% or maybe even over 100% of the blocks.
Maximum size - rough rule of thumb
Size of the keep pool=minimum of
a) The total size of all objects to be buffered in the keep pool
b) The total of all physical reads of all objects to be buffered in the keep pool after a longer warm-up time in v$segstat.
c) Minimum size - rough rule of thumb
If you want to store all records of an object permanently in the keep pool, the minimum size must be the object size.
If, on the other hand, a specific portion of records is accessed repeatedly but in such long intervals that the records have to be read again from disk when you use default pool buffering, the following estimate can be useful:
You can determine a minimum size for the keep pool by searching a snapshot of the entire default pool for blocks of the objects to be buffered in the keep pool and by buffering the file_IDs and block_IDs of the blocks found (provided they were not already found in previous snapshots) in an auxiliary table. The number of entries in the auxiliary table multiplied by the Oracle block size (8 K) provides the minimum size of the keep pool for this object.
For security reasons and since multiple buffering of the same block for consistent readings is ignored, you should multiply the size determined in this way by a factor of 2.
The total of all objects is the final minimum keep pool size.
The more snapshots (for example, every 15 minutes) you take over a longer period (for example, several days), the more precise this value will be.
Use the following statement to create the auxiliary table (ideally in its own PSAPKEEP tablespace):
create table sapr3.keep_pool_condensed (file_id number, block_id number)
tablespace psapkeep storage (pctincrease 0 initial 10M next 10m
pctfree 0);
The size of the sapr3.keep_pool_condensed auxiliary table is not more than 1/600 of the size of the keep pool of the object to be buffered since approximately 600 rows of the auxiliary table fit into one block and since one row in the auxiliary table equals one block of the table to be buffered in the keep pool.
Use the following statement to take different snapshots and save the blocks that have not been found previously:
insert into sapr3.keep_pool_condensed
(
(
SELECT distinct file#, block#
FROM DBA_OBJECTS o, V$BH bh
WHERE o.data_object_id = bh.objd AND
o.owner=' <OWNER>' and o.object_name='<OBJECT_NAME>'
)
minus
(
select file_id, block_id from sapr3.keep_pool_condensed
)
);
commit;
Caution: Test the statement first during a time of low system load.
Use the following statement to determine the minimum space requirements for this object in kilobytes:
select count(*) * 8 "size_kb" from sapr3.keep_pool_condensed;
Use the following statement to delete the auxiliary table:
drop table keep_pool_condensed;
If the keep pool still exists and if it already contains the objects, you can use the snapshot procedure described to determine approximately how many additional blocks are required for the keep pool.
The following is another method of performing a worst case estimate of the size of the keep pool (after a warm-up time in the system):
Physical_reads in v$puffer_pool_statistics - keep pool size in blocks
10. Which other SQL statements can be useful for monitoring the keep pool?
Use the following statement to determine which objects have occupied how many blocks in the keep pool (sorted by the number of blocks):
SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh, dba_segments s
WHERE o.data_object_id = bh.objd
AND o.owner=s.owner and o.object_name=s.segment_name
AND s.buffer_pool='KEEP'
GROUP BY o.object_name
ORDER BY count(1);
Use the following statement to determine how many blocks are occupied by a certain object in the keep pool:
SELECT COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh
WHERE o.data_object_id = bh.objd
AND o.owner=' <OWNER>' and o.object_name='<OBJECT NAME>';
11. Can insert operations also be accelerated by keep pool buffering?
Under certain circumstances, yes.
You must also always upgrade index blocks during insert operations. These index blocks must be read from the hard disk if they are not buffered. If you carry out mass inserts, you must also import a corresponding number of SUITABLE index blocks. A poor clustering factor of an index indicates an index that largely determines the runtime of the insert.
Caution: Inserts that perform poorly are generally the result of other factors that must be investigated beforehand. You must carry out a detailed analysis in all cases.