Re: How to reduce memory consumption?

On Mar 22, 10:52 pm, gnewsgroup <gnewsgr..._at_gmail.com> wrote:
> 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.

OK, I tried this:

SQL> startup pfile=D:\myoracle11g_home\admin\oracle11g\pfile\init.ora. 2212008193447

And it worked. Received on Sat Mar 22 2008 - 21:58:31 CDT