Home » RDBMS Server » Performance Tuning » PGA_AGREGATE_TARGET parameter (Oracle11g 11.2.0.3)
PGA_AGREGATE_TARGET parameter [message #627308] Mon, 10 November 2014 14:56 Go to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
I have a question about PGA_AGGREGATE_TARGET parameter.

I am using oracle11g and Automatic memory managment is disabled by setting SGA_TARGET=0.

SGA_MAX_SIZE = DB_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE

Let us say, total SGA_MAX_SIZE is 40G and PGA_AGGREGATE_TARGET is 8G.

Can i assume, SGA_MAX_SIZE = SGA_MAX_SIZE - PGA_AGGREGATE_TARGET?

OR PGA_AGGREGATE_TARGET is independent of the SGA_MAX_SIZE?

Can some please clarify this?
Re: PGA_AGREGATE_TARGET parameter [message #627312 is a reply to message #627308] Mon, 10 November 2014 15:12 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
PGA is independent of SGA & can grow to infinity for versions 11 & below
Re: PGA_AGREGATE_TARGET parameter [message #627336 is a reply to message #627308] Tue, 11 November 2014 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
OR PGA_AGGREGATE_TARGET is independent of the SGA_MAX_SIZE?


They are independent as long as you don't set memory_target and memory_max_target parameters.

Re: PGA_AGREGATE_TARGET parameter [message #627405 is a reply to message #627336] Tue, 11 November 2014 12:14 Go to previous messageGo to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
Thanks to both of you for responding for my post. I appreciate your time!

Here is my another question. Now i understand, PGA_AGGGREGATE_TARGET is independent
of the SGA.

My DB server has 64G physical memory. Automatic memory management is disabled for
specific reason. I am not worried about MEMORY_TARGET & MEMORY_MAX_TARGET.

Here is the memory parameters on my database. The db version is 11.2.0.3

MEMORY_MAX_TARGET         ==> 0
MEMORY_TARGET             ==> 0
SGA_MAX_SIZE	          ==> 48G
SGA_TARGET	          ==> 0
PGA_AGGREGATE_TARGET	  ==> 10240
DB_CACHE_SIZE	          ==> 32768
SHARED_POOL_SIZE	  ==> 4096
LARGE_POOL_SIZE	          ==> 1024
JAVA_POOL_SIZE	          ==> 512
STREAMS_POOL_SIZE	  ==> 256


As per my understanding,

SGA_MAX_SIZE = DB_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE 

PGA_AGGREGATE_TARGET is independent of SGA_MAX_SIZE.

SGA_MAX_SIZE = 32768 + 4096 + 1024 + 512 +  256
SGA_MAX_SIZE = 38656

So SGA_MAX_SIZE is supposed to be 38656. But SGA_MAX_SIZE is defined as 48G.

so as per the DB parameter,
SGA_MAX_SIZE = DB_CACHE_SIZE + SHARED_POOL_SIZE + 
              LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE + PGA_AGGREGATE_TARGET

SGA_MAX_SIZE = 32768 + 4096 + 1024 + 512 +  256 + 10240
SGA_MAX_SIZE = 48896


My question is, SGA_MAX_SIZE should be 38656 or 48896?

If it is 48896, then PGA_AGGREGATE_TARGET is part of SGA. Please clarify me.
If it is 38656, then PGA_AGGREGATE_TARGET is independent of SGA. Please clarify me.

Thanks again!



Re: PGA_AGREGATE_TARGET parameter [message #627409 is a reply to message #627405] Tue, 11 November 2014 12:35 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't want to use automatic memory management then get rid of sga_max_size.
If you set it then either you have not set it enough for the other parameters to be satisfied and the instance will not start, either you set it too large and so you are wasting server RAM as all the sga_max_size will be allocated even if some parts will be never used.

Previous Topic: Plan missing from cursor cache
Next Topic: Cursor: Pin S Wait on X
Goto Forum:
  


Current Time: Sun Oct 21 08:26:23 CDT 2018