By Franck Pachot
.
ORA_LPENABLE
To let Oracle use Large Pages on Windows, you need to define a registry string ORA_LPENABLE=1. This will enable large pages for all instance so it’s probably better to do it at instance level with ORA_SID_LPENABLE, especially if you have an ASM instance on your server. Note that large pages is a recommandation for an database server but if you have other applications that use lot of memory you may lack of memory because of fragmentation.
Stopping the service:
C:\cygwin64\bin>net stop OracleServiceCDB
The OracleServiceCDB service is stopping.
The OracleServiceCDB service was stopped successfully.
Showing the registry:
C:\cygwin64\bin>reg query HKLM\Software\Oracle\Key_OraDB12Home1
HKEY_LOCAL_MACHINE\Software\Oracle\Key_OraDB12Home1
ORACLE_HOME REG_SZ C:\app\oracle\product\12.1.0\EE12101
ORACLE_HOME_NAME REG_SZ OraDB12Home1
ORACLE_GROUP_NAME REG_SZ Oracle - OraDB12Home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_BUNDLE_NAME REG_SZ Enterprise
OLEDB REG_SZ C:\app\oracle\product\12.1.0\EE12101\oledb\mesg
ORACLE_HOME_TYPE REG_SZ 1
ORACLE_SVCUSER REG_SZ oracle
ORACLE_SVCUSER_PWDREQ REG_SZ 1
ORACLE_BASE REG_SZ C:\app\oracle
MSHELP_TOOLS REG_SZ C:\app\oracle\product\12.1.0\EE12101\MSHELP
ORACLE_HOME_KEY REG_SZ SOFTWARE\ORACLE\KEY_OraDB12Home1
SQLPATH REG_SZ C:\app\oracle\product\12.1.0\EE12101\dbs
RDBMS_CONTROL REG_SZ C:\app\oracle\product\12.1.0\EE12101\DATABASE
RDBMS_ARCHIVE REG_SZ C:\app\oracle\product\12.1.0\EE12101\DATABASE\ARCHIVE
ORA_CDB_AUTOSTART REG_EXPAND_SZ TRUE
ORA_CDB_SHUTDOWN REG_EXPAND_SZ TRUE
ORA_CDB_SHUTDOWNTYPE REG_EXPAND_SZ immediate
ORA_CDB_SHUTDOWN_TIMEOUT REG_EXPAND_SZ 90
ORACLE_SID REG_SZ CDB
ORA_LPENABLE REG_SZ 1
HKEY_LOCAL_MACHINE\Software\Oracle\Key_OraDB12Home1\ODE
HKEY_LOCAL_MACHINE\Software\Oracle\Key_OraDB12Home1\OLEDB
Starting the service:
C:\cygwin64\bin>net start OracleServiceCDB
The OracleServiceCDB service is starting...
The OracleServiceCDB service was started successfully.
and tailing the alert.log:
C:\cygwin64\bin>adrci exec="set home cdb ; show alert -tail 5"
2016-06-13 12:51:22.709000 -07:00
Archiving is disabled
2016-06-13 12:51:32.153000 -07:00
Instance shutdown complete
2016-06-13 12:51:38.179000 -07:00
Starting ORACLE instance (normal) (OS id: 1620)
CLI notifier numLatches:3 maxDescs:519
Large page enabled. Mode is : 1
Large page size : 2097152
Large page request size : 16777216
2016-06-13 13:00:38.599000 -07:00
Starting ORACLE instance (normal) (OS id: 1760)
CLI notifier numLatches:3 maxDescs:519
Large page enabled. Mode is : 1
Large page size : 2097152
Large page request size : 16777216
2016-06-13 13:01:37.707000 -07:00
Starting ORACLE instance (normal) (OS id: 1888)
CLI notifier numLatches:3 maxDescs:519
Large page enabled. Mode is : 1
Large page size : 2097152
Large page request size : 16777216
You can see my registry entries on the right and the alert.log on the left.
But this is not sufficient to start the instance:
Lock pages in memory
In 12c you can, and you should, run the Oracle Database instance as another user than the system administrator.
Large pages must be locked in physical memory (and that’s one good reason to use them) but by default non administrators do not have this privilege.
You have to allow it with the Local Goup Policy Editor. Unfortunately, doing that in a Windows Server Core seems to be very difficult because gpedit-msc is not there.
At that point, my enthousiasm for Windows Core ended and I installed Windows Server GUI.
Now I can startup. Here is the alert.log:
Starting ORACLE instance (normal) (OS id: 2620)
Wed Jun 15 09:53:12 2016
CLI notifier numLatches:7 maxDescs:519
Wed Jun 15 09:53:12 2016
Large page enabled. Mode is : 1
Wed Jun 15 09:53:12 2016
Large page size : 2097152
Large page request size : 16777216
Wed Jun 15 09:53:12 2016
Allocated Large Pages memory of size : 14680064
Wed Jun 15 09:53:12 2016
Allocated Large Pages memory of size : 1660944384
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 4
Number of processor cores in the system is 4
Number of processor sockets in the system is 1
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
Windows NT Version V6.2
CPU : 4 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:5718M/8191M, Ph+PgF:7735M/10111M
And yes, I am in Automatic Memory Management (AMM) where size of PGA and SGA is dynamically resized by Oracle within MEMORY_TARGET:
Using parameter settings in server-side spfile C:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILECDB.ORA
System parameters with non-default values:
processes = 300
use_large_pages = "ONLY"
memory_target = 1600M
control_files = "C:\APP\ORACLE\ORADATA\CDB\CONTROL01.CTL"
control_files = "C:\APP\ORACLE\FAST_RECOVERY_AREA\CDB\CONTROL02.CTL"
db_block_size = 8192
compatible = "12.1.0.2.0"
db_recovery_file_dest = "C:\app\oracle\fast_recovery_area"
db_recovery_file_dest_size= 500M
_catalog_foreign_restore = FALSE
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=CDBXDB)"
audit_file_dest = "C:\APP\ORACLE\ADMIN\CDB\ADUMP"
audit_trail = "DB"
db_name = "CDB"
open_cursors = 300
diagnostic_dest = "C:\APP\ORACLE"
enable_pluggable_database= TRUE
On Windows, all Oracle “processes” run as threads of the same Windows process, I don’t see any reason to advise against AMM there.
Note that I don’t see any reason to recommend it either…
Update 20-JUN-2016
My Oracle Support engineer has confirmed that there is no problem to use AMM with large pages on windows systems. Doc ID 422844.1 -“Using Large Memory Pages on 64-Bit Windows Systems” confirms that large pages are used only for SGA, not PGA, so only SGA is locked in memory and 2MB pages. However, I think it’s a good idea to set SGA_TARGET to get a minimum that keeps allocated because frequent resizing may lead to fragmentation. And if you want to allocate small pages when large pages are not available, you can set ORA_LPENABLE=2 since 12c.
Added 18th December 2016
My tests (see http://dbi-services.com/blog/large-pages-and-memory_target-on-windows/) show that it’s not a good idea to mix AMM and Large Pages even on Windows, and this is why I deleted the previous update. Oracle developers have confirmed that we should avoid AMM with Large Pages.