How to check if oracle instance is leaking memory
Oracle memory leak info
col name format a20
select sid, substr(name,1,30) name , 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;
set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10
select b.sid, substr(name,1,30) name, 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;
set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10
select b.sid, substr(name,1,30), value , value/(1024*1024) "MB",s.sid ssid, 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
sa.sql_id
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;
set long 4000
select sql_text from v$sqlarea where sql_id='&1'
Search This Blog
Total Pageviews
1,084,666
Wednesday, 20 April 2011
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)
No comments:
Post a Comment