Re: How to reduce memory consumption?

On Mar 21, 4:02 pm, gnewsgroup <gnewsgr..._at_gmail.com> wrote:
> On Mar 21, 3:52 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > Oracle 11g offers new features for autosizing memory parameters, if
> > you selected "Typical" when using DBCA to create the database, or
> > "Custom" and "Automatic Shared Memory Management".  See page 33 (2-13)
> > of this PDF for a quick summary of the options:http://download.oracle.com/docs/cd/B28359_01/server.111/b28301.pdf
>
> > If you are trying to learn Oracle 11g, that document is definitely the
> > best place to start.
>
> > While I have worked with Oracle 11g a bit, I don't recall if
> > explicitly changing the SGA_TARGET and PGA_AGGREGATE_TARGET has an
> > effect when automatic memory management is in use.  See page 76 of the
> > above PDF for information about how to adjust the memory allocation
> > parameters with automatic memory management enabled.
>
> > If the database was started using a pfile (init.ora), the effects of
> > the ALTER SYSTEM command will be lost once the database is bounced
> > (shut down and brought back up).  To eliminate confusion about whether
> > or not the effects of an ALTER SYSTEM command will be lost when the
> > database is bounced, you can use the SCOPE keyword like this:
> > ALTER SYSTEM SET SGA_TARGET=200M SCOPE=BOTH;
>
> > If the database was started using a pfile, the above will fail.  If it
> > was started with a spfile, the spfile will be updated with the new
> > value.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Also, I do notice that it still consume a lot of memory, at this
> moment, it eats more than 380M.  See the screen shot below which I
> created 1 min ago.
>
> http://gnewsgroup.googlepages.com/taskmanagerscreenshot
>
> I will try to learn what you suggested.  Thank you

I have had a couple minutes to experiment with 11g. If you are using Automatic Shared Memory Management, you will likely see values for MEMORY_TARGET and MEMORY_MAX_TARGET:
SQL> SHOW PARAMETER MEMORY_TARGET SQL> SHOW PARAMETER MEMORY_MAX_TARGET Both of the above showed 0 on my test system, as I manually specified the SGA_TARGET and PGA_AGGREGATE_TARGET. I did not specify SGA_MAX_SIZE, and that defaulted to SGA_TARGET + 4M on the test system.

If the above commands show values, and you are using a spfile, you can adjust the parameters like this:
ALTER SYSTEM SET MEMORY_TARGET=250M SCOPE=SPFILE; ALTER SYSTEM SET MEMORY_MAX_TARGET=300M SCOPE=SPFILE; Note in the above that SCOPE is set to SPFILE, so you will need to bounce the database. If you do not specify the SCOPE, you will see the following error when attempting to modify MEMORY_MAX_TARGET: ORA-02095: specified initialization parameter cannot be modified

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Mar 21 2008 - 19:26:39 CDT