Don?t waste RAM on 32-bit Oracle Windows servers
Oracle Tips by Burleson Consulting
I grew-up believing that wasting
something was a mortal sin against God, and I remain very stingy
with server resources as an Oracle DBA. One of the largest
wastes I see are Oracle Windows servers that some with 4-gig or
8-gig RAM, but the SGA is only sized to 1.5 gig. This leaves
many gigabytes of valuable RAM memory going to waste!
Wasting RAM and this is very
common on 32-bit Oracle Windows databases, especially servers with 8
gig RAM and a low high-water mark for external PGA RAM usage.
As a refresher, when you don't have enough RAM to fully-cache your
working-set of frequently-referenced table and index blocks, your
database does unnecessary I/O:
The Solutions to Windows RAM
There are two solutions used by
most shops. You have two choices, 4GT or AWE.
To enable 4GT, add the /3GB
switch to the Boot.ini file. AWE is
best and you can use the entire RAM for the Oracle database (less
20% for the OS).
In my experience the increased
data caching can make a huge difference and for a dedicated Windows
server you only need to reserve 20% of the RAM for the OS, and save
the rest for SGA and PGA.
- In Windows NT Server v4.0 EE, 4
gig RAM Tuning (4GT) was added which allows SGA up to 3 gig of
memory as opposed to the standard 2 gig. (Yes, 4GT = 3 gig)
- Gives Oracle up to 50% more memory. (but still not enough)
- For larger systems 64-bit Oracle
allows up to 512 gig SGA sizes.
- All that's needed is an added
boot.ini switch -- /3GB
- AWE (Address Windowing
Extension) - Allows 32-bit Windows Oracle to have up to 64 gig of
- Available after Oracle8i
- Moves database buffers out of
the 3GB address space "above the line", leaving low-memory RAM for
more connections, larger sort areas, etc...
- The Win32 AWE (Address Windowing
Extension) calls are used which provide a fast map/unmap interface
to "above the line" RAM.
- When running on a machine with
>16GB of RAM, Oracle can either use the 4GT feature or the memory
above 16GB, but not both. This is a Windows limitation.
- From the Oracle 3 gig address
space, we allocate space for a 'window' onto the whole of the db
- The size of this window is
- The larger the window, the more
buffers are quickly available, however the larger the window, the
less space there is for other memory structures like shared pool,
connections, PGAs, etc.
- Set the parameter
- Set AWE_WINDOW_SIZE in the
registry to the number of bytes of address space to use for your
window onto extended memory. The default is 1GB.
Increase db_block_buffers to a higher value (expressed in blocks) up
to available memory.
For more information on sizing RAM
regions for minimum wastage, see my book "Oracle
Tuning: The Definitive Reference".
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.