In my recent case, the external procedure eventually consumed almost 5GB memory out of 20GB, and the thing could be worse if the user continued to use it improperly.
In the beginnings, I noticed the overall free memory of server was dropping rapidly. Later, I checked the outstanding process:
[oracle@orcl ~]$ ps -eo pid,pmem,pcpu,rss,vsz,stime,user,args | grep -i extproc | grep -v grep
43459 14.2 6.6 2840656 2850512 08:47:55 oracle extprocPLSExtProc (LOCAL=NO)This process seemed taking too much memory.
Let's see the process tree:
[oracle@orcl ~]$ ptree 43459
43459 extprocPLSExtProc (LOCAL=NO)OK, it's a standalone process.
Let's check the process map:
[oracle@orcl ~]$ pmap 43459
43459: extprocPLSExtProc (LOCAL=NO)
0000000100000000 24K read/exec /u01/app/product/9.2.0/db_1/bin/extproc
0000000100104000 8K read/write/exec /u01/app/product/9.2.0/db_1/bin/extproc
0000000100106000 3537232K read/write/exec [ heap ]
FFFFFFFF7AD00000 40K read/exec /usr/local/lib/sparcv9/libgcc_s.so.1
FFFFFFFF7AE08000 8K read/write/exec /usr/local/lib/sparcv9/libgcc_s.so.1
FFFFFFFF7AF00000 8K read/write/exec [ anon ]
FFFFFFFF7B000000 1264K read/exec /u01/app/product/9.2.0/db_1/extlibs/chemicalb.so
FFFFFFFF7B23A000 104K read/write/exec /u01/app/product/9.2.0/db_1/extlibs/chemicalb.so
FFFFFFFF7B254000 8K read/write/exec /u01/app/product/9.2.0/db_1/extlibs/chemicalb.so
FFFFFFFF7B402000 8K read/write [ anon ]
FFFFFFFF7B604000 8K read/write [ anon ]
FFFFFFFF7B806000 8K read/write [ anon ]
FFFFFFFF7BA08000 8K read/write [ anon ]
FFFFFFFF7BC0A000 8K read/write [ anon ]
FFFFFFFF7BE0C000 8K read/write [ anon ]
FFFFFFFF7C00A000 8K read/write [ anon ]
FFFFFFFF7C00E000 8K read/write [ anon ]
FFFFFFFF7C20C000 8K read/write [ anon ]
FFFFFFFF7C210000 8K read/write [ anon ]
FFFFFFFF7C40E000 8K read/write [ anon ]
FFFFFFFF7C508000 8K read/write [ anon ]
FFFFFFFF7C600000 8K read/write/exec/shared [ anon ]
FFFFFFFF7C700000 8K read/write/exec [ anon ]
FFFFFFFF7C800000 16K read/exec /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7C904000 8K read/write/exec /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7CA00000 8K read/exec /u01/app/product/9.2.0/db_1/lib/libwtc9.so
FFFFFFFF7CB00000 8K read/write/exec /u01/app/product/9.2.0/db_1/lib/libwtc9.so
FFFFFFFF7CC00000 8K read/write/exec [ anon ]
FFFFFFFF7CD00000 216K read/exec /usr/lib/sparcv9/libm.so.1
FFFFFFFF7CE34000 16K read/write/exec /usr/lib/sparcv9/libm.so.1
FFFFFFFF7CF00000 8K read/exec /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D002000 8K read/write/exec /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D100000 24K read/exec /usr/lib/sparcv9/librt.so.1
FFFFFFFF7D206000 8K read/write/exec /usr/lib/sparcv9/librt.so.1
FFFFFFFF7D300000 32K read/exec /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7D408000 8K read/write/exec /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7D500000 728K read/exec /usr/lib/sparcv9/libc.so.1
FFFFFFFF7D6B6000 56K read/write/exec /usr/lib/sparcv9/libc.so.1
FFFFFFFF7D6C4000 8K read/write/exec /usr/lib/sparcv9/libc.so.1
FFFFFFFF7D700000 8K read/write/exec [ anon ]
FFFFFFFF7D800000 32K read/exec /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7D908000 8K read/write/exec /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7DA00000 56K read/exec /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7DB0E000 16K read/write/exec /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7DC00000 11384K read/exec /u01/app/product/9.2.0/db_1/lib/libclntsh.so.9.0
FFFFFFFF7E81C000 720K read/write/exec /u01/app/product/9.2.0/db_1/lib/libclntsh.so.9.0
FFFFFFFF7E8D0000 72K read/write/exec /u01/app/product/9.2.0/db_1/lib/libclntsh.so.9.0
FFFFFFFF7E900000 8K read/write/exec /usr/lib/sparcv9/libdl.so.1
FFFFFFFF7EA00000 680K read/exec /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7EBAA000 64K read/write/exec /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7EBBA000 32K read/write/exec /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7EC00000 2952K read/exec /u01/app/product/9.2.0/db_1/lib/libagtsh.so.1.0
FFFFFFFF7EFE0000 232K read/write/exec /u01/app/product/9.2.0/db_1/lib/libagtsh.so.1.0
FFFFFFFF7F01A000 8K read/write/exec /u01/app/product/9.2.0/db_1/lib/libagtsh.so.1.0
FFFFFFFF7F100000 8K read/write/exec [ anon ]
FFFFFFFF7F200000 128K read/exec /usr/lib/sparcv9/libthread.so.1
FFFFFFFF7F320000 16K read/write/exec /usr/lib/sparcv9/libthread.so.1
FFFFFFFF7F324000 64K read/write/exec /usr/lib/sparcv9/libthread.so.1
FFFFFFFF7F400000 8K read/exec /usr/platform/sun4u-us3/lib/sparcv9/libc_psr.so.1
FFFFFFFF7F500000 8K read/write/exec [ anon ]
FFFFFFFF7F600000 184K read/exec /usr/lib/sparcv9/ld.so.1
FFFFFFFF7F72E000 16K read/write/exec /usr/lib/sparcv9/ld.so.1
FFFFFFFF7FFF4000 48K read/write [ stack ]
total 3556720KYou can see the "heap" was the largest item consumed, and the process was loading something into memory heap, I guessed it was data loaded into the heap due to a query, because any other objects couldn't be that large.
Next, we should know who or what statement triggered the external libraries. The key feature of such sessions are with "HS message to agent" wait event. So, we composed a statement to identify the session:
set linesize 180;
set pagesize 100;
column username format a8;
column os_pid format a8;
column event format a20;
column sql_text format a60;
SELECT DISTINCT s.inst_id,
s.sid,
s.serial#,
p.spid OS_PID,
s.username,
e.event,
q.sql_text
FROM gv$session s,
gv$session_event e,
gv$process p,
gv$sql q
WHERE s.inst_id =p.inst_id
AND s.sid =e.sid
AND s.paddr =p.addr
AND s.sql_hash_value=q.hash_value
AND s.status ='ACTIVE'
AND e.event LIKE 'HS%';
exit;Yes, we found it, the use HR was querying a big table.
INST_ID SID SERIAL# OS_PID USERNAME EVENT SQL_TEXT
---------- ---------- ---------- -------- -------- -------------------- ------------------------------------------------------------
1 45 3459 8394 HR HS message to agent SELECT * FROM hr.data_table WHERE sn = :b1 and bn = :b2At the last check, we found the query executed for about half a hour and take almost 5GB memory, then the process was terminated for some reasons and freed all memory of itself.
[oracle@orcl ~]$ ps -eo pid,pmem,pcpu,rss,vsz,stime,user,args | grep -i extproc | grep -v grep
43459 25.5 6.7 5108352 5118208 16:57:09 oracle extprocPLSExtProc (LOCAL=NO)We reported this issue to the application development unit, they promised to decrease the loaded data by limiting the query range on the user interface in order to decrease the consumption of memory.