Oracle10g Obsolete Oracle SGA Parameters

Using AMM via the sga_target parameter renders several parameters obsolete. Remember, you can continue to perform manual SGA tuning if you like, but if you set sga_target, then these parameters will default to zero:

• 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_xk_cache_size - This set of parameters (with x replaced by 2, 4, 8, 16, or 32) sets the size for specialized areas of the buffer area used to store data from tablespaces with varying blocksizes. When these are set, they impose a hard limit on the maximum size of their respective areas.
• db_keep_cache_size - This 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 non-reusable SQL, you may get better performance with a smaller shared pool.
• java_pool_size -- This parameter specifies the size of the memory area used by Java, which is similar to the shared pool used by SQL and PL/SQL.
• streams_pool_size - This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.

This is exactly the same automatic tuning principle behind the Oracle9i pga_aggregate_target parameter that made these parameters obsolete. If you set pga_aggregate_target, then these parameters are ignored:

• sort_area_size - This parameter determines the memory region that is allocated for in-memory sorting. When the v$sysstat value sorts (disk) become 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.