ORA-04030

今天在执行一条SQL时，报错如下：

ORA-04030 在尝试分配 1046552 字节 (QERHJ hash-joi,QERHJ list array) 时进程内存不足

在未关闭进程之前，其它用户都无法连接Oracle

 

今天终于找到解决办法：

由于以前设置SGA内存时，考虑到Oracle Windows32位平台上只能使用1.7G内存，就把Oracle　SGA设置为

SQL> show sga

 

Total System Global Area 1652555776 bytes

Fixed Size                  1251680 bytes

Variable Size             805308064 bytes

Database Buffers          838860800 bytes

Redo Buffers                7135232 bytes

 

虽然说还没有达到1.7G，但已经出问题了

 

解决办法，降低SGA内存设置

SQL> show sga

 

Total System Global Area 1233125376 bytes

Fixed Size                  1250476 bytes

Variable Size             595594068 bytes

Database Buffers          629145600 bytes

Redo Buffers                7135232 bytes

 

 

其中参考了metalink 371074.1，如下：

Subject:	ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
	:		Type:	PROBLEM
	Modified Date :	30-JAN-2008	Status:	MODERATED

In this Document
  
  
  
  
  

---

This document is being delivered to you via Oracle Support's  (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.3
Oracle Server - Personal Edition - Version: 8.1.7.4 to 10.2.0.3
Oracle Server - Standard Edition - Version: 8.1.7.4 to 10.2.0.3
Microsoft Windows 2000
Microsoft Windows XP
Microsoft Windows Server 2003


Microsoft Windows (32-bit)

Symptoms

No new connections are allowed to the database.

Database appears to be in a hang state.
Errors:

Mon May 22 14:00:46 2006
Errors in file d:\ecomdb\bdump\ecomdb_psp0_1192.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn

written to alert log.

.
Verified the issue by the RDA output provided, which displays errors:

Mon May 22 14:00:46 2006
Errors in file d:\ecomdb\bdump\ecomdb_psp0_1192.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn

in the alert log included in RDA output.

Changes

The '/3GB'  switch was added to the  'boot.ini'  file at the OS level but the errors are eventually encountered when the database is under heavy load.

Cause

OS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.

In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On
Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of
addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini'  file, this
changes the ratio of kernel memory and user memory. When the  '/3GB'  switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail.

Solution

To implement the solution, please execute the following steps:

Tune the application running on the 32-bit environment so that it will not consume greater than
2.7GB of virtual memory when the  '/3GB'  switch is set in the  'boot.ini'  file.
In the case of Oracle encountering the OS resource issue, the options are:

1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user
process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only
uses a fraction of the amount of memory allocated to each user process when initial connection to
the database is established.


See Metalink Note 225349.1 for an in depth discussion regarding memory usage in a 32-bit Windows environment.