set linesize 120 pagesize 66 col c1 for a9 col c1 heading "OS User" col c2 for a9 col b1 for a9 col b1 heading "Unix PID" col b2 for 9999 justify left col b2 heading "SID" col b3 for 99999 justify left col b3 heading "SERIAL#" col sql_text for a35 col event for a30 break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3 select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3, b.event, a.sql_text from v$sqltext a, v$session b, v$process c where a.address = b.sql_address -- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE -- ACTVE TRANSACTION ON THAT MOMENT */ and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid, a.hash_value, a.piece / REM TOP SESSION with sessions as (select /*+ materialize */ sess.inst_id, sess.sid, sess.serial#, sess.username, sess.module, sess.program, stat.value cpu_used_by_this_session, i.physical_reads, i.block_gets, sess.command, sess.status, sess.lockwait, decode(sess.sql_hash_value, 0, sess.prev_hash_value, sess.sql_hash_value) sql_hash_value, decode(sess.sql_address, '00', sess.prev_sql_addr, sess.sql_address) sql_address from gv$sesstat stat, gv$session sess, gv$sess_io i where stat.statistic# = (select statistic# from v$statname where name = 'CPU used by this session') and stat.sid = sess.sid and stat.inst_id = sess.inst_id and (stat.value > 100 or i.physical_reads > 100 or i.block_gets > 100) and sess.username is not null and i.sid = sess.sid and i.inst_id = sess.inst_id), sqlarea as (select inst_id, sql_fulltext sql_text, hash_value, address from gv$sqlarea) select * from sessions, sqlarea where sessions.inst_id = sqlarea.inst_id and sessions.sql_hash_value = sqlarea.hash_value and sessions.sql_address = sqlarea.address order by cpu_used_by_this_session desc |