Recently I have partially read Oracle 11g: New features for Administrator. In it I found some nice parts about Automatic Memory Management, which I'd like to accent here more as a small memo for me then as a standalone blog topic for other. This is mainly because most of the things are well know from other Oracle's manuals and books but clearness of presentation and compress of information is somehow the main reason of writing this blog topic here.Automatic Memory Management was a new feature introduced in 10g. With 10g release Oracle has come up with a new parameter called sga_target which was used to automatically manage the memory inside SGA. The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size. With 11g, Oracle went a step further to manage both SGA as well as PGA automatically. Oracle database 11g introduced 2 new parameters
memory_target and memory_max_target.
memory_target
The memory_target parameter is somehow somehow a combination of the sga_target parameter value and the pga_aggregate_target parameter, representing the total amount of memory that Oracle has to allocate between the various SGA and PGA structures. The memory_target parameter is dynamic and can be changed up to and including the value of memory_max_target.memory_max_target
The memory_max_target parameter allows dynamically change the value of the parameter memory_target within the confines of memory_max_target. Thus you can adjust the total amount of memory available to the database as a whole at any time. There are known limitations:- The memory_target and memory_max_target parameters cannot be used when lock_sga parameter has been set.
- memory_target and memory_max_target cannot be used in conjunction with huge pages on Linux or Windows
Ant the best of all, here is "how to" diagram that shows effects of memory init.ora parameters:
The End
While setting these new parameters (memory_target and max_memory_target), one has to be careful. A general advice would be to set sga_target and pga_aggregate_target to a minimum fixed value and set memory_target. Oracle will automatically increase sga_target and pga_aggregate_target to the desired level in specific moments.If you are upgrading 10g database to 11g and want to keep the current value of sga_target and pga_aggregate_target, than make sure you set the value:
memory_target>=(sga_target + pga_aggregate_target)With new hierarchy in memory management, Oracle has also introduced new memory advisory. You can view V$MEMORY_TARGET_ADVICE view to get advice on the optimal value of memory_target parameter. This view will show advisory data only after you set mentioned memory_target parameter.
Cheers!
