Oracle Database 10g automates the management of shared memory used by an instance and liberates administrators from having to manually configure the sizes of shared memory components. Automatic Shared Memory Tuning significantly simplifies Oracle database administration by introducing a more dynamic, flexible and adaptive memory management scheme. It makes more effective use of available memory therefore reducing the cost incurred on acquiring additional hardware.
SQL to find the size of the SGA
select name, value
where name in ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
'log_buffer', 'db_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',
'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size',
'db_keep_cache_size', 'db_recycle_cache_size', 'large_pool_size');
Using Oracle's Automatic Shared Memory Tuning, you can instruct Oracle to manage a subset of the components that make up the SGA by merely telling the instance the target size of the SGA through the new SGA_TARGET parameter.Oracle will then pool from this value and dynamically distribute memory across selected components of the SGA.
You now need not set values for SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, or DB_CACHE_SIZE as Oracle will automatically size them for you.Once you set the SGA_TARGET parameter to a desirable size for your SGA these parameters will take on a value of zero and new parameters will be created designated by
__SHARED_POOL_SIZE,__JAVA_POOL_SIZE,LARGE_POOL_SIZE, and __DB_CACHE_SIZE.
As workloads go through the system and memory is needed in these areas, Oracle will allocate more memory based on internal statistics trends.
Oracle will not manage the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DBnK_CACHE_SIZE, or the STREAMS_POOL_SIZE and you must still determine the value for these parameters.
Database Tuning >