Scripts to check Memory and Cpu intensive queries

 

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 sessv$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_timet.used_ublks.usernames.sids.serial#, r.segment_idr.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

Previous
Next Post »