Re: How to reduce memory consumption?

On Mar 22, 2:37 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Mar 22, 12:23 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > One of the interesting items mentioned in the Metalink article is the
> > following:  "The preceding steps instruct you to set SGA_TARGET and
> > PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance
> > PGA are tuned up and down as required, without restrictions."  That
> > seems to raise the question, does setting a value for SGA_TARGET and
> > PGA_AGGREGATE_TARGET help limit the memory used for the SGA and PGA
> > when MEMORY_MAX_TARGET/MEMORY_TARGET is set?  And is the limit
> > specified as the minimum or maximum value?  Now I have a reason to dig
> > back into the Concepts manual.
>
> If you use the new 11g parameter memory_target and ( optionally )
> memory_max_target then if you specify sga_target etc it is used as a
> minimum similar to how shared_pool_size etc can be set to minimum aka
> reserved size pre 11g.

Hpuxrac, your comment above is consistent with the Oracle documentation.

I looked through the Concepts manual, Performance Tuning Guide, 2 Day DBA manual, New Features manual, and Reference manual for 11g and could not find an answer. When those documents mentioned the PGA_AGGREGATE_TAGET and SGA_TARGET, there was no mention of their impact on how it affects memory allocations per MEMORY_TARGET. I then found this in the Administrator's Guide on page 5-6 (PDF page 152): "The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA." So, the parameters that used to specify the intended maximum memory allocation now specify the minimum memory allocation when MEMORY_TARGET or MEMORY_MAX_TARGET are set.

Per the documentation, if the value for MEMORY_TARGET is specified but the value for MEMORY_MAX_TARGET is not specified, Oracle will set MEMORY_MAX_TARGET to be equal to MEMORY_TARGET. If the value for MEMORY_MAX_TARGET is specified, but the value for MEMORY_TARGET is not specified, Oracle will leave MEMORY_TARGET at 0 so that it may be manually adjusted without bouncing the database instance on most operating systems.

I also noticed in the 11g documentation that if SGA_TARGET is specified, PGA_AGGREGATE_TARGET defaults to 20% of the SGA_TARGET. So the next challenge, if MEMORY_TARGET or MEMORY_MAX_TARGET is specified, as well as SGA_TARGET, will Oracle still default PGA_AGGREGATE_TARGET to 20% of SGA_TARGET?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Mar 22 2008 - 17:33:37 CDT