col program form a30 heading "Program"col CPUMins form 99990 heading "CPU in Mins"select rownum as rank, a.*from (SELECT v.sid,sess.Serial# ,program, v.value / (100 * 60) CPUMinsFROM v$statname s , v$sesstat v, v$session sessWHERE s.name = 'CPU used by this session'and sess.sid = v.sidand v.statistic#=s.statistic#and v.value>0ORDER BY v.value DESC) awhere rownum < 11;
col program form a30 heading "Program"col cpu_usage_sec form 99990 heading "CPU in Seconds"col MODULE for a18col OSUSER for a10col USERNAME for a15col OSPID for a06 heading "OS PID"col SID for 99999col SERIAL# for 999999col SQL_ID for a15select * from (select p.spid "ospid",(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,se.VALUE/100 cpu_usage_secfrom v$session ss,v$sesstat se,v$statname sn,v$process pwherese.STATISTIC# = sn.STATISTIC#and NAME like '%CPU used by this session%'and se.SID = ss.SIDand ss.username !='SYS'and ss.status='ACTIVE'and ss.username is not nulland ss.paddr=p.addr and value > 0order by se.VALUE desc);
SQL Text top consuming CPU in Oracle
col cpu_usage_sec form 99990 heading "CPU in Seconds"select * from (select(se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_secfrom v$session ss,v$sesstat se,v$statname sn, v$process p, v$sql qwherese.STATISTIC# = sn.STATISTIC#AND ss.sql_address = q.addressAND ss.sql_hash_value = q.hash_valueand NAME like '%CPU used by this session%'and se.SID = ss.SIDand ss.username !='SYS'and ss.status='ACTIVE'and ss.username is not nulland ss.paddr=p.addr and value > 0order by se.VALUE desc);
Top 10 CPU consuming Session in Oracle
col program form a30 heading "Program" col CPUMins form 99990 heading "CPU in Mins" select rownum as rank, a.* from ( SELECT v.sid,sess.Serial# ,program, v.value / (100 * 60) CPUMins FROM v$statname s , v$sesstat v, v$session sess WHERE s.name = 'CPU used by this session' and sess.sid = v.sid and v.statistic#=s.statistic# and v.value>0 ORDER BY v.value DESC) a where rownum < 11;
Top CPU Consuming Session in last 10 min
select * from ( select session_id, session_serial#, count(*) from v$active_session_history where session_state= 'ON CPU' and sample_time >= sysdate - interval '10' minute group by session_id, session_serial# order by count(*) desc );
SQL id consuming more CPU in Oracle
col program form a30 heading "Program" col cpu_usage_sec form 99990 heading "CPU in Seconds" col MODULE for a18 col OSUSER for a10 col USERNAME for a15 col OSPID for a06 heading "OS PID" col SID for 99999 col SERIAL# for 999999 col SQL_ID for a15 select * from ( select p.spid "ospid", (se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program", ss.module,ss.osuser,ss.MACHINE,ss.status, se.VALUE/100 cpu_usage_sec from v$session ss,v$sesstat se, v$statname sn,v$process p where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%' and se.SID = ss.SID and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.VALUE desc);
SQL Text top consuming CPU in Oracle
col cpu_usage_sec form 99990 heading "CPU in Seconds" select * from ( select (se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_sec from v$session ss,v$sesstat se, v$statname sn, v$process p, v$sql q where se.STATISTIC# = sn.STATISTIC# AND ss.sql_address = q.address AND ss.sql_hash_value = q.hash_value and NAME like '%CPU used by this session%' and se.SID = ss.SID and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.VALUE desc);