How to reduce memory consumption?
gnewsgroup
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.
Charles Hooper
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.
Frank van Bortel
When you created the database instance, did
you pay any attention to the screen where the
memory distribution was listed?
You could try to start the Database Creation Assistant
again, and see if you can change those parameters.
Alternatively, close some of the programs you are
running; 1GB of RAM, with 40% allegedly for Oracle,
leaves 600MB for XP, that only uses about 200,
when installed conservatively.
The 400MB is used somewhere else - find it!
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
hpuxrac
Memory is relatively cheap I would consider upgrading to 2 gig.
You can also stop the 11g services when you are not using oracle and
then start them when you do not to practice.
gnewsgroup
Thank you.
This is what I got:
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 0
I have then run this as you suggested:
SQL> alter system set sga_target=200M;
System altered.
SQL> alter system set pga_aggregate_target=50M;
System altered.
Hope this will ease the load on my computer.
gnewsgroup
Thank you. I will consider upgrading if there is a good deal. Yes,
like you said, I have made the oracle service manual (instead of
automatic) and only start it when I need it.
gnewsgroup
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.
gnewsgroup
wrote:
Yes, I did notice some screen during installation showed a default
setting of 256M for something which I don't know.
Charles Hooper
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.
gnewsgroup
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.
bdbafh
I don't quite recall, but was it Personal Oracle v7.3.4 that didn't
startup until it was first accessed?
-bdbafh
Steve Howard
hpuxrac
snip
> I don't quite recall, but was it Personal Oracle v7.3.4 that didn't
> startup until it was first accessed?
>
> -bdbafh- Hide quoted text -
>
> - Show quoted text -
How is that relevant to this thread?
OP noted 11g EE if you did not see that.
Charles Hooper
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
hpuxrac
>
> - Show quoted text -
IMHO, running with an sga and a pga limited to a toal size of 250M is
going to lead to problems "real soon now".
Mladen Gogala
> 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
Charles, he's using Oracle11g. Memory management mechanism has been
significantly altered in oracle11. The new feature is described in the
note 443746.1 about the automatic memory management. Having said that,
I fully support your recommendation for checking the documentation.
Charles Hooper
Mladen, thanks for the reply. That looks like a very helpful Metalink
note. Per the Metalink article, Oracle now offers 5 memory management
methods... and I may have used the wrong buzz word to describe the
method that uses SGA_TARGET and PGA_AGGREGATE_TARGET. I guess that I
need to dig back into the Concepts manual for 11g to see what else is
new.
One of the interesting items mentioned in the Metalink article is the
following: "The preceding steps instruct you to set SGA_TARGET and
PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance
PGA are tuned up and down as required, without restrictions." That
seems to raise the question, does setting a value for SGA_TARGET and
PGA_AGGREGATE_TARGET help limit the memory used for the SGA and PGA
when MEMORY_MAX_TARGET/MEMORY_TARGET is set? And is the limit
specified as the minimum or maximum value? Now I have a reason to dig
back into the Concepts manual.
hpuxrac
snip
> One of the interesting items mentioned in the Metalink article is the
> following: "The preceding steps instruct you to set SGA_TARGET and
> PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance
> PGA are tuned up and down as required, without restrictions." That
> seems to raise the question, does setting a value for SGA_TARGET and
> PGA_AGGREGATE_TARGET help limit the memory used for the SGA and PGA
> when MEMORY_MAX_TARGET/MEMORY_TARGET is set? And is the limit
> specified as the minimum or maximum value? Now I have a reason to dig
> back into the Concepts manual.
If you use the new 11g parameter memory_target and ( optionally )
memory_max_target then if you specify sga_target etc it is used as a
minimum similar to how shared_pool_size etc can be set to minimum aka
reserved size pre 11g.
Charles Hooper
Hpuxrac, your comment above is consistent with the Oracle
documentation.
I looked through the Concepts manual, Performance Tuning Guide, 2 Day
DBA manual, New Features manual, and Reference manual for 11g and
could not find an answer. When those documents mentioned the
PGA_AGGREGATE_TAGET and SGA_TARGET, there was no mention of their
impact on how it affects memory allocations per MEMORY_TARGET. I then
found this in the Administrator's Guide on page 5-6 (PDF page 152):
"The preceding steps instruct you to set SGA_TARGET and
PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance
PGA are tuned up and down as required, without restrictions. You can
omit the statements that set these parameter values to zero and leave
either or both of the values as positive numbers. In this case, the
values act as minimum values for the sizes of the SGA or instance
PGA."
So, the parameters that used to specify the intended maximum memory
allocation now specify the minimum memory allocation when
MEMORY_TARGET or MEMORY_MAX_TARGET are set.
Per the documentation, if the value for MEMORY_TARGET is specified but
the value for MEMORY_MAX_TARGET is not specified, Oracle will set
MEMORY_MAX_TARGET to be equal to MEMORY_TARGET. If the value for
MEMORY_MAX_TARGET is specified, but the value for MEMORY_TARGET is not
specified, Oracle will leave MEMORY_TARGET at 0 so that it may be
manually adjusted without bouncing the database instance on most
operating systems.
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?
gnewsgroup
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.
gnewsgroup
OK, I tried this:
SQL> startup pfile=D:\myoracle11g_home\admin\oracle11g\pfile\init.ora.
2212008193447
And it worked.
Charles Hooper
That's great that you found a work-around for the problem. I should
have mentioned this to you - always keep an updated copy of the pfile
(init.ora) handy. Name the pfile as initSID.ora (where SID is the
database name) and place it in the same folder (directory) as the
spfile. If you make a bad parameter choice when updating the spfile,
all that you need to do is rename the spfile, and on the next startup
command, Oracle will automatically use the pfile. You can then create
a new spfile from the pfile with a simple command. The web interface
in Oracle 10.2 makes it all too easy to select a bad parameter value
that will prevent the database from opening, so I had a bit of
practice with this approach.
So, what can you do if you do not have an up to date pfile? Open the
spfile using Wordpad, but do not make any changes using Wordpad.
Create a new file in Notepad listing each parameter that is found in
the spfile, on a separate line in the Notepad document, for example:
compatible='11.1.0.0.0'
control_files=("C:\Oracle\oradata\VMTEST\vmtest\control01.ctl", "D:
\Oracle\oradata\VMTEST\vmtest\control02.ctl")
db_block_size=8192
db_domain=''
...
There may be a couple stray characters here and there that need to be
removed. You can then save the file in the location expected by
Oracle, with the expected init.ora file name, and Oracle will open the
database using that file. If you have to use this method to recover
the parameters, you might encounter errors such as this when Oracle
tries to mount the database:
---------------------------------------------------
ORA-01078: failure in processing system parameters
LRM-00101: unknown parameter name 'oracle_base'
---------------------------------------------------
Just comment out the parameter identified by Oracle and try again.
For a fun experiment, I put my 11g test database into the same
condition as your database:
SQL>SHUTDOWN IMMEDIATE;
SQL>EXIT
C:\>SET ORACLE_SID=VMTEST
C:\>SQLPLUS /NOLOG
SQL>CONNECT / AS SYSDBA
SQL>STARTUP NOMOUNT;
SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET=300M SCOPE=SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET=250M SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>STARTUP NOMOUNT;
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be
at least 800M
After renaming the SPFILEVMTEST.ORA spfile found in:
C:\Oracle\Product\11.1.0\db_1\database
SQL>STARTUP
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT
\11.1.0\DB_1\DATABASE\INITVMTEST.ORA'
That's a nice improvement over Oracle 10.2, it even tells me where the
expected init.ora file should be located and the expected name of the
file. So, I create a new pfile by manually extracting the values from
the spfile as I have no backup pfile (just for this experiment).
SQL>STARTUP
ORA-01078: failure in processing system parameters
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account, see
alert log for more information
Checking the new pfile, I see that the values still specified for
SGA_TARGET and PGA_AGGREGATE_TARGET are too large for the value
specified for MEMORY_MAX_TARGET, so I comment out those values by
placing # in front of the parameters.
SQL>STARTUP
ORA-01078: failure in processing system parameters
ORA-00600: internal error code, arguments: [kmgsb_dependency_2],
[0x8822174], [0
], [], [], [], [], []
Well, that is a rather unhelpful error. Let's see if HPUXRAC's
comment: "IMHO, running with an sga and a pga limited to a toal size
of 250M is going to lead to problems 'real soon now'.", is correct.
Let's comment out the MEMORY_MAX value, but leave MEMORY_MAX_TARGET
set to 300M.
SQL>STARTUP
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes
Variable Size 255854948 bytes
Database Buffers 50331648 bytes
Redo Buffers 6340608 bytes
SQL>SHOW PARAMETER MEMORY_MAX_TARGET
NAME TYPE VALUE
-------------------- ----------- -----
memory_max_target big integer 300M
SQL>SHOW PARAMETER MEMORY_TARGET
NAME TYPE VALUE
-------------------- ----------- -----
memory_target big integer 0
Now that the Oracle database is mounted, I can create a new spfile
from the current pfile using the following command:
SQL>CREATE SPFILE FROM PFILE;
File created.
To continue the experiment:
SQL> ALTER SYSTEM SET MEMORY_TARGET=250M;
System altered.
SQL>SHOW PARAMETER MEMORY_TARGET
NAME TYPE VALUE
-------------------- ----------- -----
memory_target big integer 250M
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes
Variable Size 255854948 bytes
Database Buffers 50331648 bytes
Redo Buffers 6340608 bytes
SQL>SHOW PARAMETER MEMORY_TARGET
NAME TYPE VALUE
-------------------- ----------- -----
memory_target big integer 0
Because I had previously started the database using the pfile, the
parameter change did not survive the bouncing of the database. If I
had wanted it to survive, that would have been one of the advantages
of using the spfile rather than the pfile.
SQL> ALTER SYSTEM SET MEMORY_TARGET=250M;
System altered.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes
Variable Size 226494820 bytes
Database Buffers 79691776 bytes
Redo Buffers 6340608 bytes
SQL>SHOW PARAMETER MEMORY_TARGET
NAME TYPE VALUE
-------------------- ----------- -----
memory_target big integer 252M
So, it looks like HPUXRAC's comment is correct that 250M causes
problems, but 252M will work for test databases, or even very small
production databases.
It is important to experiment when trying to learn about Oracle.
Charles Hooper
> 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).