How to detect what is the Oracle memory hunger process

How to detect what is the Oracle memory hunger process? Previously I did share a script on how to check the top 10 memory consumption process in Unix.

It is recommended to check the growth memory usage of your Oracle Application Server from time to time.

Top Memory Usage Checking

$ UNIX95= ps -eo vsz,ruser,pid,args | sort -rn | head -10

  85796 applprod 21020 f60webmx webfile=5,1425,apps_oracle

  80228 applprod  5803 f60webmx webfile=5,2443,apps_oracle

  68388 applprod 27304 f60webmx webfile=5,1619,apps_oracle

  65828 applprod 15211 f60webmx webfile=5,1741,apps_oracle

  65444 applprod 11075 f60webmx webfile=5,264,apps_oracle

  64676 applprod 20696 f60webmx webfile=5,2163,apps_oracle

  62948 applprod   885 f60webmx webfile=5,2303,apps_oracle

  56996 applprod 24789 f60webmx webfile=5,2458,apps_oracle

  54884 applprod 18172 f60webmx webfile=5,2405,apps_oracle

  54756 applprod 18077 f60webmx webfile=5,61,apps_oracle

From the output above, it seems that the Oracle form process f60webmx utilize a high amount of server memory.

Quite abnormal because the f60webmx form process should be terminated after certain time of inactivity.

Use the script as below to check on the status of f60webmx session by using the Unix PID.

Oracle Forms Session Checking

SELECT s.STATUS "Status",

s.TYPE "Type",

s.username "DB_User",

s.osuser "Client_User",

s.server "Server",

s.machine "Machine",

s.module "Module",

s.logon_time "Connect Time",

s.process "Process",

p.spid,

p.pid,

s.SID,

s.audsid,

SYSDATE - (s.last_call_et / 86400) "Last_Call"

FROM v$session s,

v$process p

WHERE s.paddr = p.addr(+)

AND s.process = <replace WITH Unix PID>;