The relation between an Oracle instance and memory in Windows

Posted by David Yahalom under Windows, Hardware, Oracle

The Oracle instance memory allocation works differently in Windows then it does in UNIX-Linux like operating systems.

In Windows environments, on starting up the Oracle instance all global memory pages are committed (like the buffer cache, redo log buffer and the library cache). However, and this is an important difference compared to POSIX operating systems, only a small number of these memory pages are actually cached in memory upon instance startup causing most of the SGA not to be part of  Oracle’s active working set.

This forces Oracle to compete on equal grounds with other processes. The O/S might swap certain pages from Oracle’s working set in memory to disk during periods of increased activity or when other processes are paging more and compete for RAM. Windows pages according to activity using an MRU-like mechanism, so when paging is going-on system-wide, Windows will try to reserve RAM pages to the most active process, the one that is paging the most.

If Windows decides to swap the database instance to disk, this will cause a severe performance degradation for the Oracle Database and might even grind the instance to a complete halt.

There are two registry parameters that exist and allow us to manipulate the working set of the Oracle process. These are:

• ORA_WORKINGSETMIN or ORA_%SID%_WORKINGSETMIN:
  Minimum working set for the ORACLE.EXE process (units = MB)
• ORA_WORKINGSETMAX or ORA_%SID%_WORKINGSETMAX:
  Maximum working set for the ORACLE.EXE process (units = MB)

You can set these parameters under:

• For single ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
• For multiple ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx (for multiple homes)

ORA_WORKINGSETMIN can be used to prevent the pages allocated to the Oracle process from dropping below the defined threshold (in MB) until the instance is shutdown.

The biggest benefit of setting these parameters will be in an environment where Oracle coexists with other applications. Although it can be beneficial in other scenraios as well, such as when production and test instances are running on the same physical machine.

Another very useful parameter that exist in the Win32/64 platform is: PRE_PAGE_SGA. This parameter causes Oracle will force Oracle to allocate all SGA pages upon instance startup thus bringing all of them to memory as the working set of the Oracle executable.

This will allow the instance to reach maximum performance more quickly rather than through an incremental build up as pages are loaded on a need-to basis.

Combining ORA_WORKINGSETMIN  with PRE_PAGE_SGA will force the Oracle instance to start above the minimum threshold and not drop below. Using ORA_WORKINGSETMIN in isolation will cause the Oracle working set rises above the threshold it will not drop below.