Wednesday, April 23, 2008

Enabling > 4GB RAM Support with Oracle 10g Running on Windows Server 2003 32-bit Ed.

  1. Add the /3G, USERENV, and /PAE switches to the boot.ini file, on the Windows Server 2003 Server. For example: multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003" /fastdetect /NoExecute=OptIn /3GB /USERENV /PAE
  2. The account that is used to start the Oracle Service must be given the "Lock Pages in Memory" right. You can do this by a) going to Administrative Tools, from the Start menu or Control Panel, b) opening Local Security Policy, c) opening Local Policies, then d) opening User Rights Assignment; e) open/edit the "Lock Pages in Memory" key, f) then add the Oracle Service account to this list.
  3. Create an AWE_WINDOW_MEMORY String (REG_SZ) registry key in the Windows' Registry, under HKLM\Software\Oracle\h, where h is the ORACLE_HOME you're targeting. Set this value to the following calculated value (you can calculate this value via the calculator below):
    SETS_PER_POOL = 2 * CPU_COUNT (if VLM is enabled; VLM is enabled by setting USE_INDIRECT_DATA_BUFFERS=TRUE) SETS_PER_POOL = CPU Count / 2 (If VLM is NOT enabled _DB_BLOCK_LRU_LATCHES = (8 * SETS_PER_POOL) AWE_WINDOW_MEMORY (min.) = ((4096 * DB_BLOCK_SIZE * _DB_BLOCK_LRU_LATCHES) / 8) * 1.03125
    So, in a server with 8GB of RAM with 4 CPUs and a DB_BLOCK_SIZE of 8192, the AWE_WINDOW_MEMORY would be ((((2 * 4) * 8) * 4096 * 8192) / 8) * 1.03125 = 276824064.
  4. Set the _DB_BLOCK_LRU_LATCHES DB instance parameter according to the preceding formula.
  5. Set the DB instance parameter use_indirect_data_buffers to TRUE.
  6. Ensure that the DB instance parameter db_cache_size is NOT set.
  7. Set the DB instance parameter db_block_buffers to the following calculated value (you can use the calculator below instead):
    db_block_buffers = (Total RAM - 4GB + AWE_WINDOW_MEMORY) / DB_BLOCK_SIZE
    So, using the preceding example, the db_block_buffers parameter value would be 786432.
  8. Set the DB instance parameter PGA_AGGREGATE_TARGET to ( * 80%) * 40%.
  9. Ensure the DB instance parameter sga_target is NOT set.
  10. Set the DB instance parameter shared_pool_size to AWE_WINDOW_MEMORY * 1.27923, at the very minimum.
  11. Ensure that the streams_pool is NOT set to 0 if you will be using utilities such as the Data Pump ones (e.g., expdp and impdp). 1MB is a good start.
  12. Restart the server and related Oracle services and DB(s).

Note: The CPU count includes hyperthreaded processors, so in a dual-core processor, the CPU count would be 2, not 1.

Set these values:
Is VLM enabled?
CPU_COUNT =
DB_BLOCK_SIZE =
Total RAM =
Results:
SETS_PER_POOL
_DB_BLOCK_LRU_LATCHES =
AWE_WINDOW_MEMORY (min.) =
AWE_WINDOW_MEMORY (rec.) =
DB_BLOCK_BUFFERS =
PGA_AGGREGATE_TARGET (min.) =
PGA_AGGREGATE_TARGET (rec.) =
SHARED_POOL_SIZE (min.) =
SHARED_POOL_SIZE (rec.) =

1 comment:

Anonymous said...

in oracle 10.2.0.4
ORA-25138: initialization parameter DB_BLOCK_LRU_LATCHES is obsolete.