Re: How to reduce memory consumption?

On Mar 21, 3:07 pm, gnewsgroup <gnewsgr..._at_gmail.com> wrote:
> On Mar 21, 2:39 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > On Mar 21, 1:37 pm, gnewsgroup <gnewsgr..._at_gmail.com> wrote:
>
> > > I am new to Oracle.
>
> > > I just installed Oracle11g enterprise edition on my Windows XP Sp2
> > > system.  My computer is a Pentium 4,  2.20GHz with 1G memory.
>
> > > If the Oracle service is running, it consumes around 400M memory, and
> > > as a result, my computer becomes barely usable.
>
> > > I only need to practice / play with Oracle 11g, so I am wondering if
> > > it is possible to greatly reduce its memory consumption and yet still
> > > let me play with it happily.
>
> > > Thank you.
>
> > The parameters SGA_TARGET and PGA_AGGREGATE_TARGET help control the
> > amount of memory used by Oracle - what values are specified for those
> > parameters?  If you are able to connect to the database using
> > SQL*Plus, type the following:
> > SHOW PARAMETER SGA_TARGET
> > SHOW PARAMETER PGA_AGGREGATE_TARGET
>
> > 400MB is a fairly small memory footprint for an Oracle database.  You
> > might be able to specify a value as small as 200M for the SGA_TARGET
> > and 50M for the PGA_AGGREGATE_TARGET.
>
> > Make certain that you have excluded the Oracle binary and data files
> > from virus scans, as real-time scans of the data files could kill
> > system performance.  2GB of memory is reasonably inexpensive upgrade
> > for most computers, so you might want to consider that upgrade.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> OK, after I changed the memory settings for SGA_TARGET and
> PGA_AGGREGATE_TARGET, I ran shutdown and then issued startup and I got
> this:
>
> SQL> startup
> ORACLE instance started.
>
> Total System Global Area  431038464 bytes
> Fixed Size                  1333676 bytes
> Variable Size             297797204 bytes
> Database Buffers          125829120 bytes
> Redo Buffers                6078464 bytes
> Database mounted.
> Database opened.
> SQL>
>
> Does it look right given the new settings?
>
> Thank you

It looks like the memory parameters reset - the SGA is at 411MB.

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. Received on Fri Mar 21 2008 - 14:52:33 CDT