Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Inside the Oracle SGA Regions

Oracle Tips by Burleson Consulting
July 31, 2003
Don Burleson

 

Most DBAs know all about the Oracle System Global Area (SGA).  The SGA is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database.  Long gone are the days when only four main tunable components existed.  If you are using Oracleor above, expect to deal with the following memory regions:

  • Default buffer cache  ? This is the default memory cache that stores data blocks when they are read from the database.  If the DBA does not specifically place objects in another data cache (which will be covered next), then any data requested by clients from the database will be placed into this cache.  This memory area is controlled by the db_block_buffers  parameter in Oracle8i and below, and db_cache_size  in Oracleand above. 

  • Keep buffer cache  - Beginning with Oracle8, a DBA can assign objects to a special cache that will retain those object?s requested blocks in RAM for as long as the database is up.  The keep cache's main function is to hold frequently referenced lookup tables that should always be kept in memory for quick access.  The buffer_pool_keep  parameter controls the size of this cache in Oracle8, while the db_keep_cache_size  parameter handles the cache in Oracleand above.  The keep pool is a sub-pool of the default buffer cache.

  • Recycle buffer cache  - Imagine the opposite of the keep cache, and you have the recycle cache.  When large table scans occur, the data filling a memory cache is unlikely to be needed again, and should be quickly discarded from RAM.  By placing this data into the recycle cache, it will neither occupy valuable memory space nor prevent blocks that are needed from being placed in a buffer.  However, should it be requested again, the discarded data is quickly available.  The buffer_pool_recycle  parameter controls the size of this cache in Oracle8 and below, while the db_recycle_cache_size  parameter handles the cache in Oracleand above. 

  • Specific block size caches - Beginning in Oracle, a DBA can create tablespaces whose blocksize differs from the overall database blocksize.  When data is read into the SGA from these tablespaces, their data has to be placed into memory regions that can accommodate their special blocksize.  Oracleand above has memory settings for 2K, 4K, 8K, 16K, and 32K caches.  The configuration parameter names are in the pattern of db_nk_cache_size

  • Shared pool  - This familiar area holds object structures and code definitions, as well as other metadata.  Setting the proper amount of memory in the shared pool  assists a great deal in improving overall performance with respect to code execution and object references.  The shared_pool_size  parameter controls this memory region.  

  • Large pool  ? Starting in Oracle8, a DBA can configure an optional, specialized memory region called the large pool, that holds items for shared server operations, backup and restore tasks, and other miscellaneous things.  The large_pool_size  parameter controls this memory region.  The large pool is also used for sorting when the multi-threaded server (MTS) is implemented.    

  • Java pool  ? This area handles the memory for Java methods, class definitions, etc.  The java_pool_size  parameter controls the amount of memory for this area.  

  • Redo log buffer  - This area buffers modifications that are made to the database before they are physically written to the redo log files.  The log_buffer  configuration parameter controls this memory area. 

Note that Oracle also maintains a "fixed" area in the SGA that contains a number of atomic variables, pointers, and other miscellaneous structures that reference areas of the SGA. 

 



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational