Hello Friend's,
In this post i am giving some queries that will help you find memory and cpu intensive queries.
Memory usage by session
SELECT username, value/(1024*1024) "Current session memory MB", sess.sid,sess.status
FROM v$session sess, v$sesstat stat, v$statname name
WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name like '%memory%'
--and username = 'ELISA_PROD'
and sess.status='INACTIVE'
order by 2,4 asc;
==================================================================
Rollback check :
select t.start_time, t.used_ublk, s.username, s.sid, s.serial#, r.segment_id, r.segment_name
from v$transaction t, v$session s, dba_rollback_segs r
where t.ses_addr = s.saddr
and t.xidusn = r.segment_id
and t.start_date < sysdate-1/48;
=================================================================
Cpu intensive query usage :
Set lines 300
Col sql_text for a60
Col program for a40
SELECT DISTINCT(se.SID), ss.serial#, ss.username, value/100/100 CPU_USAGE_PERCENTAGE, VALUE/100 cpu_usage_seconds, ss.OSUSER, ss.PROGRAM,
DBMS_LOB.SUBSTR(sql.SQL_fullTEXT,10000) sql_text FROM v$session ss, v$sesstat se, v$statname sn, v$sql SQL WHERE se.STATISTIC# = sn.STATISTIC# AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID AND sql.sql_id = ss.PREV_SQL_ID AND ss.status ='ACTIVE'
--AND ss.username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
--and ss.program not in ('%ORACLE.EXE%')
AND ss.username IS NOT NULL AND VALUE NOT IN '0' order by CPU_USAGE_PERCENTAGE desc ;
===============================================================
PID from SID :
SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID = &SpID;
===============================================================
SID from PID :
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
Hope This Help's
Regards
Sultan Khan
ConversionConversion EmoticonEmoticon