Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


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 Oracle parameters that govern the configuration of every aspect of the database, there are only a handful of Oracle 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 Oracle, 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:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright ฉ 1996 -  2014 by Burleson. All rights reserved.

Oracleฎ is the registered trademark of Oracle Corporation.