Error in Alert log file
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p005_8232.trc (incident=200692):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p006_8236.trc (incident=200701):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 17 10:20:40 2016
Error In trace file
oracle@server1$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_8222.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_8222.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2
System name: SunOS
Node name: server1
Release: 5.10
Version: Generic_150400-20
Machine: sun4u
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 200
Unix process pid: 8222, image: oracle@server1 (P000)
*** 2016-05-17 10:20:40.630
*** SESSION ID:(60.7937) 2016-05-17 10:20:40.630
*** CLIENT ID:() 2016-05-17 10:20:40.630
*** SERVICE NAME:(SYS$USERS) 2016-05-17 10:20:40.630
*** MODULE NAME:(SQL Developer) 2016-05-17 10:20:40.630
*** ACTION NAME:() 2016-05-17 10:20:40.630
DDE: Problem Key 'ORA 4030' was flood controlled (0x4) (incident: 200613)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
DDE: Problem Key 'ORA 4030' was flood controlled (0x6) (incident: 200614)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
*** 2016-05-17 10:20:42.310
DDE: Problem Key 'ORA 4030' was flood controlled (0x6) (incident: 200615)
ORA-04030: out of process memory when trying to allocate bytes (,)
Solution
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p005_8232.trc (incident=200692):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p006_8236.trc (incident=200701):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 17 10:20:40 2016
Error In trace file
oracle@server1$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_8222.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_8222.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2
System name: SunOS
Node name: server1
Release: 5.10
Version: Generic_150400-20
Machine: sun4u
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 200
Unix process pid: 8222, image: oracle@server1 (P000)
*** 2016-05-17 10:20:40.630
*** SESSION ID:(60.7937) 2016-05-17 10:20:40.630
*** CLIENT ID:() 2016-05-17 10:20:40.630
*** SERVICE NAME:(SYS$USERS) 2016-05-17 10:20:40.630
*** MODULE NAME:(SQL Developer) 2016-05-17 10:20:40.630
*** ACTION NAME:() 2016-05-17 10:20:40.630
DDE: Problem Key 'ORA 4030' was flood controlled (0x4) (incident: 200613)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
DDE: Problem Key 'ORA 4030' was flood controlled (0x6) (incident: 200614)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
*** 2016-05-17 10:20:42.310
DDE: Problem Key 'ORA 4030' was flood controlled (0x6) (incident: 200615)
ORA-04030: out of process memory when trying to allocate bytes (,)
Solution
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
It is a bug in oracle 11g version . To resolve this issue set the value of _PGA_MAX_SIZE to a lower value which internally forces the hash-join to use the less memory and avoid the ORA-4030
By default it is 200 MB.
SQL> alter system set _pga_max_size=120M;
No comments:
Post a Comment