Oracle Memory Allocations on Windows

Oracle Memory Allocations on Windows

Oracle's implementation of database on Windows has some specifics not seen on other platforms (*nix, Linux). Main difference is that all Oracle processes (PMON, SMON, even server processes) run within single OS process as separate threads. The reason for this is that on Windows you can't start a background process other than a "service". Otherwise you have to start it in a user session and it will be stopped when session is ended (user logged out). So, Oracle had to "pack" all the background processes as threads within a single process that you see in "Services" panel on Windows as your "OracleServiceORCL" or something else with ORCL representing your database's SID.
Main impact of this architecture is that the maximum amount of memory that can be used for both SGA and PGA is limited by a maximum amount of memory available to single process. On Windows (we assume a 32-bit platform in this article), the default maximum user addressable memory is 2GB. It is a limitation imposed by a 32-bit addressing (4GB) and fact that Windows reserves 2GB out of user space for kernel memory.
So, this means that sum of SGA and PGA cannot be over 2GB... Not a very pleasant situation, given that nowadays it is rather common to have a cheap Intel server running Windows with Oracle with 4GB of RAM.
What can be done to get around this? Well, good news is that you can do something...
First, you can use a /3GB switch in boot.ini file. Just put it like this:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(1)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect /3GB
What does it do? Basically, what it looks like - pushes the limit of 2GB to 3GB? How? - Windows reserves not 2GB but only 1GB for kernel use. This gives us extra 1GB for user process.
If you have over 4GB of RAM, you can also use another mecahnism in Windows to leveragr this memory. It is called Address Windowing Extensions (AWE) and is enabled by /PAE switch in boot.ini:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(1)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect /3GB /PAE
But just setting the switch is not enough. Now you need to configure Oracle to use the extra memory you've got in there. How Oracle does it is that it "maps" the extra memory through a "window" in the usual memory space. One limitation here is that Oracle can do it only for buffer cache, not shared pool.
So, what you need to set in Oracle is:
• USE_INDIRECT_DATA_BUFFERS=TRUE in init.ora
• increase DB_BLOCK_BUFFERS in init.ora (Note: if you use Oracle 9.2 and use DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS, you will get an error starting instance. Comment out DB_CACHE_SIZE and use DB_BLOCK_BUFFERS instead)
• You might want to adjust a registry setting in HKLM\Software\Oracle\Homex called AWE_WINDOW_MEMORY. It specifies the size of the "window" that Oracle will use for using the extra memory. See Metalink Note 225349.1 for details on calculating miminum size of this setting.

So, to summarize all of the above:
• use /3Gb switch to increase memory space for Oracle from default 2Gb to 3GB
• use /PAE and USE_INDIRECT_BUFFERS to utilize memory over 4GB
• remember, that SGA and PGA come from the same address space. So, do not set your SGA to 3GB - you will have no memory for client processes then. You need to estimate client processes memory requirements and size SGA accordingly to have enough memory for PGA. (On 9i you can use PGA_AGGREGATE_TARGET to bettre manage PGA)