Re: How to reduce memory consumption?

On Mar 21, 8:26 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

Hi, Charles,

Thank you. I tried the 2 new commands you offered and they gave me a big problem. I issued those two commands and then shutdown the instance and tried to restart it as follows:

SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 252M
SQL> See the problem? How do I alter the MEMORY_TARGET value again now that no Oracle instance is running? Thank you. Received on Sat Mar 22 2008 - 21:52:44 CDT