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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.