History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QA-57
Type: Oracle - Operating System Oracle - Operating System
Status: Closed Closed
Resolution: Third-party Problem
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

ORA-04030 returned by "__libc_sbrk(0x0000000001010020) Err#12 ENOMEM"

Created: 02/Dec/13 02:21 PM   Updated: 28/Feb/17 09:01 AM
Fix Version/s: None

Product Version: 10.2.0.4
Operating System: IBM-AIX
Operating System Version: 6.1
Host Name: .
Database Name: .


 Description  « Hide
The customer encountered the following problem: 
ORA-04030: out of process memory when trying to allocate 2093096 bytes (QERHJ hash-joi,QERHJ list array)


 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
ubTools Support - 02/Dec/13 02:52 PM
ANALYIS 1:

PGASTAT:

SQL> select * from v$pgastat order by value;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
maximum PGA used for manual workareas                                     0
bytes

over allocation count                                                     0


total PGA used for manual workareas                                       0
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
cache hit percentage                                                  98.53
percent

process count                                                           126


max processes count                                                     135



NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
recompute count (total)                                              132370


total PGA used for auto workareas                                   4399104
bytes

total freeable PGA memory                                         106823680
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
maximum PGA used for auto workareas                               153909248
bytes

global memory bound                                               214743040
bytes

total PGA inuse                                                   747691008
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
total PGA allocated                                              1180690432
bytes

aggregate PGA auto target                                        1265577984
bytes

maximum PGA allocated                                            1299183616
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
aggregate PGA target parameter                                   2147483648
bytes

extra bytes read/written                                         1.2622E+10
bytes

PGA memory freed back to OS                                      6.0510E+10
bytes


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
UNIT
------------
bytes processed                                                  8.5171E+11
bytes


19 rows selected.

SQL>

pga_aggregate_target parmeter is not exceeded.

HEAPDUMP:

Set Up:

To setup tracing to trap the ORA-4030, on the server use the following in SQL*Plus:

SQL> ALTER SYSTEM SET EVENTS '4030 trace name heapdump level 536870917;name errorstack level 3';
Once the error reoccurs with the event set, you can turn off tracing using the following command in SQL*Plus:

ALTER SYSTEM SET EVENTS '4030 trace name context off; name context off';

Ref: Oracle note: Master Note for Diagnosing OS Memory Problems and ORA-4030 (Doc ID 1088267.1)

TRACE:

Heap:

HEAP DUMP heap name="session heap"  desc=11044a830
 extent sz=0xff80 alt=32767 het=32767 rec=0 flg=2 opc=2
 parent=1101981f0 owner=70000033f6789e8 nex=0 xsz=0x0
.....
Total heap size    =108241256

Internal Parameters:

  _pga_max_size                       = 419420 KB
.....
  _smm_max_size                       = 209710 KB
  _smm_px_max_size                 = 1048576 KB

No PGA limits are exceeded.


[ Permlink | « Hide ]
ubTools Support - 02/Dec/13 03:02 PM
ANALYSIS 2:

System Calls:

truss -fae -o <outputFile> -p <V$PROCESS.SPID> excerpt:

14483680:	43122723: __libc_sbrk(0x0000000001010020)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000000FE0020)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000001004020)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000000FE0020)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000001001020)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000000FE0020)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000001000420)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000000FDF420)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000001000120)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000000FDF420)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000001000060)	Err#12 ENOMEM
14483680:	43122723: __libc_sbrk(0x0000000000FDF420)	Err#12 ENOMEM
14483680:	43122723: statx("/oracle/admin/ATSD/udump", 0x0FFFFFFFFFFF41A8, 176, 0) = 0
14483680:	43122723: close(5)				= 0
14483680:	43122723: statx("/oracle/admin/ATSD/udump/atsd2_ora_14483680.trc", 0x0FFFFFFFFFFF44C0, 176, 01) Err#2  ENOENT
14483680:	43122723: statx("/oracle/admin/ATSD/udump/atsd2_ora_14483680.trc", 0x0FFFFFFFFFFF44C0, 176, 0) Err#2  ENOENT
14483680:	43122723: kopen("/oracle/admin/ATSD/udump/atsd2_ora_14483680.trc", O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE, S_IRUSR|S_IWUSR|S_IRGRP|S_IWGRP) = 5
14483680:	43122723: kwrite(5, 0x0000000104A1C468, 0)	= 0
14483680:	43122723: kwrite(5, " / o r a c l e / a d m i".., 47) = 47

When ORA-4030 error occured, trace file ("/oracle/admin/ATSD/udump/atsd2_ora_14483680.trc was created. So, the problem occured before its generation at _libc_sbrk with return code of _ENOMEM. The system could not return memory to Oracle process.

User resource limits:

oracle@atlasdb2:/home/oracle/dunal >ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        unlimited
memory(kbytes)       unlimited
coredump(blocks)     unlimited
nofiles(descriptors) unlimited
threads(per process) unlimited
processes(per user)  unlimited
oracle@atlasdb2:/home/oracle/dunal >

No limit was found for oracle user.


[ Permlink | « Hide ]
ubTools Support - 02/Dec/13 03:10 PM
The system admin will work on this problem. The solution will be added here.

[ Permlink | « Hide ]
ubTools Support - 28/Feb/17 09:01 AM
There was no response from the system admin. But, the problem was a resource limit problem that Oracle user could not allocate memory.


Powered by Atlassian JIRA the Professional Issue Tracker. (Standard Edition, Version: 3.12.3-#302) - Bug/feature request - Atlassian news - Contact Administrators