Memory and SGA : Target vs Max Sizes

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