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
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
- Use normal pages and set LOCK_SGA=true
Incurs the overhead of using 4KB pages on large memory systems.
- 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.
- 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
I have filed a bug with Oracle Support to correct the documentation.
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