:

How to use Large Pages in Oracle on Windows

Background

Large pages on Windows was implemented in Oracle 10g release 1. To put it simply, the idea behind it is to allocate larger chunks of memory for the buffer cache so there is lower overhead when the CPUs are accessing large amounts of memory, resulting in the RAM being accessed more quickly. With a lot of DBs having very large buffer cache’s nowadays, it seems like a good idea. However, from my experience, I have mixed opinions about it. Here is why:

1. Instance Start-up – when you enable large pages it is necessary to ensure that the memory on the server is not fragmented in any way. If it is, it takes a long time to start the instance and I have even seen the Oracle database instance crash and appear to hang when starting up using large pages. Simply restarting the DB server and starting it again resolved the issue. This can be very annoying if you want to restart the DB and the server on which it resides is also home to other DBs.

At first we didn’t realise that we would need to restart the server for the best start up time so we were restarting the DB without doing it. We noticed that at first the DB took less than 1 min to start, then under 5 mins, then 15 minutes and then 45 minutes. The restart after that did not start…The DB hung and wouldn’t start at all. Then the instance crashed and we had to restart the server.

2. Server Performance – This is more of an addition to point 2 because while the DB is allocating the memory to the DB instance, it prevents most other actions taking place on the server. The server becomes very slow, unresponsive and you can’t even open up Windows explorer to navigate around. If it’s only for a few seconds it isn’t too bad, but for any period of time and it is most likely going to cause major problems. This completely prevents you from stopping and starting and DB with large pages unless you have planned downtime.

3. DB Performance – From what I have seen on our OLTP databases there is no additional performance. We suspect that it is better suited to applications which select large chunks of data at a time, such as Data Warehouse applications. Furthermore, we did not see any increase in CPU utilisation when we switched it off.

Configuration

You can easily turn large pages on in Windows by adding/modifying a registry entry. If you have multiple instances on the same host you can specify which you would like it on for and which off. Alternatively, you can enable or disable it for all instances running from the same Oracle Home. The parameters are as follows:

ORA_LPENABLE – Enables/disables it for any Oracle instance running from this home. 1 is on, 0 is off.

ORA_<SID>_LPENABLE – Replace the <SID> with the Oracle instance name and it will enable/disable large pages only for that particular instance. Again, 1 means turn large pages on and 0 means switch it off.

Both of the above registry entries can be found somewhere like:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1

I always restart the DB server when turning large pages on or when I restart one of the DBs so that I know ther memory is not fragmented and the DBs will start up fast and without issues.

Further Reading

Section “G.6 Large Page Support for 64-bit Windows” in the Oracle documentation

Other Notes

We recently bought some new servers and I installed 11g on them. I attempted to start the DB with NUMA enabled and Large pages. The server become completely unresponsive and the memory became exhausted until server crashed…Needless to say, I turned large pages and NUMA off!

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *