Oracle Scratchpad

June 23, 2010


Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 7:33 pm BST Jun 23,2010

A client recently upgraded from 32-bit Oracle to 64-bit Oracle because this would allow a larger SGA. At the same time they increased their SGA from about 2GB to 3GB hoping to take more advantage of their 8GB of RAM. The performance of their system did not get better – in fact it got worse. You might think that they should have increased the SGA even further at this point – but read on before you give them that advice.

A little arithmetic.

It is important background information to know that they were running a version of Red Hat Linux and that there were typically 330 processes connected to the database using an average of about 4MB of PGA each.

Using small memory pages (4KB) on a 32-bit operating system the memory map for a 2GB SGA would be: 4 bytes for each of 524,288 pages, totalling 2MB per process, for a grand total of 660MB memory space used for mapping when the system has warmed up. So when the system was running at steady state, the total memory directly related to Oracle usage was: 2GB + 660MB + 1.2GB (PGA) = 3.8GB, leaving about 4.2GB for O/S and file system cache

The upgrade

Upgrade to a 64-bit operating system and a 3GB SGA and you need 8 bytes for each page in the memory map and have 786,432 pages, for a total of 6MB per process, for a total of 1,980 MB of maps – an extra 1.3GB of memory lost to maps. Total memory directly related to Oracle usage: 3GB + 1.9GB + 1.2GB (PGA) = 6.1GB, leaving about 1.9GB for O/S and file system cache.

Remember, also, that a surprisingly large volume of the SGA consists of pointers – which means the various structures used in (e.g.) v$sql are a few percent larger as all those pointers change from 4 bytes to 8 bytes – so a 3GB SGA using 64-bit Oracle won’t really be to handle much more “user” information than a 2GB SGA using 32-bit Oracle.

So we see: 2.3GB of memory (4.2GB – 1.9GB) that used to be available for the filesystem cache has just been lost and the effective SGA is not significantly larger.

I didn’t mention it earlier, but some of their application code was protected by the file-system cache because the Oracle cache wasn’t big enough. So they really needed that filesystem cache and they’ve just lost a lot of it – with the net result that their ‘physical read” counts didn’t decrease much but the average time for a ‘physical read’ event got longer because more of their “physical reads” really did turn into trips to disc – their performance got worse.


They should be using “huge pages” (a.k.a large pages)  for memory mapping. I can’t remember whether their platform supported 1MB or 2MB pages, but even at 1MB per page that would be 3,072 pages for a 3GB SGA, which means only 24KB per process, for a total of 7.9 MB – reclaiming most of the 1,980MB of mapping memory, which could go straight into the SGA, targeted at the buffer cache. (Axiom – if you have spare memory, being used by the filesystem as a cache, you should probably give most of it to Oracle for the buffer cache.)

This is a topic I’ve raised in the past – but it seems appropriate to post another link to Christo Kutrovsky’s presentation and blog on memory.


  1. Very good point. It’s not only the SGA size that matters but how many processes map that SGA into their address space.

    By the way, it probably makes sense to note that 11g Automatic Memory Management (i.e. setting memory_target) prevents Oracle from using HugePages. I’ve seen way to many times when DBA allocates HugePages and enables AMM and wondering why everything comes to a halt.

    Comment by Alex Gorbachev — June 23, 2010 @ 9:10 pm BST Jun 23,2010 | Reply

    • Alex,

      Excellent point about the incompatibility between HugePages and 11g’s Automatic Memory Management (AMM) – thanks for highlighting it.

      Comment by Jonathan Lewis — June 24, 2010 @ 3:57 pm BST Jun 24,2010 | Reply

  2. Oracle has had notes to this effect (64 bit will take more memory for structures) for years – but it’s so much more convincing and helpful to see the arithmetic and details of a real world example.

    Comment by joel garry — June 24, 2010 @ 12:15 am BST Jun 24,2010 | Reply

  3. We are also facing the same issue here, we have Oracle 10gR2 on windows server 2003 (32 bit). At server 12 GB ram is installed but due to OS dependency only 2 Gb ram is availabe to oracle, recently we have faced lot of Ora-04031 (Unable to allocate XXX bytes of memory….) error. After setting different values of SGA from last 3 days i have’t recieved this error, current value of sga is 1400 MB but still database performance is slow.

    I have suggested upgradation of OS and database to 64 bit, i.e switching to Windows server 2003 (64 bit) and 10gR2 (64 bit) versions, and will share my experience when perform this activity (which is planned in couple of weeks).


    Comment by Ken — June 24, 2010 @ 5:47 am BST Jun 24,2010 | Reply

    • Hi Ken,

      I upgraded some of my customers from Oracle 9i or Oracle 10GR2 from 32 bits to 64 bits without problems.
      Problem related to ORA-04031 disappear, performance increase due to more memory for buffer cache ….
      On Windows, large pages mut not be used because OS has problem to relase correctly memory ( during shutdown db ).

      I used export / import with Oracle 9i. With Oracle 10GR2, I just invalided all objects and recompiled them.
      You can also use rman backup.


      Comment by Grégory — June 24, 2010 @ 7:11 am BST Jun 24,2010 | Reply

    • Ken,
      You’re probably already familiar with the options to make use of more memory – the /3 switch to allow a larger user address space in windows, and the “paging/windowing” options that allow the db_block_buffers (I think you have to use the old-style parameter if you do this) to use high memory pages by creating a window in low memory that uses address switching to take advantage of high memory.

      They can help in the short term – but neither is a good solution to the problems of supporting a large number of users.

      Comment by Jonathan Lewis — June 24, 2010 @ 4:03 pm BST Jun 24,2010 | Reply

  4. Also make sure that the memory size as specified by sga_max_size can be reserved within the memory reserved for huge pages. In tests we did ( on RHEL 5) we confirmed that huge pages where hardly used (<100MB) and the entire SGA being allocated outside the memory reserved for huge pages when sga_max_size was slightly higher than size reserved for huge pages. Makes sense, however we thought that since sga_target was within the memory reserved for huge pages, Oracle would start allocating from them. But it didn't until we lowered sga_max_size.

    Comment by Øyvind Isene — June 24, 2010 @ 8:22 am BST Jun 24,2010 | Reply

    • On Linux, Oracle allocates all sga memory up to sga_max_size when starts an instance, even sga_target may be lower.

      Comment by Wayne Lang — June 24, 2010 @ 4:29 pm BST Jun 24,2010 | Reply

  5. […] an OakTable Network member, Jonathan Lewis, posted about the experience of one client migrating to 64 bit and increasing their SGA to see performance going down. The moral […]

    Pingback by Log Buffer #192, A Carnival of The Vanities for DBAs | The Pythian Blog — June 25, 2010 @ 3:17 pm BST Jun 25,2010 | Reply

  6. I have two questions here:

    1.) Where is the page table allocated? In the system space of the process memory?

    2.) Is this an advantage for the Windows OS, since on this OS there is only one Oracle process and sessions get their own thread within it, so only one page table is used for all session threads?

    Comment by Marcus Moennig — June 27, 2010 @ 7:40 am BST Jun 27,2010 | Reply

    • Marcus,

      Questions relating to bits around the edges (O/S, discs, networks) are where I start to pursue specialists in the technology when I see the need. In this case I can’t give you answers that I am certain of, but:

      (a) I believe it will be in process memory if you’re using small pages, but if you’re on RHEL and switch to huge pages I think you also get a shared memory map with locked memory pages which means there is only one (much smaller) map and it’s probably in system (or at least “public”) space.

      (b) I think you’re probably correct, although some people might say that this is more “damage limitation” than an advantage (particularly on 32-bit Windows) a shared map would be a relatively small benefit compared to the limitation that requires the sum of all process memory to be inside the 32-bit limit.

      Comment by Jonathan Lewis — June 30, 2010 @ 8:47 am BST Jun 30,2010 | Reply

  7. […] Memory […]

    Pingback by 犯错了 (vm_nr_hugepages) « 弹冠相庆 — October 23, 2010 @ 5:59 am BST Oct 23,2010 | Reply

  8. […] to a page I use to connect to a very technical classic presentation and video on the topic. And a note I wrote several years ago with a bit of real-life arithmetic explaining why a hardware upgrade didn’t have the expected […]

    Pingback by Huge Pages | Oracle Scratchpad — August 5, 2021 @ 12:53 pm BST Aug 5,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: