Oracle Memory Oracle Memory Configuration on Configuration on Windows ServerWindows ServerConfiguring Large Memory Configuring Large Memory for Oracle on 32-bit and 64-for Oracle on 32-bit and 64-bit Windowsbit Windows

ObjectivesObjectives

At the end of this module the At the end of this module the student will understand the student will understand the following concepts:following concepts:

OS Memory ConfigurationOS Memory Configuration 32-bit and 64-bit systems32-bit and 64-bit systems Boot.ini fileBoot.ini file /3GB, /PAE, and /NOPAE Switches/3GB, /PAE, and /NOPAE Switches

Oracle Memory ConfigurationOracle Memory Configuration Awe enabledAwe enabled

Performance Implications of 32-bit Performance Implications of 32-bit OracleOracle

Advantages of 64-bit OracleAdvantages of 64-bit Oracle

32-bit Addressing32-bit Addressing 32-bit processors have address 32-bit processors have address

registers that are 32-bits wide (can registers that are 32-bits wide (can address up to 4GB physical RAM)address up to 4GB physical RAM)

Allows per process virtual address Allows per process virtual address space of 4GB range (0 to space of 4GB range (0 to 4,294,967,295 bytes)4,294,967,295 bytes)

Each individual process gets its own Each individual process gets its own virtual address space that can be up virtual address space that can be up to 4GBto 4GB

This virtual address space is divided This virtual address space is divided – system and user space– system and user space

User and System Virtual User and System Virtual Address SpaceAddress Space

4GT (4GB Tuning) with 4GT (4GB Tuning) with /3GB/3GB /3GB = 4GB Tuning Feature – /3GB = 4GB Tuning Feature –

reduces system space to 1GB, reduces system space to 1GB, allowing 3GB for user spaceallowing 3GB for user space

Add to boot.ini file and reboot to take Add to boot.ini file and reboot to take effecteffect

For systems 4GB to 16GB onlyFor systems 4GB to 16GB only

32-bit Systems with PAE 32-bit Systems with PAE (36-bit)(36-bit) PAE- Physical Address ExtensionPAE- Physical Address Extension AKA Large Memory SupportAKA Large Memory Support Allows 36-bit memory addressingAllows 36-bit memory addressing Allows the IA32 processor to address Allows the IA32 processor to address

up to 64GB of physical RAMup to 64GB of physical RAM Add /PAE switch in the boot.ini file Add /PAE switch in the boot.ini file

and reboot serverand reboot server Adds translation layer to memory Adds translation layer to memory

accessaccess

Windows 2003 /NOPAE Windows 2003 /NOPAE Special Case (32-bit)Special Case (32-bit) New for Windows 2003 is the /NOPAE New for Windows 2003 is the /NOPAE

switchswitch Forces Windows 2003 NOT to use Forces Windows 2003 NOT to use

PAE algorithms for memory access PAE algorithms for memory access (there is overhead involved)(there is overhead involved)

Should be used on systems with 4GB Should be used on systems with 4GB or less and hot-add memory devicesor less and hot-add memory devices

Boot.ini Examples (32-bit)Boot.ini Examples (32-bit)

To use /3GB or /PAE, add the To use /3GB or /PAE, add the switches to the boot.ini file and switches to the boot.ini file and reboot the serverreboot the server

Note:Note: Using both /3GB and /PAE together Using both /3GB and /PAE together

conserves the most memory under 4 GBconserves the most memory under 4 GB Using both /3GB and /PAE will limit Using both /3GB and /PAE will limit

memory addressability to 16 GBmemory addressability to 16 GB

32-bit Memory Size/Switch 32-bit Memory Size/Switch ChartChartMEMORY SIZEMEMORY SIZE Boot.ini SWITCHBoot.ini SWITCH

< 4GB< 4GB None (or /NOPAE)None (or /NOPAE)

= 4GB= 4GB /3GB (or /3GB /NOPAE)/3GB (or /3GB /NOPAE)

> 4GB & <= > 4GB & <= 16GB16GB

/3GB /PAE/3GB /PAE

>16GB>16GB /PAE only/PAE only

Support for /3GB and /PAE Support for /3GB and /PAE – 32-bit– 32-bit Windows 2000 Advanced ServerWindows 2000 Advanced Server Windows 2000 Datacenter ServerWindows 2000 Datacenter Server Windows 2003 Enterprise EditionWindows 2003 Enterprise Edition Windows 2003 Datacenter EditionWindows 2003 Datacenter Edition

32-bit Max Memory 32-bit Max Memory Support on x86 based Support on x86 based computerscomputers Windows 2003 Enterprise = 32GB Windows 2003 Enterprise = 32GB

maxmax Windows 2003 Datacenter = 64GB Windows 2003 Datacenter = 64GB

maxmax

AWE for 32-bit OracleAWE for 32-bit Oracle

Address Windowing ExtensionAddress Windowing Extension AWE – a set of APIs that allow AWE – a set of APIs that allow

programs to address memory programs to address memory locations outside of their 4GB virtual locations outside of their 4GB virtual addressing rangeaddressing range

Available through 32-bit addressingAvailable through 32-bit addressing With AWE programs reserve memory With AWE programs reserve memory

as non-paged (will not page out)as non-paged (will not page out)

Implementing AWE with Implementing AWE with 32-bit Oracle32-bit Oracle Set Set thethe /PAE boot.ini flag /PAE boot.ini flag Set the “USE_INDIRECT_BUFFERS=TRUE” Set the “USE_INDIRECT_BUFFERS=TRUE”

parameter in the Oracle initialization file.parameter in the Oracle initialization file. Use the DB_BLOCK_BUFFERS parameter in Use the DB_BLOCK_BUFFERS parameter in

the initialization file instead of the the initialization file instead of the DB_CACHE parameter. DB_CACHE parameter.

The AWE_WINDOW_SIZE registry The AWE_WINDOW_SIZE registry parameter sets up a “swap area” in low parameter sets up a “swap area” in low memory to map data buffers in high memory to map data buffers in high memorymemory Default size is 1 GB.Default size is 1 GB.

Performance Implications Performance Implications of 32-bit Memory of 32-bit Memory ManagementManagement Memory split into two parts is not as Memory split into two parts is not as

efficient as memory in one segmentefficient as memory in one segment Overhead is introducedOverhead is introduced 32-bit systems with 8 GB of RAM may 32-bit systems with 8 GB of RAM may

not perform much better than systems not perform much better than systems with 4 GB of RAM.with 4 GB of RAM.

May need to add 12 GB of RAM to see a May need to add 12 GB of RAM to see a noticable return for the investment.noticable return for the investment.

Performance Implications Performance Implications of 32-bit Memory of 32-bit Memory ManagementManagement Memory above 4 GB can only be used for Memory above 4 GB can only be used for

DATA_BUFFER cache.DATA_BUFFER cache. There is little room for the Shared Pool, Large Pool, There is little room for the Shared Pool, Large Pool,

PGA, etc.PGA, etc. The AWE_WINDOW_SIZE parameter takes up 1 GB of The AWE_WINDOW_SIZE parameter takes up 1 GB of

RAMRAM Leaves only 1 GB of RAM for all other memory types (2 Leaves only 1 GB of RAM for all other memory types (2

GB with /3GB)GB with /3GB) The other memory types are required for supporting The other memory types are required for supporting

memory connections. memory connections.

As a result, Oracle on 32-bit systems is severely As a result, Oracle on 32-bit systems is severely restricted on the number of user connections restricted on the number of user connections supported supported 100s – low thousands with Shared Servers100s – low thousands with Shared Servers

Conserving Memory for User Conserving Memory for User ConnectionsConnections AWE/PAE does not improve user AWE/PAE does not improve user

connectivity for 32-bit Oracleconnectivity for 32-bit Oracle User connections must share a User connections must share a

limited pool of memory, 1-2 GBlimited pool of memory, 1-2 GB The AWE_WINDOW_SIZE registry The AWE_WINDOW_SIZE registry

parameter can be decreased from 1 parameter can be decreased from 1 GB to as low as 512 MB to increase GB to as low as 512 MB to increase user memoryuser memory Tradeoff between upper memory Tradeoff between upper memory

accessibility and user memory sizeaccessibility and user memory size

Conserving Memory for User Conserving Memory for User ConnectionsConnections Oracle initialization parameters can be used to decrease Oracle initialization parameters can be used to decrease

per user memory usageper user memory usage bitmap_merge_area_sizebitmap_merge_area_size create_bitmap_area_sizecreate_bitmap_area_size hash_area_sizehash_area_size open_cursorsopen_cursors sort_area_size (sort_area_retained_size) sort_area_size (sort_area_retained_size)

The Orastack command can be used to decrease memory The Orastack command can be used to decrease memory use per threaduse per thread Default allocation is 1 MBDefault allocation is 1 MB Risky to decrease below 700KB; any change must be testedRisky to decrease below 700KB; any change must be tested Example usage:Example usage:

orastack oracle.exe 700000 orastack oracle.exe 700000 orastack tnslsnr.exe 700000 orastack tnslsnr.exe 700000 orastack svrmgrl.exe 700000 orastack svrmgrl.exe 700000 orastack sqlplus.exe 700000 orastack sqlplus.exe 700000

Windows 2003 64-bit Windows 2003 64-bit SystemsSystems

64-bit versions of Windows have 64-bit versions of Windows have implemented up to 16 terabytes of implemented up to 16 terabytes of virtual address spacevirtual address space

Don’t use PAE or /3GB with 64-bitDon’t use PAE or /3GB with 64-bit 64-bit programs use 16 terabyte 64-bit programs use 16 terabyte

tuning model (8TB user / 8TB kernel)tuning model (8TB user / 8TB kernel) Compare to 32-bit tuning mode Compare to 32-bit tuning mode

(2GB user / 2GB kernel)(2GB user / 2GB kernel) No performance penalties for large No performance penalties for large

memorymemory Unlimited user connectivityUnlimited user connectivity

64-bit Memory Support on 64-bit Memory Support on Itanium based computersItanium based computers Windows 2003 Enterprise 64-bit = Windows 2003 Enterprise 64-bit =

1TB max1TB max Windows 2003 Datacenter 64-bit = Windows 2003 Datacenter 64-bit =

1TB max1TB max

64-bit Memory Support on 64-bit Memory Support on EM64T and Opteron based EM64T and Opteron based computerscomputers Windows 2003 Standard 64-bit = Windows 2003 Standard 64-bit =

64GB64GB Windows 2003 Enterprise 64-bit = Windows 2003 Enterprise 64-bit =

1TB max1TB max Windows 2003 Datacenter 64-bit = 1 Windows 2003 Datacenter 64-bit = 1

TB maxTB max

Monitor Oracle MemoryMonitor Oracle Memory

Oracle for Oracle for Windows Windows Performance Performance CountersCounters Oracle Oracle

%phyreads/gets %phyreads/gets shows Buffer Cache shows Buffer Cache performanceperformance

Should be below Should be below 10% - few physical 10% - few physical reads as a reads as a proportion of all proportion of all reads; otherwise, reads; otherwise, more memory may more memory may be requiredbe required

ReviewReview

What flag is set in the boot.ini file to What flag is set in the boot.ini file to use memory above 16 GB?use memory above 16 GB?

Which two flags may be set in the Which two flags may be set in the boot.ini file for memory between 4 boot.ini file for memory between 4 GB and 16 GB?GB and 16 GB?

What are the performance What are the performance implications of 32-bit memory implications of 32-bit memory management for Oracle?management for Oracle?

What tool may be used to monitor What tool may be used to monitor Buffer Cache efficiency?Buffer Cache efficiency?

SummarySummaryThe following topics were discussed The following topics were discussed in this training module:in this training module:

OS Memory ConfigurationOS Memory Configuration 32-bit and 64-bit systems32-bit and 64-bit systems Boot.ini fileBoot.ini file /3GB, /PAE, and /NOPAE Switches/3GB, /PAE, and /NOPAE Switches

Oracle Memory Configuration with Oracle Memory Configuration with AWEAWE

Performance Implications of 32-bit Performance Implications of 32-bit OracleOracle

Advantages of 64-bit OracleAdvantages of 64-bit Oracle