Using Buffer Pools in Oracle8

Using the Buffer Pools in Oracle8 - Once again, from the Performance Tuning Tips and Techniques Book...if this is helpful let me know and I'll post more like it.

The init.ora parameters db_block_buffers, db_block_size, buffer_pool_keep, and buffer_pool_recycle will be the determining factors for memory used to store data.
Db_block_buffers times the db_block_size refers to the total size in bytes of the main buffer cache (or memory for data) in the SGA. In Oracle8, two new buffer pools are introduced: buffer_pool_keep and buffer_pool_recycle. These additional two pools serve the same purpose as the main buffer cache (used to store data in memory), with the exception that the algorithm to maintain the pool is different for all 3 available pools.

The main buffer cache (defined by db_block_buffers) maintains the LRU (least recently used) list and flushes the oldest buffers in the list. The amount of blocks specified in db_block_buffers make up all three pools. The main buffer cache is what is left over from the buffer_pool_keep and the buffer_pool_recycle.
The keep pool (defined by buffer_pool_keep) is never flushed, and is intended for buffers that need to be "pinned" indefinitely (buffers that are very important and need to stay in memory). Use the keep pool for small tables that frequently accessed and need to be in memory at all times.

The recycled pool (defined by buffer_pool_recycle) is instantly flushed in order to reduce contention and waits in the pool by leaving the LRU list empty at all times. Data that would rarely be on the list in a normal mode, would search the list with no success. This overhead can be avoided with this pool.  Use the recycle pool for large less important data that is usually accessed only once in a long while.

Create a table which will be stored in the Keep Pool upon being accessed: Create table state_list (state_abbrev varchar2(2), state_desc varchar2(25)) Storage (buffer_pool keep);

Alter the table to the Recycle Pool:

Alter table state_list storage (buffer_pool recycle);

Alter the table back to the Keep Pool:

Alter table state_list storage (buffer_pool keep);

Find the disk and memory reads in the Keep Pool:

Select physical_reads "Disk Reads", block_gets + consistent_gets "Memory Reads"

From	v$buffer_pool
Where	name = 'KEEP';

Use the example below to query v$parameter and list the various buffer pool settings.

select 	name, value, isdefault, isses_modifiable, issys_modifiable 
from 	v$parameter
where 	name like '%pool%';

NAME	VALUE	ISDEFAULT	ISSES		ISSYS
shared_pool_size	10000000   	FALSE     	FALSE 	FALSE          
                
shared_pool_reserved_size	500000     	TRUE      	FALSE 	FALSE  
                        
shared_pool_reserved_min_alloc	5K         	TRUE      	FALSE 	FALSE  
                        
large_pool_size 	0          	TRUE      	FALSE 	FALSE          
                
large_pool_min_alloc	16K        	TRUE      	FALSE 	FALSE          
                
buffer_pool_keep		TRUE      	FALSE 	FALSE                  
        
buffer_pool_recycle		TRUE      	FALSE 	FALSE                  
        
parallel_min_message_pool	48330      	TRUE      	FALSE 	FALSE  
                        

8 rows selected.

Tip: The additional buffer pools (memory for data) available in Oracle8 are initially set to zero. By setting values for the buffer_pool_keep and buffer_pool_recycle, we eliminates from the blocks set by db_block_buffers that are allocated for the main memory for data and give them to the other two pools.

Rich Niemiec
Oracle Performance Tips and Techniques, Oracle Press ISBN: 0078824346 Received on Wed Jun 09 1999 - 13:03:53 CDT