Large Page Support or Not – ORA_LPENABLE, Granule Size

May 17, 2011

Have you ever tried to bounce a database (maybe to adjust a parameter such as PROCESSES, which can only be altered in the spfile/init.ora) and encountered something like this (note that this is on a 64 bit Windows server):

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service. 

Interesting, 14,256MB of free memory, the database instance was just running with an SGA_TARGET set to 10,016M, and now the database will not OPEN.  The SGA_TARGET need not be more than 10,000M in size, the same problem can happen for small databases with the SGA_TARGET set to just 600M.  If you check the alert log, you might find something like this for the database instance with the small SGA_TARGET (note that the server has Intel Xeon X5687 CPUs, Oracle Database 11.2.0.2):

Starting ORACLE instance (normal)
Large page enabled         : 1
Large page size            : 2097152
Large page request size    : 4194304 

The database instance with the 10,016M SGA_TARGET might have an entry similar to the following:

Starting ORACLE instance (normal)
Large page enabled         : 1
Large page size            : 2097152
Large page request size    : 33554432

—

Straying a bit from the main topic of this blog article, I was a little curious about the change in the Large page request size that was reported for the two database instances.  So, I performed a couple of tests with different SGA_TARGET values:

SGA_TARGET	Large Page Request Size
256M	4,194,304
512M	4,194,304
1G	4,194,304
1G + 1 byte	16,777,216
2G	16,777,216
4G	16,777,216
8G	16,777,216
8G + 1 byte	33,554,432
16G	33,554,432
32G	67,108,864
64G	134,217,728
64G + 1 byte	268,435,456

So, what determines the Large page request size?  Checking the Oracle Database 11.2 documentation:

“Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. The granule size is 4MB if the SGA size is less than 1GB. If the SGA size is greater than 1GB, the granule size changes to 16MB. The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance.”

The observation of 4MB up to a SGA_TARGET of 1GB matches the documentation that describes the granual size, as does the observation of 16MB for an SGA_TARGET of 8GB.  However, notice that at a SGA_TARGET of 1 byte beyond 8GB, the Large page request size jumped to 32MB, which is a value greater than what the documentation describes for the granule size.  So, is the granule size still 16MB when the SGA_TARGET is set to a value just larger than 8GB, as indicated it would be in the 11.2 documentation?

You might recall reading in the past that as of Oracle Database 10.2 the LOG_BUFFER parameter is set by default to a value of just less than the granule size, typically being set to a value around 14MB with SGA values larger than 1GB – see Metalink (MOS) Doc ID 351857.1, “The Log_buffer Default Size Cannot Be Reduced In 10g R2”.  Let’s take a look at the size of the LOG_BUFFER in the database instance with the 10,016M SGA_TARGET:

SQL> SHOW PARAMETER LOG_BUFFER
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
log_buffer                           integer     18202624

The LOG_BUFFER was auto-set to a value of 17.36MB, which possibly indicates that the granule size in this database instance is not 16MB.  If we check Metalink (MOS) Doc ID 947152.1, “How to determine granule size”, we find that the calculation for the granule size has changed from Oracle Database 9.2 to 10.2, again with the release of 11.1, and again with the release of 11.2.  With the discovery of this new bit of information, the values reported for the Large page request size exactly mirrors that of the granule size.

—

Back to the main topic of this article, large page support.  Here is a quote from the Oracle Database 10.2 documentation:

“Large page support is a feature of Oracle Database 10g Release 1 (10.1) or later. It provides a performance boost for memory-intensive database instances. By taking advantage of newly introduced operating system support, Oracle Database 10g Release 1 (10.1) or later can now make more efficient use of processor memory addressing resources. Specifically, when large page support is enabled, the CPUs in the system will be able to access the Oracle Database buffers in RAM more quickly. Instead of addressing the buffers in 4KB increments, the CPUs are told to use 4MB page sizes when addressing the database buffers.”

A quote from the same section of the Oracle Database 11.2 documentation, with one extra line copied at the end:

“Large page support is a feature of Oracle Database 10g Release 1 (10.1) or later. It provides a performance boost for memory-intensive database instances on Windows Server 2003. By taking advantage of newly introduced operating system support, Oracle Database 10g Release 1 (10.1) or later can now make more efficient use of processor memory addressing resources. Specifically, when large page support is enabled, the CPUs in the system will be able to access the Oracle Database buffers in RAM more quickly. Instead of addressing the buffers in 4KB increments, the CPUs are told to use 2 MB page sizes in Physical Address Extension (PAE) mode and 4MB page sizes in non-PAE mode when addressing the database buffers.”

“By default Oracle allocates the minimum available large page size when using large pages. The minimum available large page size, 16 MB, is obtained by using the GetLargePageMinumum function.”

As you can see by the above, a little additional clarity was added to the 11.2 documentation, but where are the 2MB, 4MB, and 16MB numbers coming from?  Let’s check Metalink (MOS) Doc ID 422844.1, “Using Large Memory Pages on 64-Bit Windows Systems” – a very brief quote:

“… instead of addressing the buffers in 4KB increments, the CPUs are told to use 2 MB page sizes or larger. The default page size depends on the chipset, and the total amount of memory in the system.”

From the above we learn that the default page size depends on the chipset and the amount of memory in the server, with 2MB page sizes being the minimum (that 2MB figure does match the Large page size figure that appears above).  Is it really the chipset, or is it the CPU architecture (Intel vs. AMD, NUMA vs. non-NUMA) that is a potential source of the change?  Is it really the total amount of memory in the system, or is it the total amount of memory allocated to the SGA that is a potential source of the change?

—

Let’s take a look at a Microsoft document to see if it provides a little more insight into why the ORA-27102: out of memory error might have appeared when there was 14,256MB of free memory in the server:

• “Large-page memory regions may be difficult to obtain after the system has been running for a long time because the physical space for each large page must be contiguous, but the memory may have become fragmented. Allocating large pages under these conditions can significantly affect system performance…”

The memory must be contiguous… in this case, I wonder if Oracle Database is requesting multiple (313) 32MB allocations of memory without physical gaps in each memory region, or a single 10,016MB contiguous memory region at start up – the difference is significant.

Incidentally, the quick work-around for this problem is to disable large page support by changing the ORA_LPENABLE Windows registry key from a value of 1 to a value of 0 (this parameter is set for the Oracle home, but can also be set for each database instance running from the Oracle home).  There is also a mention of the ORA_LPSIZE registry key in Metalink (MOS) for controlling the size of the large page request size, but not enough information is provided in the articles to determine its exact usage.

On a slight tangent, the ORA_LPENABLE and ORA_LPSIZE registry keys, which are quite clearly intended to store numbers, must be defined as string (REG_SZ) datatypes in the Windows registry.  A bit of irony for a database company to use a string field to store a number, when the same action in the company’s database may have undesirable consequences.