The 12.2 documentation says “Large page usage locks the entire SGA into physical memory“.  However, this is not always true.

Consider the case of mixed mode, (ORA_SID_LPENABLE=2), with fragmented memory.  Some of the SGA has to be allocated with normal pages.  The alert log will show:

Fri Jul 14 13:01:56 2017
Large page enabled. Mode is : 2
Fri Jul 14 13:01:56 2017
Large page size           : 2097152
Large page request size   : 16777216
Fri Jul 14 13:01:56 2017
Large page Mixed-mode : Max time for each Large page allocation can be : 30000 msecs
Fri Jul 14 13:01:56 2017
Allocated Large Pages memory of size :         14680064
Fri Jul 14 13:01:56 2017
Allocated Large Pages memory of size :      1073741824
Fri Jul 14 13:01:56 2017
Allocated Large Pages memory of size :       536870912
Fri Jul 14 13:01:56 2017
Allocated Large Pages memory of size :       134217728
Fri Jul 14 13:01:57 2017
Allocated Large Pages memory of size :         50331648
Fri Jul 14 13:01:57 2017
Allocated Large Pages memory of size :         33554432
Fri Jul 14 13:01:57 2017
Large page Mixed-Mode : Failed to allocate memory in Large Pages. Allocation will be in normal pages

The normal sized pages of the SGA are not locked into memory, (and can’t be with LOCK_SGA), which could impact performance.
From x$ksmge and x$kmgsct

MIN(BASEADDR)   MAX(BASEADDR)      GRANULES         MB  GRANFLAGS COMPONENT                        GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- -----------
000007FF00000000 000007FF61000000         90       1440          4 DEFAULT buffer cache             ALLOC
000007FF02000000 000007FF07000000          6         96          4 Shared IO Pool                   ALLOC
000007FF5C000000 000007FF5C000000          1         16          4 java pool                        ALLOC
000007FF5D000000 000007FF63000000          3         48          4 large pool                       ALLOC
000007FF64000000 000007FF7E000000         27        432          4 shared pool                      ALLOC
VM Map showing unlocked SGA

We can see large page usage, but the entire SGA is not locked in RAM.  (Part of the shared pool is not locked).

Unfortunately, we can’t use the LOCK_SGA parameter to fix this because the instance will fail to start with that parameter set when large pages are used.

The DBA’s Options

  1. Use normal pages and set LOCK_SGA=true
    Incurs the overhead of using 4KB pages on large memory systems.
  2. Use ORA_SID_LPENABLE=1 and set LOCK_SGA=false
    Benefits from large pages, but the instance may fail to start if the server hasn’t been rebooted.  Not ideal for a large production server with multiple databases on it.
  3. Use ORA_SID_LPENABLE=2 and set LOCK_SGA=false
    Benefits from large pages, but unless the instance is restarted soon after a reboot each time, part of the SGA will often comprise normal pages, which are not locked, and so could be paged out.

While I appreciate the value of consistent performance, I still favour option 3 for large memory systems.  Even if reboots aren’t possible before each instance is started, the instance will not fail to start and most of the SGA should be in large locked pages.  To mitigate the risk of some of the SGA being paged out, I’d reserve a lot of RAM to handle spikes in demand from other processes, such as Windows Update.

Making the Oracle World a Better Place

Medium Term

I have filed a bug with Oracle Support to correct the documentation.

Long Term

Surely the Oracle code could be altered to behave as if LOCK_SGA=true when mixed mode wasn’t able to allocate large pages for the entire SGA.  Having the entire SGA locked in RAM, with as many large pages as possible, would be the ideal situation, so I have logged an enhancement request:

Bug 26670992 – BOTH NORMAL AND LARGE PAGES USED BY SGA SHOULD BE LOCKED WHEN IN MIXED MODE

Advertisements