Home » RDBMS Server » Performance Tuning » What should be recommended percentage of memory to be used for oracle (11.2.0.3 Standard Edition, SLES SP 3 64 bit)
What should be recommended percentage of memory to be used for oracle [message #632237] Wed, 28 January 2015 00:49 Go to next message
juniordbanewbie
Messages: 201
Registered: April 2014
Senior Member
Hi all,

from http://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#i47856

Quote:


For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

where total_mem is the total amount of physical memory available on the system.



80% percent of physical memory is to be used for oracle database instances.

from 567506.1 Maximum SHMMAX values for Linux x86 and x86-64
Quote:

Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of "1/2 of physical RAM".

from 1527109.1 Relationship Between SGA_TARGET and SHMMAX

Quote:

Ideally, we would like SGA_TARGET to fit in one shared memory segment at startup by having SGA_TARGET < SHMMAX



okay let me figure out this if I'm using automatic shared memory management model.

Let's say we have a physical memory of 16 GB, => shmax=>2^4*2^30, i.e 17179869184

my SGA_target will be then be 8G. =>I get this value based on 567506.1 Maximum SHMMAX values for Linux x86 and x86-64


If I based on http://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#i47856

For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 80%

now SGA_target should be = (total mem*80%)*80

=10.24G


For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
=6.4 G

Based on method one, I calculate SGA to be 8 G while that of method 2, I get 10.24 G or 6.4, would like to know how much percent of memory should we allocate to oracle database instances?

also would like to know why the differences if my method of calculating is sga_target is correct.

many thanks!
Re: What should be recommended percentage of memory to be used for oracle [message #632243 is a reply to message #632237] Wed, 28 January 2015 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can take any of these values.
You CAN'T know the necessary memory you will need from just the server RAM. It depends on your workload and concurrent workload.
So start with any value and then follow the need using V$%ADVICE views.

Re: What should be recommended percentage of memory to be used for oracle [message #632244 is a reply to message #632237] Wed, 28 January 2015 01:44 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
I think you may be reading the examples in te docs of how you COULD do it as instructions for how MUST do it. THe MOS articles are so old that you can give no credence to them for modern systems. You have to try running the code, and tune from experience.
Re: What should be recommended percentage of memory to be used for oracle [message #632313 is a reply to message #632244] Wed, 28 January 2015 18:40 Go to previous messageGo to next message
juniordbanewbie
Messages: 201
Registered: April 2014
Senior Member
Hi,

understood. but how do I know if MOS articles is old? MOS 1527109.1 was checked for relevance on 01-Oct-2014. As for 567506.1,

Quote:


Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.1.0.1 [Release 9.2 to 12.1]


thanks

Re: What should be recommended percentage of memory to be used for oracle [message #632314 is a reply to message #632313] Wed, 28 January 2015 18:48 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
Install the software & database, accept the defaults & worry about adjusting SGA size after you complete some load testing.
Re: What should be recommended percentage of memory to be used for oracle [message #632323 is a reply to message #632313] Thu, 29 January 2015 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not all MOS documents are accurate and adequate, they are written by human beings and most of them have only a couple of years in MOS and even none as a DBA in an enterprise.

Michel Cadot wrote on Wed, 28 January 2015 08:39

You can take any of these values.
You CAN'T know the necessary memory you will need from just the server RAM. It depends on your workload and concurrent workload.
So start with any value and then follow the need using V$%ADVICE views.


Re: What should be recommended percentage of memory to be used for oracle [message #632326 is a reply to message #632323] Thu, 29 January 2015 01:31 Go to previous message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
That's exactly it. Before I worked for Oracle, I used to think that Oracle Support knew it all. Well, they don't - they are just ordinary blokes like you and me. Heck, they were[/italic] me. Perhaps they have done one or two more courses than the average user (Uncle Oracle is good about internal training) but that may be all. They do not have access to any more information than we do. Yes, some MOS articles are hidden from the public, but not that many. We all see the same database, use the same search tools. Furthermore, unless things have changed, the support structure is very flat: any TAR might be picked up by someone with twenty years experience, or by some kid hired just a couple of months ago.

I should qualify all of the above by saying that things may well have changed - it is a long time since I worked for Larry.

[Updated on: Thu, 29 January 2015 01:31]

Report message to a moderator

Previous Topic: AWR - licensing question
Next Topic: Explain plan
Goto Forum:
  


Current Time: Sat Feb 24 07:12:05 CST 2018

Total time taken to generate the page: 0.02446 seconds