1] Use session statistics (to measure) The Oracle server maintains statistics that record the PGA and UGA memory consumption on a per session basis. If you suspect a memory leak then use SQL similar to that presented below to verify the amount of memory in use and re-issue it several times and monitor to confirm that memory is indeed growing. sql> select sid, substr(name,1,30), value , value/(1024*1024) "MB" from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name like '%a memory' order by sid, name; Note that if you do not see one of these statistics increase yet the process still seems to increase in memory consumption from the operating system standpoint then perhaps the problem lies with the your application code where memory is allocated but not released/ 2] You may also experience ora-4031 / ora-4030 errors running your sql statements. Please note that getting these errors does not automatically indicate that you have a memory leak, because you may not have configured your system appropriately. Remember that old l0ogic truth - a bus is a motor vehicle, but not all motor vehicles are buses. --additional Info select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,c.osuser,c.machine,c.terminal,c.program,c.module,state,logon_time from v$statname a, v$sesstat b,v$session c where a.statistic# = b.statistic# and name like '%a memory' and b.sid=c.sid and osuser!='oracle' order by status,MB desc,sid, name; If you want to the SQL also join v$sqlarea table- This will give you how much memory is utilized by package/procedure and a simple/complex SQL text select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", s.serial#,s.sid ssid, s.status,s.username,s.schemaname,s.osuser,s.machine,s.terminal,s.program,s.module,state,logon_time,substr(p.spid,1,8) spid, substr(sa.sql_text,1,2000) txt from v$statname a, v$sesstat b,v$session s, v$process p ,v$sqlarea sa where a.statistic# = b.statistic# and name like '%a memory' and b.sid=s.sid and osuser!='oracle' and p.addr = s.paddr and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+) order by status,MB desc,sid, name; |
Oracle DBA FAQ >