Memory Parameters in oracle9i

Oracle9i new features on Memory Management :

1. Oracle9i introduced new parameter PGA_AGGREGATE_TARGET to tune portion of the memory in PGA. This new parameter dynamically adjust sort_area_size, hash_area_size, create_bitmap_area_size and bitmap_merge_area_size. The WORK_AREA_SIZE_POLICY should be set to AUTO to enable this feature.

2. Prior to oracle9i, shared pool size was static parameter. DBA has to restart the instance if shared pool size parameter needs to be changed. But in oracle9i introduced a new capability to change the shared_pool_size parameter dynamically without restarting the instance. But this parameter size should be less than or equal to SGA_MAX_SIZE.

3. Oracle9i introduced new approach to determine how the buffer cache is being using and how much memory has to be added or released from the buffer cache for optimal performance. Here are the steps to enable to feature.

a) Set the DB_CACHE_ADVICE parameter to ON. It gather statistics on the buffer cache by setting the value of this parameter. This is dynamic paramter and can be changed by using ALTER SYSTEM command.

b) Display the statistics gathered by querying the new dynamic performance view, V$DB_CACHE_ADVICE. This view contains information about the physical reads for the different cache sizes.

select id, name, block_size,buffers_for_estimate, estd_physical_reads, estd_physical_read_factor from v$db_cache_advice

c) We can change the buffer cache size based on the observation in step b.

The source of this article is click

Here are other related link to this topic.

Oracle10g Memory feature
Oracle11g Memory feature
Oracle Memory components