Oracle Scratchpad

December 19, 2007

Memory Management

Filed under: Infrastructure,Performance — Jonathan Lewis @ 10:28 pm BST Dec 19,2007

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’ 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).

4 Comments »

  1. 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 BST Dec 20,2007 | Reply

  2. 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 BST Dec 20,2007 | Reply

    • 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 | Reply

  3. [...] 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,164 other followers