Please send your Questions & Answers or Feedback to "dvmadhavreddy@javabook.org"

Please send your Questions & Answers or Feedback to "mohan@javabook.org"

Memory Tuning


Memory tuning is one of the important aspect for a DBA. If the memory is undersized or oversized it degrades the application performance. If the memory is undersized, it degrades the performance because of excessive hard parsing or more number of I/O's. If we have sufficient amount of memory, allocate 35% to 45% of memory for SGA. If we dont have sufficient amount of memory, allocate atleast 25% to 30% of memory for SGA.

Prior to 9i, its a tough task for a DBA to allocate memory for various sub components and to increase the values of sub components parameters.
Prior to 9i, all sub components parameters are static in nature.
In version 9i, oracle has introduced dynamic parameters for various sub components.
In version 9i, dynamically we can shrink and expand depending on the work load but DBA intervention is required.
In version 9i, oracle has introduced pga_aggrigate_target to allocate a consolodated PGA memory for sorting operations.
In version 10g, oralce has introduced automatic shared memory management(ASMM) by introducing a new background process and a new parameter(MMAN,sga_target).
In order to enable ASMM, we need to set sga_target to a non zero value. Once we enable ASMM, oracle dynamically shrinks and expands the sizes of the sub components depending on the work load. Once we enable ASMM, the following parameters are considered as auto tunable
1.db_cache_size
2.shared_pool_size
3.large_pool_size
4.java_pool_size
5.streams_pool_size
The following parameters are not considered as auto tunable
1.log_buffers
2.db_nk_cache_size
3.db_recycle_cache_size
4.db_keep_cache_size

sga_max_size represents the maximum allowable size for SGA.
sga_target value cannot go beyond sga_max_size.

After enabling ASMM, even if we set dynamic parameters to a value, oracle considers the parameters value as a lower limit and it expands further if there is a requirement.
In 11g, oracle has introduced automatic memory management by introducing the parameters
memory_max_target
memory_target
memory_target cannot exceed memory_max_target value.
In 11g, by default oracle allocates 60% of memory_target to SGA and 40% to PGA.

If we want to disable AMM, set memory_target to zero.
memory_target and sga_target are dynamic parameters.
Ideally, the hit rates of all sub components should be greater than or equal to 90%.
We can get hit rates by writing some queries or by generating stats pack report or AWR report(Automatic Workload Repository).

Related Posts Plugin for WordPress, Blogger...

Flag Counter