If you’ve just installed a machine with 16Gb of RAM and want to install an Oracle instance how much memory should you allocate to the SGA, the PGA target, and the operating system.
There’s an old rule of thumb (RoT) that I see from time to time that says something like: 50% to the SGA, 30% to the PGA, and 20% to the O/S.
That seems to me to be a naive (though possibly fairly safe) suggestion for many OLTP systems. Why do you need so much memory for the operating system – are you failing to make best use of your memory if you set aside so much.
Take the time to read, and listen to, this presentation from Christo Kutrovsky from Pythian and you’ll find out what that 20% is needed for – and how you can avoid wasting it if you configure your system properly.
Take note, especially, of the sections on “Page Tables” and “Huge Pages”. (Many of the examples reference Linux, but the principles do apply to other flavours of Unix).
Oracle Scratchpad 
I went through some sizing “exercises” (of a large production Oracle 9.2 OLTP database) under AIX 5L this year and if I felt inclined to formulate ROTs, that would be mine for AIX 5L:
Use Concurrent I/O for the datafiles (>= DirectIO), set lru_file_repage to 0 (to protect computational pages), put SGA in 16MB-pages (to both reduce the page tables’s size and pin SGA into physical memory), and leave PGA in non-pinned memory to allow unused but allocated PGA memory to get paged out. Keep an eye on the memory pinned by the OS – it often turns out to be way more than 20%. Measure and monitor.
But I don’t like rules of thumb ;-)
Flado
Comment by Flado — December 20, 2007 @ 4:01 pm GMT Dec 20,2007 |
OS memory is also used for the network devices. If you have many concurrent connections, the TCP queues may account for a significant portion of OS memory usage.
Comment by prodlife — December 20, 2007 @ 6:04 pm GMT Dec 20,2007 |
Chen,
I’ve just been prompted to search for this page and thought I’d add in the comment that you’re probably talking about the tcp transmit and receive buffers (actual names vary across flavours of UNIX) that tend to be sized somewhere between 32KB and 1MB each. (Correct me if I’m wrong).
1MB doesn’t sound like much, but with 2 directions and 2,000 users that’s a (possibly surprising) chunk 4GB just disappeared.
Comment by Jonathan Lewis — September 21, 2009 @ 2:34 pm BST Sep 21,2009 |
[…] is a topic I’ve raised in the past – but it seems appropriate to post another link to Christo Kutrovsky’s presentation and […]
Pingback by Memory « Oracle Scratchpad — June 23, 2010 @ 7:35 pm BST Jun 23,2010 |
[…] https://jonathanlewis.wordpress.com/2007/12/19/memory-management/ […]
Pingback by Oracle内存相关文章 | leonotes — July 1, 2016 @ 6:12 am BST Jul 1,2016 |
[…] that add some further bits and pieces that are likely to be relevant in some environments. And a link to a page I use to connect to a very technical classic presentation and video on the topic. And a note I wrote […]
Pingback by Huge Pages | Oracle Scratchpad — August 5, 2021 @ 12:53 pm BST Aug 5,2021 |