Re: huge pages, ASMM and SGA sizing
Date: Wed, 23 Jul 2014 09:30:10 -0500
Message-ID: <CAP79kiRYMB3rc21qXW6R80zBvxRzgvK6NrgHdY2QDjVi=f-zXw_at_mail.gmail.com>
A good rule of thumb is the 80-20 rule. Use up to 80% of your server's memory for Oracle pools and leave 20% to the system.
You have to be careful with this though if you have a very "busy" system with lots of user connections as the sheer volume of user connections could overrun the available memory on the system (for example, if you have thousands of dedicated connections, you'd overrun the 20% available system memory fairly easily).
Some people recommend limiting the SHARED POOL size because it can actually be "too large in some cases and I don't have that information in front of me at the moment but Google should give you some good ideas on making sure your shared_pool_size is not too large.
So, let's say you have 128GB of RAM on the server and you have 1 Oracle instance. Now, on Linux (and I assume others) part of that 128GB of RAM is assigned to the kernel by the OS and so you don't have an "actual" 128 GB to start with. You can check "top" as a quick reference to see how much RAM the system reports as being installed - this is your useable RAM.
Example:
Let's say that TOP give you this number:
Mem: 99052568k - we divide that by 1024/1024 and get 94 GB of RAM
installed.
We take 94 GB * 80% and get 75 GB. We can use 75 GB for our Oracle instance.
How much of that 75 GB should goto the buffer cache? Shared Pool? Other Caches? That will be up to you but the lion's share should go to the buffer cache.
Considerations:
1. In your testing you'll need to make sure to account for the sheer number
of user connections - if you have a system with few dedicated connections,
then you should be fine with 20% available to the system. With many user
dedicated connections you'll have to adjust and alllow for more than 20% of
RAM to the system.
2. If you have more than 1 Oracle instance on the server, you have to make sure that the sum total for all the instances does not exceed the 80% mark. (again with testing)
(Once upon a time I had all this documented somewhere with links to Metalink docs talking about the 80/20 'rule' but I don't have them in front of me at the moment)
HTH
Chris Taylor
On Wed, Jul 23, 2014 at 7:56 AM, April Sims <aprilcsims_at_gmail.com> wrote:
> Thanks to everyone with the Huge pages advice....we now have it enabled.
>
> I am now getting pressure from all sides to increase the SGA "because we
> have plenty of memory now" with huge pages. Triple the current SGA
> size....what are the ramifications of doing this? In 10G there were lots
> of easy ways to check buffer/pool sizes with hit ratios.
>
> Does anyone have some advice? I need solid evidence to back up what I
> say....just pointing to the ADDM report isn't what they want. We don't own
> Database Replay.
>
>
> I currently have the SGA allocated manually....I have not turned on ASMM
> yet (but it is on the table) and don't plan on using AMM due to known
> issues with huge pages.
>
> Currently on 11.2.0.3 with a planned upgrade to 12.1.0.2 within the year.
>
> thanks,
>
> --
> April C. Sims
> http://aprilcsims.wordpress.com
> Twitter, LinkedIn
> Oracle Database 11g – Underground Advice for Database Administrators
>
> <http://www.amazon.com/Oracle-Database-Underground-Advice-Administrators/dp/1849680000/ref=sr_1_1?ie=UTF8&s=books&qid=1272289339&sr=8-1#noop>
> https://www.packtpub.com/oracle-11g-database-implementations-guide/book
> OCP 8i, 9i, 10g, 11g DBA
> Southern Utah University
> aprilcsims_at_gmail.com
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 23 2014 - 16:30:10 CEST