ORA-04030: out of process memory when trying to allocate XXX bytes

rdbms 11.2.0.4

前几天，数据库突然出现了ORA-04030: out of process memory when trying to allocate 之类的错误 bytes。通过alert log发现，该错误主要出现在6月20日 22:00到22:30之间。

Thu Jun 20 22:00:00 2019
Starting background process VKRM
Thu Jun 20 22:00:00 2019
VKRM started with pid=32, OS id=6859 
Thu Jun 20 22:00:03 2019
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jun 20 22:13:20 2019
Thread 1 cannot allocate new log, sequence 639
Private strand flush not complete
  Current log# 2 seq# 638 mem# 0: /u01/oradata/ABC/redo02.log
Thread 1 advanced to log sequence 639 (LGWR switch)
  Current log# 3 seq# 639 mem# 0: /u01/oradata/ABC/redo03.log
Thu Jun 20 22:13:25 2019
Archived Log entry 259 added for thread 1 sequence 638 ID 0xea12efcf dest 1:
Thu Jun 20 22:25:33 2019
Errors in file /u01/app/oracle/diag/rdbms/ABC/ABC/trace/ABC_j003_6873.trc  (incident=32289):
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/ABC/ABC/incident/incdir_32289/ABC_j003_6873_i32289.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ABC/ABC/trace/ABC_j003_6873.trc  (incident=32290):
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/ABC/ABC/incident/incdir_32290/ABC_j003_6873_i32290.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Jun 20 22:25:40 2019
Dumping diagnostic data in directory=[cdmp_20190620222540], requested by (instance=1, osid=6873 (J003)), summary=[incident=32290].
Errors in file /u01/app/oracle/diag/rdbms/ABC/ABC/incident/incdir_32289/ABC_j003_6873_i32289.trc:
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Errors in file /u01/app/oracle/diag/rdbms/ABC/ABC/trace/ABC_j003_6873.trc  (incident=32291):
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/ABC/ABC/incident/incdir_32291/ABC_j003_6873_i32291.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ABC/ABC/incident/incdir_32289/ABC_j003_6873_i32289.trc:
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Dumping diagnostic data in directory=[cdmp_20190620222542], requested by (instance=1, osid=6873 (J003)), summary=[incident=32291].
Thu Jun 20 22:25:42 2019
Sweep [inc][32291]: completed
Sweep [inc][32290]: completed
Sweep [inc][32289]: completed
Sweep [inc2][32291]: completed
Sweep [inc2][32290]: completed
Thu Jun 20 22:26:42 2019
Sweep [inc2][32289]: completed
Thu Jun 20 22:27:39 2019
Thread 1 advanced to log sequence 640 (LGWR switch)
  Current log# 1 seq# 640 mem# 0: /u01/oradata/ABC/redo01.log
Thu Jun 20 22:27:40 2019
Archived Log entry 260 added for thread 1 sequence 639 ID 0xea12efcf dest 1:
Thu Jun 20 22:30:06 2019
Errors in file /u01/app/oracle/diag/rdbms/ABC/ABC/trace/ABC_j003_6873.trc  (incident=32292):
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/ABC/ABC/incident/incdir_32292/ABC_j003_6873_i32292.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Jun 20 22:30:08 2019
Dumping diagnostic data in directory=[cdmp_20190620223008], requested by (instance=1, osid=6873 (J003)), summary=[incident=32292].
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jun 20 22:30:09 2019
Sweep [inc][32292]: completed
Sweep [inc2][32292]: completed

查看alert中对应的trc文件，内容如下，其他的是一些dump出来的内容。基本上看到这里，已经猜测到是什么引起的了

*** 2019-06-20 22:00:52.182
*** SESSION ID:(399.2745) 2019-06-20 22:00:52.182
*** CLIENT ID:() 2019-06-20 22:00:52.182
*** SERVICE NAME:(SYS$USERS) 2019-06-20 22:00:52.182
*** MODULE NAME:(DBMS_SCHEDULER) 2019-06-20 22:00:52.182
*** ACTION NAME:(ORA$AT_SQ_SQL_SW_9) 2019-06-20 22:00:52.182
 
  status=(pst=ERROR)

同时查看这个时间段的AWR ，发现有个SQL语句执行了1772秒，相当于执行了差不多30分钟。该SQL语句的详细信息。里面有“SYS_AUTO_SQL_TUNING_TASK”.

 

此时，查看PGA的建议。发现PGA的确不是很够用。按照建议，需要19G以上的PGA。因为这套系统是从别的地方迁移过来的，旧系统的配置，PGA也就2444M，相差这么大，肯定有其他原因。

解决方法：

根据以上的alert log及trc里面的内容，禁用掉了SQL调优计划。禁用后，再次查看alert log ，里面没有任何的告警。查看awr，里面PGA的建议。PGA使用很低。

 

END