With
recent talks with Jonno and Mark over last few days on parameters memory_target and memory_max_target
(Automatic Memory Management), here are few scenario’s.
Please do reply with your valuable comments.
Scene
1 :
Current init.ora settings
#*.memory_max_target=2G
#*.memory_target=2G
*.sga_max_size=1200M
*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 0
memory_target big integer 0
sga_max_size big integer 1200M
sga_target big integer 800M
pga_aggregate_target big integer 900M
Obviously, sga_max_size equals the value set
explicitly.
Scene
2 :
Current init.ora settings
#*.memory_max_target=2G
#*.memory_target=2G
*.sga_max_size=1200M
#*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 0
memory_target big integer 0
sga_max_size big integer 1200M
sga_target big integer 0
pga_aggregate_target
big integer 900M
Well, all expected.
Scene
3 :
Current init.ora settings
#*.memory_max_target=2G
#*.memory_target=2G
#*.sga_max_size=1200M
*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 0
memory_target big integer 0
sga_max_size big integer 800M
sga_target big integer 800M
pga_aggregate_target big integer 900M
Okay, sga_max_size equals to value set for sga_target.
Scene
4 :
Current init.ora settings
*.memory_max_target=2G
*.memory_target=2G
*.sga_max_size=1200M
*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 2G
sga_max_size big integer 1200M
sga_target big integer 800M
pga_aggregate_target big integer 900M
All parameters are defined.
Scene
5 :
Current init.ora settings
*.memory_max_target=2G
#*.memory_target=2G
*.sga_max_size=1200M
*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 0
sga_max_size big integer 1200M
sga_target big integer 800M
pga_aggregate_target big integer 900M
Looks okay. Right ?
Scene
6 :
Current init.ora settings
#*.memory_max_target=2G
*.memory_target=2G
*.sga_max_size=1200M
*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 2G
sga_max_size big integer 1200M
sga_target big integer 800M
pga_aggregate_target big integer 900M
So, if not set, memory_max_target gets value from
memory_target.
Scene
7 :
Current init.ora settings
*.memory_max_target=2G
*.memory_target=1600M
#*.sga_max_size=1200M
#*.sga_target=800M
#*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 1600M
sga_max_size big integer 2G
sga_target big integer 0
pga_aggregate_target big integer 0
And here is my point, if sga_max_size is not set, it
defaults to the value set for memory_max_target.
Scene
8 :
Current init.ora settings
*.memory_max_target=2G
#*.memory_target=1600M
#*.sga_max_size=1200M
#*.sga_target=800M
#*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 2G
memory_target big integer 0
sga_max_size big integer 2G
sga_target big integer 0
pga_aggregate_target big integer 1632M
To add to the statement from sc. 7, if sga_max_size is not set, it defaults to the value set
for memory_max_target irrespective of the values of memory_target and sga_target.
Scene
9 :
Current init.ora settings
#*.memory_max_target=1200M
*.memory_target=1600M
#*.sga_max_size=1200M
#*.sga_target=800M
#*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 1600M
memory_target big integer 1600M
sga_max_size big integer 1600M
sga_target big integer 0
pga_aggregate_target
big integer 0
If memory_target is not set, it defaults to 0, but if
memory_max_target is not set and memory_target is defined, it defaults to
memory_target.
Scene
10 :
Current init.ora settings
#*.memory_max_target=1200M
*.memory_target=2G
#*.sga_max_size=1200M
*.sga_target=800M
*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 2G
sga_max_size big integer 2G
sga_target big integer 800M
pga_aggregate_target big integer 900M
Hmmm.., when max values are not set for sga and
memory, both (memory_max_target and sga_max_size) equals to memory_target ??
In other words, if sga_max_size is not set,
memory_target takes precedence over sga_target to define sga_max_size.
Scene
11 :
Current init.ora settings
*.memory_max_target=2G
*.memory_target=2G
*.sga_max_size=0
*.sga_target=800M
*.pga_aggregate_target=900M
Any guesses ?
.
.
.
.
Starting up ..
ORA-01078: failure in
processing system parameters
ORA-00823: Specified
value of sga_target greater than sga_max_size
Scene
12 :
Current init.ora settings
*.memory_max_target=2G
*.memory_target=2G
*.sga_max_size=0
#*.sga_target=800M
#*.pga_aggregate_target=900M
Parameter values on the instance
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 2G
sga_max_size big integer 1228M
sga_target big integer 0
pga_aggregate_target big integer 0
Well, this looks better Automatic Memory Management to
me. Oracle calculates sga_max_size during start up.
Scene
13 :
Current init.ora settings
*.memory_max_target=2G
*.memory_target=1600M
*.sga_max_size=0
#*.sga_target=800M
#*.pga_aggregate_target=900M
Parameter values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 2G
memory_target big integer 1600M
sga_max_size big integer 960M
sga_target big integer 0
pga_aggregate_target big integer 0
When memory_target is reduced, Oracle also reduces
sga_max_size and my observation (could be wrong) is that Oracle calculates
sga_max_size's value from SGA Advisor.
I always found that the ratio of PGA/SGA for given
memory_target is in sync with v$sga_target_advice and v$pga_target_advice.
Hope
this helps,
Regards,
N@vin
One more reply ...
From: Navin
Subject: RE: Memory and SGA :
Target vs Max Sizes
Okay
Jonathan, Now consider this one,
Current
init.ora settings
*.memory_max_target=320M
*.memory_target=300M
#*.sga_max_size=0
*.sga_target=0
*.pga_aggregate_target=140
Parameter
values on the instance
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
memory_max_target big integer 320M
memory_target big integer 300M
sga_max_size big integer 320M
sga_target big integer 0
pga_aggregate_target big integer 140
Running
some heavy load on the database .. and here is the scenario
PGA
used is 60 M.
SQL> SELECT SUM(PGA_USED_MEM)/1024/1024 FROM V$PROCESS;
SUM(PGA_USED_MEM)/1024/1024
---------------------------
60.1974182
And
SGA usage is:
SQL> /
CURRENT_SGA_SIZE CURRENT_PGA_SIZE
---------------- ----------------
282.5625 60.1974182
SQL> SELECT NAME, BYTES/1024/1024, RESIZEABLE FROM V$SGAINFO
ORDER BY 2 ;
NAME
BYTES/1024/1024 RES
-------------------------------- --------------- ---
Shared IO Pool Size 0 Yes
Fixed SGA Size 2.12313843 No
Java Pool Size 4 Yes
Granule Size 4 No
Large Pool Size 4 Yes
Streams Pool Size 8 Yes
Redo Buffers 12.4375 No
Free
SGA Memory Available
36
Startup overhead in Shared Pool 88 No
Buffer Cache Size 116 Yes
Shared Pool Size 136 Yes
Maximum
SGA Size 318.5625
No
12 rows selected.
Maximum
SGA Size is 318.5625 M (memory_max_target) and free SGA is just 36 M and total
SGA size in use on the instance is 282.5625.
So
total SGA+PGA = 342.75M which is more than memory_max_target.
If
sga_max_size is not set , entire memory_max_target is allocated to SGA and
memory used by PGA can be on top of memory_max_target if required by the
instance. This
is what happened with ..T T.n..rs where memory_max_target was set to 9G but oracle.exe
was using 10.8G memory on the server.
Also please note that Oracle is just
brilliant to name this parameter as memory_max_target and not memory_max_size
:)
I
guess, we found something very interesting :)
Regards,
N@vin
