Poor Database Performance After Setting Huge Pages

I recently faced a problem whereby full memory of system got occupied after the database startup and eventually system was hung and reboot was inevitable. The recent change was to setting huge pages on the host. Eventually we found the reason that “soft memlock” was not set (oracle software owner name was wrongly spelled when specifying memlock in /etc/security/limits.conf file). Bu default “soft memlock” was set to a default value 64KB and none of huge pages was in use by the Oracle (SGA). As a result, SGA was allocated from the remaining memory (Huge pages would remain unused, yet allocated) and huge pages were merely consuming memory without any use. Following message in alert log let us understand the problem

Setting Huge Pages in Linux

If your Linux based database server has huge amount of physical memory, it will be a good idea to enable huge pages so that memory could be used efficiently by Oracle instance. Setting huge pages means that you have bigger sized (2 MB) memory blocks in memory to allocate to Oracle SGA. Bigger memory block size would mean fewer number of total memory blocks, and this is where managing memory becomes efficient by the OS. I would recommend to use/enable huge pages if host’s physical memory size goes beyond 128G. But you can also set huge pages even for a small amount of memory.

Migrating from Filesystem to ASM

While migrating from file system to ASM, we create ASM instance on the current host before we could stamp the disks and create asm diskgroups to migrate existing datafiles from the file system to the ASM. Before we create/start ASM instance, we need to start “Oracle Cluster Synchronization Service” (CSS). So, we initiate “dbca” to create/start ASM instance as a first step. If you are using Windows, invoke dbca application using right click and “Run as Administrator”.