Memory

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.

Solution

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.