Oracle10g Obsolete SGA Parameters

Using AMM via the sga_target and sga_max_size parameter changes how the traditional pool parameters work. Also see these sga_target tips.

When using AMM, the values for the “traditional” pool parameters (db_cache_size, shared_pool_size, &c) are not ignored. Rather, they will specify the minimum size that Oracle will always maintain for each sub-area in the SGA.

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 and changed the behavior of others.

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. The hash_area_size is retained only for backward compatibility purposes but it's used in "alter session" commands.

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

��