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
Hello my friend, your site is very good!
Thanks 🙂
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.
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.
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.
/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?
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.
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
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..
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
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
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
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
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?
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 ? "
“< 2,7 GB" seems from Oracle Notes. for Linux, this value can go up a little bit.
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.
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?
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.
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 🙂
Sorry, Oracle 9.2.0.6
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
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).
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.)
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
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
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
Try using /3Gb switch if you have ram >3 Gb. Also you would like to re-read the article as it applies to your case 🙂
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
HI Amit,
Thanks for your response. I just wanted you to check the variables defined like sga etc. If this is OK then I will certainly log an SR for resolution.
Regards
Vinod
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
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
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
What MOS mean ?
MOS means My Oracle Support or metalink 🙂
Cheers
Amit
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
Jargo,
Can you please unset SGA_MAX_SIZE parameter and try again.
Regards
Amit
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
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-
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
As mentioned in article , you can’t use SGA_TARGET
**Please Note that you can not use ASMM i.e SGA_TARGET when using AWE.**
-Amit
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
Crysu,
Can you let me know the errors which you are getting while starting db.
-Amit
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
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.