PL/SQL

External Procedures May Consume a Lot of Memory

External procedures are very special procedures stored outside of the database, and it can be coded by C or Java language. Since the external libraries were executed on OS-level, their behaviors are not managed and bounded by database anymore, so it could become a security leak. The users who have been granted the privilege to execute on  external libraries could abuse the privilege and consume a lot of resource.

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  3556720K

You 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 = :b2


At 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.

Leave a Reply

Your email address will not be published. Required fields are marked *