Donald K. Burleson Oracle Tips

Oracle SGA Parameters
 

Self-tuning Oracle's memory regions involves altering the values of a number of Oracle parameters. While there are over 250 Oracle9i parameters that govern the configuration of every aspect of the database, there are only a handful of Oracle9i parameters that are important for Oracle SGA tuning:

• db_cache_size - This parameter determines the number of database block buffers in the Oracle SGA and is the single most important parameter in Oracle memory.

• db_keep_cache_size - db_keep_cache_size is used to store small tables that perform full table scans. This data buffer pool was a sub-pool of db_block_buffers in Oracle8i.

• db_recycle_cache_size - This is reserved for table blocks from very large tables that perform full table scans. This was buffer_pool_keep in Oracle8i.

• large_pool_size - This is a special area of the shared pool that is reserved for SGA usage when using the multi-threaded server. The large pool is used for parallel query and RMAN processing, as well as setting the size of the Java pool.

• log_buffer - This parameter determines the amount of memory to allocate for Oracle's redo log buffers. If there is a high amount of update activity, the log_buffer should be allocated more space.

• shared_pool_size - This parameter defines the pool that is shared by all users in the system, including SQL areas and data dictionary caching. A large shared_pool_size is not always better than a smaller shared pool. If your application contains nonreusable SQL, you may get better performance with a smaller shared pool.

• sort_area_size - This parameter determines the memory region that is allocated for in-memory sorting. When the stats$sysstat value sorts (disk) becomes excessive, you may want to allocate additional memory.

• hash_area_size - This parameter determines the memory region reserved for hash joins. Starting with Oracle9i, Oracle Corporation does not recommend using hash_area_size unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL work areas by setting pga_aggregate_target. hash_area_size is retained only for backward compatibility purposes.

• pga_aggregate_target – This parameter defines the RAM area reserved for system-wide sorting and hash joins.

• sga_max_size – This parameter defines the maximum size of the Oracle SGA, and cannot be modified while the instance is running.
 

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA