Windows AWE to increase Oracle Data Buffer Size Oracle Tips by Burleson Consulting

If you are an Oracle shop using 32-bit Oracle Windows, it is highly likely that you are wasting RAM resources. Most Oracle servers come with 4-gig or 8-gig RAM while a 32-bit windows SGA has a maximum size on only about 1.3 gig. This means that the db_block_buffers (db_cache_size)cannot be large enough to fully-cache the working set of frequently-referenced rows, causing excessive disk I/O.

Until of you on a 64-bit version of Windows Oracle (Windows Advanced Server 2003), you can use either the 4GT or the AWE tools to allow Oracle to use your expensive RAM to cache additional data buffers. To enable 4GT, add the /3GB switch to the Boot.ini file.

AWE stands for "Address Windowing Extension" and allows 32-bit Windows Oracle to have up to 64 gig of database buffers. AWE has been available after Oracle8i (8.1.6).

Internally, AWE Moves database buffers out of the 3GB address space "above the line", leaving low-memory RAM for more connections and larger sort areas.

In addition we see the following features of AWE:

• 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.

Using AWE in Windows is relatively simple. From the Oracle 3 gig address space, we allocate space for a 'window' onto the whole of the db buffers.

• The size of this window is user-configurable.

• 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 use_indirect_data_buffers=true

• 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 up to available memory

Also see: