This
error comes because of shortage of memory available to be provided to a new
session’s server process which wants to connect to the database.
Windows 32-bit
This error is more common on
32-bit Windows based systems. On Windows, Oracle session processes run as threads under main oracle.exe process and maximum 2G can be allocated to single OS process under windows 32-bit, and hence when oracle.exe reaches near 2G, it starts throwing ORA-04030.
In today's modern era, I don't think there are many 32-bit servers are left. If someone is till using these old servers, probably they will be replacing 32-bit serves very soon with the 64-bit ones.
When Does this Error Come?
Suppose we have allocated 1.5G RAM to SGA, now we have total 500 MB available for the user sessions (remember that maximum 2G for whole oracle.exe). If more and more sessions keep connecting with the database, we can observe (in task manager) that "oracle.exe" memory consumption also keeps increasing. Once this RAM allocation reaches near to 2G, this error would start appearing.
Above explanation is regarding 32-bit servers, but this error may also come in 64-bit environments and can come on Windows as well as Unix based systems. Reason of this error on 64-bit platforms is same: OS is not able to allocate memory to the pga of a new incoming session and hence session fails to connect with the database and throws ORA-04030. On Linux systems, you should also check limits for oracle user by using command "ulimit -a" and also kernel parameters related to memory that are set in /etc/sysctl.conf file. Follow oracle documentation for the appropriate settings.
Solution
1. Increase the amount of RAM (if it is not a 32 bit system with restriction of maximum 4G RAM and maximum 2G to a single process).
2. If Windows 32-bit, decrease the size of SGA so that more sessions can connect with the database and no session fails with this error
3. Switch to Shared Server configuration to preserve memory.
4. For 64-bit platforms, make sure that you calculate memory allocation properly. Suppose if you have 64G total memory, you should leave around 30% (20G) for the OS. Leftover 44G should be distributed between (SGA+PGA_AGGREGATE_TARGET) and process memory for each Oracle session that will connect with the database. Suppose if I expect total 300 sessions connecting with the database simultaneously, I usually reserve 10 MB per session, and it would mean total 3G to be reserved for all oracle sessions, and then rest of (44 - 3 = 41G) can be used for SGA and PGA_AGGREGATE_TARGET.
Windows 32-bit
This error is more common on
32-bit Windows based systems. On Windows, Oracle session processes run as threads under main oracle.exe process and maximum 2G can be allocated to single OS process under windows 32-bit, and hence when oracle.exe reaches near 2G, it starts throwing ORA-04030.
In today's modern era, I don't think there are many 32-bit servers are left. If someone is till using these old servers, probably they will be replacing 32-bit serves very soon with the 64-bit ones.
When Does this Error Come?
Suppose we have allocated 1.5G RAM to SGA, now we have total 500 MB available for the user sessions (remember that maximum 2G for whole oracle.exe). If more and more sessions keep connecting with the database, we can observe (in task manager) that "oracle.exe" memory consumption also keeps increasing. Once this RAM allocation reaches near to 2G, this error would start appearing.
Above explanation is regarding 32-bit servers, but this error may also come in 64-bit environments and can come on Windows as well as Unix based systems. Reason of this error on 64-bit platforms is same: OS is not able to allocate memory to the pga of a new incoming session and hence session fails to connect with the database and throws ORA-04030. On Linux systems, you should also check limits for oracle user by using command "ulimit -a" and also kernel parameters related to memory that are set in /etc/sysctl.conf file. Follow oracle documentation for the appropriate settings.
Solution
1. Increase the amount of RAM (if it is not a 32 bit system with restriction of maximum 4G RAM and maximum 2G to a single process).
2. If Windows 32-bit, decrease the size of SGA so that more sessions can connect with the database and no session fails with this error
3. Switch to Shared Server configuration to preserve memory.
4. For 64-bit platforms, make sure that you calculate memory allocation properly. Suppose if you have 64G total memory, you should leave around 30% (20G) for the OS. Leftover 44G should be distributed between (SGA+PGA_AGGREGATE_TARGET) and process memory for each Oracle session that will connect with the database. Suppose if I expect total 300 sessions connecting with the database simultaneously, I usually reserve 10 MB per session, and it would mean total 3G to be reserved for all oracle sessions, and then rest of (44 - 3 = 41G) can be used for SGA and PGA_AGGREGATE_TARGET.
No comments:
Post a Comment