Memory Management

Memory Management

The Large Pool

• Optional area within the SGA providing large amounts of session memory for MTS and Oracle XA interface, I/O server processes, and backup and restore operations.
  
• MTS improved performance due to more SQL stored in cache and less shrinking and growing of the SQL cache.
  
• Large pool is able to fulfil the large memory requests of backup and restore operations and I/O server processes.
  
• Large pool does not have an LRU list.
  
• Specify the size of the large pool by setting the LARGE_POOL_SIZE init parameter.
  Accepts values followed by the suffix K or M.
  Default value is 0 (zero) unless parallel execution or DBWR_IO_SLAVES are configured.
  Min value is 600K. Max value is O/S specific.
  
• When PARALLEL_AUTOMATIC_TUNING is set to TRUE parallel execution allocates buffers from the large pool, otherwise it allocates buffers from the SHARED_POOL.
  
• If LARGE_POOL_SIZE is not set and PARALLEL_AUTOMATIC_TUNING is set to TRUE, Oracle will compute a value based upon, PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU, PARALLEL_SERVER_INSTANCES, MTS_DISPATCHERS and DBWR_IO_SLAVES.
  
• The POOL column in the V$SGASTAT view identifies which pool an object in memory resides in.
  

Segmented Buffer Cache

• The database buffer cache can be segmented into separate buffer pools and schema objects can be assigned to each. Each buffer pool handles its aging of data blocks in a different way.
  
• KEEP buffer pool retains the schema object’s database blocks in memory. Use for smaller “warm” segments queried frequently. Can help to ensure predictable query response time.
  
• RECYCLE buffer pool purges database blocks from memory once they are no longer needed. Should be smaller than the DEFAULT pool and reuse buffers more frequently than the DEFAULT pool. Use for large segments with random I/O.
  
• DEFAULT buffer pool contains blocks assigned to this pool and blocks from objects that are not assigned to any other buffer pool.
  

Related Buffer Pool Parameters and Usage

• BUFFER_POOL_KEEP=(“buffers=nb”,”lru_latches=nl”) , where nb=number of buffers to allocate to the KEEP pool and nl=number of lru-latches to allocate to the KEEP pool. Nb min value is nl*50
  
• BUFFER_POOL_RECYCLE=(“buffers=nb”,”lru_latches=nl”) , where nb=number of buffers to allocate to the RECYCLE pool and nl=number of lru-latches to allocate to the RECYCLE pool. Nb min value is nl*50.
  
• DB_BLOCK_BUFFERS=n, where n is the number of total dB blocks (buffers) allocated to the instance. The amount of all buffers available for ALL buffer pools. KEEP and RECYCLE pool buffers are “taken” from this value and the remaining buffers make up the DEFAULT pool. Sum of buffers from all pools can not exceed this value.
  
• DB_BLOCK_LRU_LATCHES=n , where n is the total number of LRU latches available to the database instance. Similar usage as the DB_BLOCK_BUFFERS, all LRU latches are taken from this “pool” of latches. Sum of latches allocated to each pool can not exceed this value. Default value is CPU count/2. Valid range is 1 to (2*the number of CPU’s).
  Assigning objects to a specific buffer pool
  
• BUFFER_POOL bp , where bp is either KEEP, RECYCLE or DEFAULT.
  Part of STORAGE CLAUSE on CREATE and ALTER TABLE commands.
  Specified at the cluster level on clustered tables.
  Can define on both the index and over-flow segments of an index-organized table.
  Can be specified in the storage clause for each partition of a partitioned table and global and local partitioned indexes in both the CREATE and ALTER INDEX statements.
  
• All changes (ALTER STATEMENTS) effect new buffers loaded. Current buffers remain in the pool they are currently in, based upon the behavior of the pool itself.
  
• Run CATPERF.SQL to create the V$BUFFER_POOL_STATISTICS and GV$BUFFER_POOL_STATISTICS VIEWS.