AWE -What is It and How to use it?

32-bit Operating systems provide 4Gb of addressable space to applications. This includes memory for the user application and also for the system.

When running Oracle on 32-Bit Windows, we can have total memory limited to 1.7 Gb only. If you allocate memory more then 1.7 GB or if it increases during course of database operation, then you are likely to encounter errors such as ORA - 4030, ORA - 12500 or ORA - 27102.

There are few solutions to overcome this problem. I am going to discuss them in this post. Feel free to post any Questions/doubts you have.

1)Using /3GB switch

To increase the virtual addressable space for processes and applications, you need to use /3GB switch. The /3GB switch allows for providing memory upto 3GB for processes (In actual it is nearly 2.7 GB for Oracle) by reducing memory available to OS to 1 GB.
To implement it, you need to modify the boot.ini as follows and restart the server

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" /3GB

Note: -"????" in the above example can be the programmatic name of any of the following operating system versions:

- Windows XP Professional

- Windows Server 2003

- Windows Server 2003, Enterprise Edition

- Windows Server 2003, Datacenter Edition

- Windows 2000 Advanced Server

- Windows 2000 Datacenter Server

- Windows NT Server 4.0, Enterprise Edition

2)Using /PAE switch

Physical Address Extention (PAE) allows applications to have virtual addressable space beyond 4GB. PAE maps up to 64 GB of physical memory into a 32-bit (4 GB) virtual address space using either 4-KB or 2-MB pages.

This can be enabled using /PAE switch in boot.ini as follows

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" <strong>/PAE</strong>
<span style="font-family: Arial;">
</span>

Note: -"????" in the above example can be the programmatic name of any of the following operating system versions:

- Windows 2000 Datacenter Server

- Windows 2000 Advanced Server

- Windows 2003 Data Center Edition (32-Bit)

- Windows 2003 Enterprise Edition (32-Bit)

PAE is also known by name Address Windowing Extensions (AWE). This basically allows Oracle to have larger buffer cache. In addition to addition to boot.ini, you need to make some changes at database level also

1) Add parameter USE_INDIRECT_DATA_BUFFERS=TRUE to spfile/pfile

2) Remove DB_CACHE_SIZE parameter from pfile/spfile and replace it with DB_BLOCK_BUFFERS. To calculate it you need to use following DB_CACHE_SIZE/DB_BLOCK_SIZE

How PAE/AWE works?

PAE allows the system to expand the number of bits that can be used to address physical memory from 32 bits to 36 bits. Out of 1.7GB allocated to Oracle, 1 GB is allocated to a “WINDOW” which is used to map the buffer cache and allows for bigger SGA. This acts like a “Swap Area” where all the blocks are mapped into this window and then provided to the application.It is advisable to use /3GB switch along with /PAE switch so that addressable space is around 2.7GB.

To do it, change boot.ini to as follows

<span style="font-size: 12pt; font-family: Arial;"> </span><span style="font-size: 12pt; font-family: Arial;">multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" <strong>/3GB /PAE</strong></span>

Note: - Using both /3GB and /PAE will limit memory addressability to 16 GB. In case you wish to address memory beyond 16GB, you will be required to unset /3GB switch.

AWE Window is specified by parameter AWE_WINDOW_SIZE. The size of this window is defined by a registry setting in the HOME key for Oracle (HKLM\Software\Oracle\Homex) called AWE_WINDOW_SIZE. Default value for AWE_WINDOW_SIZE is 1 GB and no entry in registry will mean that it is running at default value. Value for this parameter has to be specified in Bytes in registry.

Metalink Note:225349.1 and Oracle Docs mentions that you need to use AWE_WINDOW_MEMORY whereas Microsoft site mentions using AWE_WINDOW_SIZE in registry.

This 3GB space will be used by following components:

- The Value for AWE_WINDOW_SIZE- SGA (excluding Buffer Cache)- Overhead for Oracle.exe and DLL’s -Stack space for all threads (Defaults to 1MB/thread, unless orastack is used)- PGA and UGA memory for all user sessions

To understand it, lets take an example. Suppose we need to set memory for a new system using /3GB and /PAE

Out of 2.7GB of addressable space, 1 GB will be used for AW_WINDOW_SIZE. We are left with 1.7 GB which has to be used for SGA shared_pool_size + large_pool_size + java_pool_size + log_buffers +steams_pool_size)

**Please Note that you can not use ASMM i.e SGA_TARGET when using AWE.

AWE_WINDOW_SIZE can be decreased from 1GB to a lower value. This has to be calculated and set judiciously as a lower value can increase mapping/unmapping activity and can also lead to CPU Spinning. Please refer below note for details on calculating minimum AWE_WINDOW_SIZE parameter

Note:225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms

Other ways of reducing Memory usage

1) Use MTS (Shared Server Configuration) as this will reduce the number of processes and ultimately reducing memory usage.

2) Reduce value for *area_size parameter’s (if using Manual PGA management)

3) You can also use orastack to reduce the memory usage per thread (which defaults to 1 MB)

cmd>orastack oracle.exe 700000

cmd>orastack tnslsnr.exe 700000

cmd>orastack svrmgrl.exe 700000

cmd>orastack sqlplus.exe 700000

This sets the value to 700KB beyond which it is risky to decrease the value.

Issues with AWE

1) To use AWE  we need to use DB_BLOCK_BUFFERS parameter. As we cannot have DB_BLOCK_BUFFERS and DB_CACHE_SIZE parameter being used simultaneouly , we need to unset DB_CACHE_SIZE parameter. This will  mean that you cannot use Automatic Shared Memory Management (ASMM) with AWE i.e You have to unset SGA_TARGET parameter. Similarly you cannot use MEMORY_TARGET (11g ) parameter with AWE. Failure to do so will lead to

ORA-00825: cannot set DB_BLOCK_BUFFERS if SGA_TARGET or MEMORY_TARGET is set

Cause: SGA_TARGET or MEMORY_TARGET set with DB_BLOCK_BUFFERS set.

Action: Do not set SGA_TARGET, MEMORY_TARGET or use new cache parameters, and do not use DB_BLOCK_BUFFERS which is an old cache parameter.

2) Setting Low value for AWE_WINDOW_SIZE can lead to CPU spin issues.

3) Applications doing large amount of I/O will see Performance impact due to Paging.

4) Due to use of AWE, additional Overhead is introduced.

5) Memory beyond 4GB can only be used for Buffer Cache. So In case you are facing Shared pool memory issues or want large number of connections, then AWE cannot help.

Further Readings

Note 225349.1 - Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms
Note 46053.1 - Windows NT Memory Architecture Overview

http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

http://www.microsoft.com/whdc/system/platform/server/PAE/pae_os.mspx

http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/architec.htm#i1005826

54 thoughts on “AWE -What is It and How to use it?

  1. Pingback: Oracle10g ORA-27300 error « Mike R’s Blog

  2. You need to edit this paragraph, there is conflicing information about the use of db_block_buffers and db_cashe_size. One set to use it, the says not to:

    1) As you need to unset DB_CACHE_SIZE and use DB_BLOCK_BUFFERS, you cannot use Automatic Shared Memory Management (ASMM) i.e You have to unset SGA_TARGET parameter. Similarly this cannot be used with MEMORY_TARGET (11g ) parameter. Failure to do so will lead to

    ORA-00825: cannot set DB_BLOCK_BUFFERS if SGA_TARGET or MEMORY_TARGET is set

    Cause: SGA_TARGET or MEMORY_TARGET set with DB_BLOCK_BUFFERS set.

    Action: Do not set SGA_TARGET, MEMORY_TARGET or use new cache parameters, and do not use DB_BLOCK_BUFFERS which is an old cache parameter.

  3. I have tried to edit the paragraph. Let me know if it makes sense. Basically I wish to say that DB_BLOCK_BUFFERS and DB_CACHE_SIZE parameters cannot be used at same time in your instance.

  4. Hi,
    We are runing Oracle 8i 1.7.0.0 Db on Windows Server 2003 EE.Our Server contains 4GB RAM,Db Total SGA size is 700MB but the whole SGA is allocated in page file as when we down the Db page file usage in Task Manager goes approx zero but as we start the Db it goes to 960 MB and as users start connecting it increases upto 1.5 GB.We have added “/3GB /PAE” in boot.ini file and add “USE_INDIRECT_DATA_BUFFERS=TRUE” in pfile but still SGA is allocated in page file which decreses the performance.
    Plz help us out.

  5. /3gb and /PAE are provided to overcome memory sizing limit due to 32 bit addressing problem. This has nothing to do with paging. I think you should check in with your sys admin and check what all processes are consuming memory. You can use /3gb switch to allow oracle to use 3 gb memory . You does not need to set /PAE as you have SGA of 700Mb only.

    • question about USE_INDIRECT_DATA_BUFFERS = TRUE and DB_BLOCK_BUFFERS in the init.ora. I have not added either of these parms in my init.ora and seems to be running fine except for oracle cpu cycling to 100%. what is your recommended setting of DB_BLOCK_BUFFERS for my settings?
      Windows 2003 Server Standard Edition 32 bit SP2
      4GB RAM, Oracle 10g, /PAE and /3GB, AWE_WINDOW_SIZE not set(using default).

      current init.ora;
      db_block_size=8192
      sga_max_size=2400M
      sga_target=2300M
      db_cache_size=40M
      shared_pool_size=40M
      log_buffer=655360
      pga_aggregate_target=300M
      workarea_size_policy=AUTO

      With Oracle auto controling mem size, current allocation;
      Shared Pool 144
      Buffer Cache 2136
      Large Pool 8
      Java Pool 8
      Other 8

      • Dean,

        Your server has 4 Gb of RAM, so you should be fine with /3Gb switch alone. /PAE switch is useful when you need to have a higher buffer cache on system with >4Gb RAM. Anyways I would recommend reducing your sga to 2000M so as to keep 700M for PGA and oracle binary overheads.

        Hope this helps !
        Regards
        Amit

        • I forgot to mention that we are running oracle on a VM with dbf on a SAN storage, so there is extra memory for processes to draw on, just not visible, and i can have more memory assigned. I also thought SGA max includes (cache, shared pool, large pool, and java pool)and did not include PGA, but SGA, PGA, OS memory total cannot exceede available physical memory. Also IT adds /NOEXECUTE=optout to all servers which i think enables /PAE by default. This being said, why does my oracle start without USE_INDIRECT_DATA_BUFFERS = TRUE and DB_BLOCK_BUFFERS called out in my init.ora and what would DB_BLOCK_BUFFERS setting be?

          • Dean,

            You had mentioned that you are running Windows Server 2003 (Standard Edition) which does not provide functionality of enabling /PAE switch.

            Secondly to use you need to se /PAE switch explicitly (not sure what NOEXECUTE function does). In face /NOEXECUTE option is present by default.

            As mentioned earlier , you are able to start your database because you are not crossing 2.7 Gb limit. To prove it, lets try this

            a) Remove sga_max_size and sga_target
            b) set USE_INDIRECT_DATA_BUFFERS =true
            c) Set buffer cache to 3G i.e set db_block_buffers=393216 (assuming you have db_block_size=8k) . Does your oracle instance start?

  6. Hi Amit,

    I found this page as very useful one. thanks for this.
    My Boot.ini look like this,

    [boot loader]
    timeout=30
    default=multi(0)disk(0)rdisk(0)Partition(1)\Windows
    [Operating Systems]
    multi(0)disk(0)rdisk(0)partition(1)\windows=”Windows Server 2003, Enterprise”/noexecute=optout/fastdetect

    My oracle Database details:

    Version: 10.2.0.4.0
    OS: Windows 2003 Server R2 Enterprise Edition, Service Pack 2
    Ram: 8GB
    SGA_TARGET=1200M
    SGA_MAX_SIZE=1552M
    PGA_AGGREGATE_TARGET=850M
    DB_CACHE_SIZE=0
    DB_BLOCK_BUFFERS=0
    DB_BLOCK_SIZE=8192
    USE_INDIRECT_DATA_BUFFER=FALSE
    PROCESS=300
    SESSIONS=350

    Our database is frequently facing TNS-12518. Listener couldnot hand off the client connections and this occurs when the number of sessions reaching nearly 95 and above.

    I think this might be the memory problem that occurs on windows machine with oracle.

    Please suggest me what i have to set in boot.ini. I have also planned to reduce the memory allocated to both SGA and PGA.

    Your Suggestions please.

  7. Hi Srinivas,

    I believe you can set /3gb switch to increase the memory available to Oracle processes. TNS-12518 is caused by lack of resources (memory ,etc) so keeping /3gb swich will allow you to give more memory for processes. Yes, if possible you can reduce the SGA. Note that currently only 250M is available for oracle prcesses.

    Cheers
    Amit

  8. Amit, great post, a bit confusing for a newbie with VL memory needs on 32bit… So we have a typical situation of a Win 2003 Ent Server. /PAE and /3GB enabled. I have not made any changes to the AWE_WINDOW_SIZE, since 1GB is ideal for our situation. We want to allocate Oracle 5GB of RAM, out of the 8G – this is possible right? How can we do this? This is what I have played with so far with no luck (ORA-27100: shared memory realm already exists)

    I did take out the automated memory management parameters, and set the following up:

    LARGE_POOL_SIZE = 8M
    JAVA_POOL_SIZE = 8M
    SHARED_POOL_SIZE = 1536M
    USE_INDIRECT_DATA_BUFFERS = TRUE
    DB_BLOCK_BUFFERS = 4194304

    Thanks,
    Alex

    • Alex,

      If you have put both /3Gb and /PAE switch you should be able to address 2.7 G RAM. Is it 9i or 10g?
      Basically you should also consider following memory parameters

      1)Shared_pool_reserved_size – >Set automatically 5% of shared pool size.So this would also be occupying 75 M in your config.
      2) In case 9i then overhead memory (due to setting of db_files,processes,sessions parameters) is also taken from sga. In case of 10g this is allocated from shared_pool_size.

      Along with SGA you need to use this space for PGA too.. Can you try reducing Shared_pool_size to 1200M and see if it starts..

  9. What should page file size be? I found conflicting inf:

    46001.1 says:
    For machines with greater than 2GB of main memory, a 2GB pagefile should
    be adequate.

    But 225349.1 says:
    The size of SGA + Oracle’s overhead must not exceed the available virtual memory.

    The size of buffer cache depends on the available virual memory and can be calculated with
    buffer cache = db_block_buffer * db_block_size

  10. Page file size is actually swap memory size which is configured so that memory which cannot be fit into physical ram is stored in disk (Paging)..Note 225349.1 wants to say that your total memory used by oracle should not exceed Physical memory +page file size..

    try to have sufficient Physical memory on server as paging will degrade the performance drastically..

    Cheers
    Amit

  11. Hi Alex

    I have a 4GB RAM & Operating System is windows 2003 server and I want to increase the SGA_max_size.But operating system is creating error when i want to increase the SGA_MAX_SIZE.

    So,pls help me how can i work on that.

    rgds
    Borhan Uddin
    +88 01818 294004

  12. Borhan,

    what is sga_max_size you are trying to use. Did you try making entry for /3Gb switch in boot.ini. I have very little information available with me to help.Can you give details along with error messages..

    – Amit

  13. Have Win 2003 Enterprise Edition 32 bit, Oracle 9i, 6GB RAM, /PAE /3GB. Is it allowed to use sga_max_size, which parameter must be < 2,7 GB?

  14. You can use sga_max_size with /PAE switch. You would only be required to use db_block_buffers instead of db_cache_size. Also set USE_INDIRECT_DATA_BUFFERS = TRUE

    I did not understand your second question “which parameter must be < 2,7 GB ? "

      • PAE allows the system to expand the number of bits that can be used to address physical memory from 32 bits to 36 bits. Out of 1.7GB allocated to Oracle, 1 GB is allocated to a “WINDOW” which is used to map the buffer cache and allows for bigger SGA. This acts like a “Swap Area” where all the blocks are mapped into this window and then provided to the application.It is advisable to use /3GB switch along with /PAE switch so that addressable space is around 2.7GB.

  15. I have problem to calculate the parameter. Is it correct, that AWE_WINDOW_SIZE – SGA_MAX_SIZE (excluding Buffer Cache)- Overhead for Oracle.exe and DLL’s -Stack space for all threads (Defaults to 1MB/thread, unless orastack is used)- PGA and UGA memory for all user sessions must be <= 2,7GB? PGA I can see on Oracle (show parameter), how can I see UGA and how much is the Overhead for Oracle.exe?

  16. Overhead for oracle version is upto 100m. I would suggest using following values

    shared_pool_size=700m
    large_pool_size=100m
    java_pool_size (if required)
    pga_aggregate_target to 500M.
    workarea_size_policy=auto

    And try starting the oracle instance. Note that 1 gb of memory will be taken for AWE_WINDOW_SIZE. In case of dedicated server mode, your uga will be allocated from pga. So following query should give you current PGA allocation

    select sum(PGA_ALLOC_MEM)/1024/1024 from V$process;

    You can use V$librarycache to monitor shared pool . If there are lot of reload’s you might have to increase shared pool
    Please note that we pga_aggregate_target is not hard limit so it is possible that you can have total pga consumption more then set value. In case total memory exceeds 2.7 G , then your new connection will start failing with ora-27300 errors and existing session might fail with ora-4030. If this happens then you might consider lowering shared_pool or altering awe_window_size to a lesser value.

  17. At the moment we don’t use USE_INDIRECT_DATA_BUFFERS = TRUE, we get error ora-4030.
    The Win 2003 Enterprise Server has 6 GB RAM, on ORacle 9.2.0.4 we have ~450 sessions. The parameter at the moment are:
    sga:
    Total System Global Area 1720790404 bytes
    Fixed Size 458116 bytes
    Variable Size 738197504 bytes
    Database Buffers 981467136 bytes
    Redo Buffers 667648 bytes

    sga_max_size = 1720790404
    java_pool_size = 159383552
    large_pool_size = 192937984
    shared_pool_size = 343932928
    pga_aggregate_target = 339738624
    log_buffer = 524288
    db_cache_size = 981467136

    On weekend I want to change parameter USE_INDIRECT_DATA_BUFFERS = TRUE. AWE I want set to 400 MB, not standard 1GB. It is an verry importent server so I must be sure wat I do 🙂

  18. Andrea,

    I would recommend you check Metalink Note 225349.1 before going ahead. There are also few observations

    1)You cannot set db_cache_size parameter, You need to use db_block_buffers parameter. e.g to have db_cache_size of 900M, with db_block_size of 8k , you need to set db_block_buffers to (90081024*1024/(8*1024)) = 115200

    2) Check note for estimating minimum AWE_WINDOW_SIZE parameter. AWE_WINDOW_SIZE parameter can play very important role in determining DB performance.

    Quoting 10g docs (http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/architec.htm#i1005826)

    AWE_WINDOW_MEMORY, a new registry parameter specific to VLM, tells Oracle Database how much of its address space to reserve for mapping in database buffers. It defaults to a value of 1 GB, which should be suitable for most installations. If DB_BLOCK_SIZE is large, however, the default AWE_WINDOW_MEMORY value of 1 GB may not be sufficient to start the database.

    Increasing the value of AWE_WINDOW_MEMORY will improve performance, but it will also limit the amount of memory available for other Oracle Database threads (like foreground threads). Clients may see “out of memory” errors if this value is set too large. As a general guideline, increase the AWE_WINDOW_MEMORY registry value by 20 percent.

    For example, if DB_BLOCK_SIZE is set to 8 KB, AWE_WINDOW_MEMORY is set to 1 GB, and the number of LRU latches is set to 32 (16 processor computer), then database startup fails with out of memory errors 27102 and 34. Increasing the value of AWE_WINDOW_MEMORY to 1.2 GB fixes the problem.

    Metalink note 225349.1 gives the formula for determining the AWE_WINDOW_SIZE parameter. Note that you can have high buffer cache as only memory limiting to AWE_WINDOW_SIZE will be taken from 2.7 Gb limit. This is advantage of using /PAE switch.

    HTH

    Cheers
    Amit

  19. I have read Metalink Note 225349 and SAP Doc 441663. You think it is not possible to set AWE_WINDOWS_MEMORY to 400MB? I have read that MIN(AWE_WINDOW_MEMORY)=(4096 * DB_BLOCK_SIZE * _DB_BLOCK_LRU_LATCHES)/8 in oure cas it is (4096 *4096 *64)/8 = 134217728 Bytes (134 MB)
    (we have 4CPUS).

  20. No I am not saying that you can’t set it to 400 M. I don’t remember but I read somewhere that this shouldn’t be set to value less then 700m (Really not sure if this I read or it’s my pure imagination 🙂 ). As mentioned in docs, low value for AWE_WINDOW_SIZE can have impact on performance. Anyways I think value of 700M should go with your config

    AWE_WINDOW_SIZE = 700M
    Shared_pool_size = 320 M
    Java_pool_size = 150M
    large_pool_size = 190M
    log_buffer = 5M

    Note that pga_aggregate_target will not be taken from 1.7 Gb(sga_max_size.)

  21. Thank you for help, server is running fine since one month. Tis is what I have done: – Backup spfile to pfile
    – stopped Database
    – Regedit HKEY_LOCAL_MACHINE\Software\Oracle\HOME0 AWE_WINDOW_MEMORY set to 419430400 (400M)
    – change pfile:
    not changed: db_block_size = 4096
    not changed: shared_pool_size = 343932928
    not changed: java_pool_size = 159383552
    changed: large_pool_size = 100M
    dropped: db_cache_size = 981467136
    set: db_block_buffers = 300000
    set: use_indirect_data_buffers = TRUE
    set: _db_block_lru_latches = 64
    changed: processes = 500
    set sga_max_size on pfile was not posible, I must change after I have created spfile

  22. Thank you for help, server is running fine since one month, that is what I have done:
    – Backup spfile to pfile
    – stopped Database
    – Regedit HKEY_LOCAL_MACHINE\Software\Oracle\HOME0 AWE_WINDOW_MEMORY set to 419430400 (400M)
    – change pfile:
    not changed: db_block_size = 4096
    not changed: shared_pool_size = 343932928
    not changed: java_pool_size = 159383552
    changed: large_pool_size = 100M
    dropped: db_cache_size = 981467136
    set: db_block_buffers = 300000
    set: use_indirect_data_buffers = TRUE
    set: _db_block_lru_latches = 64
    changed: processes = 500
    set sga_max_size on pfile was not posible, I must change after I have created spfile

  23. hi everyone,

    my oracle 9i is install on window server 2003 standard version.Due to this oracle is not using more than 2 gb ram…please suggest

  24. Hi Amit,
    Please find below details of my Oracle Instance 10g:

    Windows Server 2003 Version V5.2 Service Pack 1
    CPU : 4 – type 586, 2 Physical Cores
    Process Affinity : 0x00000000
    Memory (Avail/Total): Ph:3671M/4094M, Ph+PgF:5646M/5971M, VA:1941M/2047M
    Fri Feb 05 09:32:08 2010
    Starting ORACLE instance (normal)
    Fri Feb 05 09:32:09 2010
    Specified value of sga_max_size is too small, bumping to 612368384
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    Picked latch-free SCN scheme 2
    Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
    Autotune of undo retention is turned on.
    IMODE=BR
    ILAT =18
    LICENSE_MAX_USERS = 500
    SYS auditing is disabled
    ksdpec: called for event 13740 prior to event group initialization
    Starting up ORACLE RDBMS Version: 10.2.0.1.0.
    System parameters with non-default values:
    processes = 150
    resource_limit = TRUE
    sga_max_size = 612368384
    __shared_pool_size = 121634816
    __large_pool_size = 50331648
    __java_pool_size = 8388608
    __streams_pool_size = 0
    spfile = F:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
    sga_target = 612368384
    control_files = F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
    db_block_size = 8192
    __db_cache_size = 423624704
    compatible = 10.2.0.1.0
    db_file_multiblock_read_count= 16
    db_recovery_file_dest = F:\oracle\product\10.2.0/flash_recovery_area
    db_recovery_file_dest_size= 2147483648
    undo_management = AUTO
    undo_tablespace = UNDOTBS1
    remote_login_passwordfile= EXCLUSIVE
    license_max_users = 500
    db_domain =
    dispatchers = (protocol=TCP)
    shared_servers = 10
    job_queue_processes = 10
    audit_file_dest = F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
    background_dump_dest = F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
    user_dump_dest = F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
    core_dump_dest = F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
    db_name = orcl
    open_cursors = 300
    pga_aggregate_target = 203423744
    PMON started with pid=2, OS id=1100
    MMAN started with pid=4, OS id=1036
    PSP0 started with pid=3, OS id=296
    DBW0 started with pid=5, OS id=1644
    LGWR started with pid=6, OS id=1648
    CKPT started with pid=7, OS id=1608
    SMON started with pid=8, OS id=1588
    RECO started with pid=9, OS id=1592
    CJQ0 started with pid=10, OS id=1892
    MMON started with pid=11, OS id=1896
    MMNL started with pid=12, OS id=1956
    Fri Feb 05 09:32:12 2010
    starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
    starting up 10 shared server(s) …
    Fri Feb 05 09:32:14 2010
    alter database mount exclusive
    Fri Feb 05 09:32:18 2010
    Setting recovery target incarnation to 2
    Fri Feb 05 09:32:19 2010
    Successful mount of redo thread 1, with mount id 1237934366
    Fri Feb 05 09:32:19 2010
    Database mounted in Exclusive Mode
    Completed: alter database mount exclusive
    Fri Feb 05 09:32:19 2010
    alter database open
    Fri Feb 05 09:32:19 2010
    Beginning crash recovery of 1 threads
    parallel recovery started with 3 processes
    Fri Feb 05 09:32:20 2010
    Started redo scan
    Fri Feb 05 09:32:20 2010
    Completed redo scan
    403 redo blocks read, 113 data blocks need recovery
    Fri Feb 05 09:32:20 2010
    Started redo application at
    Thread 1: logseq 639, block 3015
    Fri Feb 05 09:32:21 2010
    Recovery of Online Redo Log: Thread 1 Group 2 Seq 639 Reading mem 0
    Mem# 0 errs 0: F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
    Fri Feb 05 09:32:21 2010
    Completed redo application
    Fri Feb 05 09:32:21 2010
    Completed crash recovery at
    Thread 1: logseq 639, block 3418, scn 44383502
    113 data blocks read, 113 data blocks written, 403 redo blocks read
    Fri Feb 05 09:32:22 2010
    Thread 1 advanced to log sequence 640
    Thread 1 opened at log sequence 640
    Current log# 3 seq# 640 mem# 0: F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
    Successful open of redo thread 1
    Fri Feb 05 09:32:22 2010
    MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
    Fri Feb 05 09:32:22 2010
    SMON: enabling cache recovery
    Fri Feb 05 09:32:23 2010
    Successfully onlined Undo Tablespace 1.
    Fri Feb 05 09:32:23 2010
    SMON: enabling tx recovery
    Fri Feb 05 09:32:24 2010
    Database Characterset is WE8MSWIN1252
    replication_dependency_tracking turned off (no async multimaster replication found)
    Starting background process QMNC
    QMNC started with pid=28, OS id=3052
    Fri Feb 05 09:32:30 2010
    Completed: alter database open
    Fri Feb 05 09:32:30 2010
    db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
    user-specified limit on the amount of space that will be used by this
    database for recovery-related files, and does not reflect the amount of
    space available in the underlying filesystem or ASM diskgroup.

    I am facing a lot of problems with regards to performance. The goes dead slow. Kindly suggest the changes to be done.

    Thanks & Regards
    Vinod

    • Hi Vinod,

      I think instead of looking at alert log, you should try generating AWR/Statspack report for 30-60 mins and try to see what is consuming time in database. Also if any specific session is slow, you can trace it with 10046 event.
      You can also log a SR for same and Oracle specialists can assist you in diagnosing and resolving the issue.

      Regards
      Amit

        • Vinod,

          As a thumb rule, set SGA_TARGET to 256mb * Number of CPU’s i.e 1024 M in your environment.
          Can also set shared_pool_size to 256M so that you do not see reloads/invalidations. May be before touching these values, you can look at hard parses or reloads/invalidations in V$librarycache or your statspack/awr reports.

          Regards
          Amit

  25. Hi Amit,

    I found you post realy helpful.

    I need your advise to set these parameters for my database also: Below is the details:

    Database version = 10.2.0.2
    Oracle Applications Version= 12.0.6

    OS= Windows Server 2003 Enterprise Edition Service Pack 2

    Boot.ini
    ———–
    [boot loader]
    timeout=30
    default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
    [operating systems]
    multi(0)disk(0)rdisk(0)partition(2)\WINDOWS=”Windows Server 2003, Enterprise” /noexecute=optout /fastdetect /pae /3gb /userva=3030
    ————————————————-
    Total Physical Memory = 16 GB
    ————————————————-
    Total System Global Area 2147483648 bytes
    Fixed Size 1293536 bytes
    Variable Size 872416032 bytes
    Database Buffers 1258291200 bytes
    Redo Buffers 15482880 bytes
    —————————————————————————
    db_block_buffers integer 0
    db_block_size integer 8192
    db_cache_size big integer 0
    java_pool_size big integer 0
    large_pool_size big integer 0
    log_buffer integer 15244288
    sga_max_size big integer 2G
    sga_target big integer 2G
    shared_pool_size big integer 800M
    streams_pool_size big integer 0
    —————————————————————————
    use_indirect_data_buffers boolean FALSE
    —————————————————————————
    sessions integer 1600
    processes integer 800
    —————————————————————————

    Please advise what step should I follow to utilize memory more than 4 GB?

    As we are facing the following issue:
    An internal error has occurred in the program XLA_AE_LINES_PKG.SetNullLine. ORA-06500: PL/SQL: storage error
    ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,pmucalm coll).
    An internal error occurred. Please inform your system administrator or support representative that:

    Waiting for your reply

    Thanks
    Saleem

  26. Hi Saleem,

    In your case you are getting ora-4030 error i.e process memory (PGA) +SGA has gone beyond 2.7 gb . I think you will have to look at two options

    a) Try looking at why the pga requirement is high. May be you can tune the sql and try for optimal plan

    b) Reduce sga size and see if you are still getting errors.

    You can try using /PAE switch but i think you will have to change AWE_WINDOW_SIZE too. Refer to MOS note on finding appropriate size.

    Regards
    Amir

  27. Hi Amit, your article was really a great help!! Though we are still trying to get some good working settings.
    Our RMAN backup still fails whatever we try..
    Maybe you have got some ideas:

    Oracle 9.2i
    CPU’s = 8
    Total ram = 16GB

    AWE is set at 536 870 192

    DB_BLOCK_BUFFERS = 384216
    sga_max_size = 1500M
    shared_pool_size = 500M
    PGA_AGGREGATE_TARGET = 700M

    /3GB and /PAE are set in boot.ini.

    We have tried several configs with all different sizes for SGA, PGA, block_buffers etc. but still we get:

    MAN-03002: failure of backup command at 06/24/2010 14:26:10

    ORA-04030: out of process memory when trying to allocate 1049100 bytes (pga heap,KSFQ Buffers)

    with our RMAN..
    Do you have any suggestions to get RMAN working again? Must be something with PGA i would suspect!

    Thanks!!!
    regards,
    Jargo

  28. Hi Amit! This article is very usefull. We are using oracle 10.2.0.1 on win 2003 ent, with 6gb. We already tried to use awe with 1 Gb in the registry, and shared_pool_size=1024M
    db_block_buffers=368640
    db_block_size=8
    java_pool=200M
    large_pool=120M.
    pga_aggregate_target=300G

    The instance started up without error , but after a few hours of using it, with 30 users, it started to give errors like:
    ora-4031 unable to allocate shared…..(‘large pool’), and RMAN was reporting trouble also.

    No sga_ parameters are used, Thank you in advance.

    • Mathias,

      What was initial setting for large_pool. RMAN and parallel query slaves use large pool. So would recommend increasing large_pool to 200 or more (depending on your earlier setting).

      Cheers
      Amit

  29. Hi…
    iam having difficulties in tuning memory for oracle.
    Right now, i have the following setting :

    sga_max_size = sga_target = 584M
    pga_aggregat_target = 300M
    shared_pool_size = 0
    db_cached_size = 0

    I am using Oracle 10.2.0.1.0 with Win Server 2003 Enterprise x32 with 10Gb RAM. ASMM is enabled. I use /3GB and /PAE in boot.ini.

    I want to increase the memory more than 2GB but it seems limited. In “Database Configuration Assistant: Step 10” (when you create new database using wizards) , oracle can read only 2GB of RAM.
    I can’t set “use_indirect_data_buffers” to TRUE as it will stop oracle instance (it will throw error ora-12514) and therefore i cant use AWE_WINDOW_SIZE.
    If i get my AWE working, is it better using ASMM ?

    Can you please give me suggestion. I want to enable AWE… Thanks.

    -tongkimongki @ yahoo . com-

  30. Hi,
    I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0(32 bit)with windows server 2003 Enterprise Edition(64 bit) with 8GB physical memory. Below are the initialization parameter in spfile for memory:-
    sga_max_size – 3GB
    sga_target -2704MB
    pga_aggregate_target -1572MB

    Still i am facing ORA-04030- out of process memory error during daily opeations.
    Please help me or suggest me how to resolve this issue.

    Regards
    Bhupendra

  31. Hi Amit,

    First congrats for this post. I’m not very familliar with those 3G/PAE/AWE. It will be nice if you can help with the following problem: OS: Windows Server 2003 R2 EE, Oracle 11.2.0.1, 8GB RAM, 4CPU.
    If I set in the ora.ini the following parameters database will not start:
    AWE_WINDOW_MEMORY=346030080 (this is set in registry under oracle home)
    DB_BLOCK_BUFFERS=558080
    PGA_AGGREGATE_TARGET=176883763
    SHARED_POOL_SIZE=442652059
    SGA_TARGET or MEMORY_TARGET not set
    DB_CACHE_SIZE not set
    PAE and 3GB set in boot.ini

    Could you please help correcting those parameter ?

    P.S. If I set sga_max_size=2500M and pga_aggregate_target=760M without using all those parameters database will start but on heavy load will crash.

    Best Regards

      • Hi,

        The errors are related to OS Memory:
        O/S: out of memory

        Using those calculation I should be fine with all those parameters if AWE is working… but I suspect that AWE is not working.

        I forgot to mention that I’m using a virtual… but in any case I didn’t found any doc saying something about virtual machines.

        Regards

        • Crysu,

          It could be related to virtual machine but I am not sure on that. Have you set USE_INDIRECT_DATA_BUFFERS=TRUE in spfile/pfile?

          You can try reducing some parameters and see if it starts. Also consider increasing AWE_WINDOW_SIZE

          -Amit

  32. Hello,

    Is I have USE_INDIRECT_DATA_BUFFERS set to true in my pfile. Until now tried also increasing and decresing AWE without success.

    Thanks for your fast replay.

Leave a Reply