Re: How to reduce memory consumption?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 23 Mar 2008 07:54:17 -0700 (PDT)
Message-ID: <de450c2f-97c1-438b-9ca7-c88f0f19f2fa@i29g2000prf.googlegroups.com>

On Mar 22, 6:33 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

Answering my own curiosity, with MEMORY_MAX_TARGET set to 300M, and MEMORY_TARGET unset:

SQL>SHOW PARAMETER SGA_TARGET

NAME                      TYPE        VALUE
------------------------- ----------- -----------
sga_target                big integer 0

SQL>SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME                      TYPE        VALUE
------------------------  ----------- -----------
pga_aggregate_target      big integer 28940697

So, PGA_AGGREGATE_TARGET has a value, but SGA_TARGET does not.

SQL>ALTER SYSTEM SET SGA_TARGET=230M; System altered.

SQL>SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>STARTUP
ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size                  1332892 bytes
Variable Size             163580260 bytes
Database Buffers          142606336 bytes
Redo Buffers                6340608 bytes

Database mounted.
Database opened.

SQL> SHOW PARAMETER SGA_TARGET

NAME                      TYPE        VALUE
------------------------  ----------- -----------
sga_target                big integer 232M

SQL>SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME                      TYPE        VALUE
------------------------  ----------- -----------
pga_aggregate_target      big integer 48653926

So, PGA_AGGREGATE_TARGET's value has changed automatically, and is now 20% of the SGA_TARGET.

Now, let's set a value for MEMORY_TARGET: SQL>ALTER SYSTEM SET MEMORY_TARGET=252M; System altered.

SQL>SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size                  1332892 bytes
Variable Size             201328996 bytes
Database Buffers          104857600 bytes
Redo Buffers                6340608 bytes

Database mounted.
Database opened.

SQL>SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME                      TYPE        VALUE
------------------------  ----------- -----------
pga_aggregate_target      big integer 0

SQL>SHOW PARAMETER SGA_TARGET

NAME                      TYPE        VALUE
------------------------  ----------- -----------
sga_target                big integer 232M

So, it appears that if just MEMORY_MAX_TARGET is specified, the 20% rule for PGA_AGGREGATE_TARGET still applies, but once MEMORY_TARGET is set, the 20% rule no longer applies. (I then set MEMORY_TARGET to 300M, bounced the database, and PGA_AGGREGATE_TARGET continued to show a value of 0).

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Mar 23 2008 - 09:54:17 CDT