Shulou(Shulou.com)06/01 Report--

ORACLE 11g ORACLE ORA-04030 out of process memory when trying to allocate error, query ORACLE official MOS determination is: BUG11852492, the reason is that the user session single process occupied PGA more than 4GB abnormal termination, according to MOS article 1325100.1 recommendations: according to the actual situation of the server and SQL operation can be released PGA single process use memory space limit to 16GB.

The correlation analysis process is as follows:

ORA4030 alarm, alarm information is as follows: ********************************************************** ERROR IN ALERT LOG FILE - LAST 3 DAYS ================================ 15/12/2016 22:44:13 Thu Dec 15 22:44:13 2016 Thu Dec 15 22:44:13 2016 ORA-04030: out of process memory when trying to allocate 64 bytes (kxs-heap-c,allocator state) ORA-04030: out of process memory when trying to allocate 624 bytes (kxs-heap-c,MSQ2) ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer) Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_498/orcl_pmon_60993.trc: See Note 411.1 at My Oracle Support for error and packaging details. Use ADRCI or Support Workbench to package the incident.

For PGA single process 4GB memory space limit, verify that the current operating system-related limit parameter is set to 4GB:

[root@host ~]# more /proc/sys/vm/max_map_count65530[root@host ~]# more /proc/sys/vm/max_map_count65530[root@host ~]#

For PGA single process 4GB memory space limit, use the following sql statement to verify that oracle database related implicit parameters are set to 4GB:

_realfree_heap_pagesize_hint=65536 select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '_realfree_heap_pagesize_hint';

If the actual situation can be, oracle officially allows PGA single process to be set to 16GB, and the related operations are as follows:

SOLUTION Change the upper limit at either the OS or at the database level: Change the page count at the OS level: more /proc/sys/vm/max_map_count sysctl -w vm.max_map_count=262144 (for example) **Please note the above changes will revert upon host reboot unless the changes are permanently made in the /etc/sysctl.conf file ** Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database. For versions 11.2.0.4 and lower: _use_realfree_heap=TRUE _realfree_heap_pagesize_hint = 262144 For 12.1 and higher: _use_realfree_heap=TRUE _realfree_heap_pagesize = 262144

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.