Do I need to set SGA and PGA if I am using memory_max_target, memory_target in 11g.

Do I need to set SGA and PGA if I am using memory_max_target, memory_target in 11g.


dba1234Asked:
Who is Participating?
 
Chakravarthi AyyalaDatabase AdministratorCommented:
The answer is no.
Once you have memory_target in place, that means you are using AMM(Automatic Memory Management) feature in 11g and Oracle will manage SGA and PGA on its own.
0
 
Christoffer SwanströmPartnerCommented:
Short answer: no. Oracle will decide the allocation pga vs sga
0
 
dba1234Author Commented:
I have 19GB RAM, 16 CPU and have two databases of 400 GB and 700 GB. What should be the size of
memory_target , memory_max_target,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Christoffer SwanströmPartnerCommented:
You could try 14 GB memory_target, 15 GB for max. That's assuming there's nothing else running on the server
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
database sizes will not play a role in memory allocations.  So far do you have any awr reports for those DBs?
0
 
slightwv (䄆 Netminder) Commented:
>>You could try 14 GB memory_target, 15 GB for max.

They have two databases.

>>What should be the size of  memory_target , memory_max_target,

This is a duplicate question:
http://www.experts-exchange.com/Database/Oracle/Q_27482217.html

You need to keep this question related to the memory parameters themselves.  Leave the sizing to the other question.

>>Do I need to set SGA and PGA if I am using memory_max_target, memory_target in 11g.

I have to slightly disagree with the previous experts explicit 'NO'.

I say the answer is, it depends.  You do not 'need' to but there can be specific situations where you want to over-ride Oracle on some minimum values and not let AMM take care of 'everything'.

0
 
Christoffer SwanströmPartnerCommented:
Sorry, I overlooked the fact that there were two DBs. In that case the total for the memory target should probably not exceed 14 GB. The way it's split up depends on how the databases are used. If usage patterns are similar you can just split it in two.

The original question was specifically about having to set the PGA and SGA parameters when using AMM and to that the answer is clearly no. However, I agree that there are cases in which you might want to tweak the memory settings, but in general you don't have to do it.
0
 
slightwv (䄆 Netminder) Commented:
>>I agree that there are cases in which you might want to tweak the memory settings,

Then the answer cannot be 'no'.  It has to be 'it depends'.

You need to understand the specifics for the system you are running.

Would ypu go into a room of Oracle DBA's at Open World and state that the answer is 'always' 'no' to the question asked?  If not, then you cannot say it here.
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
Ref text:
  The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA).
Ref URL:
  http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm

When i first posted the comment 37262158, i remmeber my read and also with a little analysis it makes sense that whatever settings we give for sga and pga, may not remain the same and will change.
Perhaps, i should have said "we can set, but they may not remain same".

Waiting to read from experts.
0
 
Christoffer SwanströmPartnerCommented:
We're getting into semantics here...

If the question is understood to mean "must I set set SGA and PGA parameters if using AMM" then the answer is unequivocally "no".

 If the question is understood to mean "should I set the SGA and PGA parameters if using AMM" then the answer is "it depends".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.