Oracle top session info in RAC with tracefile name

See real time top session info in either RAC or standalone database, including tracefile name qualified by instance name, with:

-- ALTER SESSION SET nls_date_format = 'Dy DD-Mon-YYYY HH24:MI:SS' ;
SELECT DISTINCT s.inst_id , s.sid , s.serial# , s.username , s.osuser , s.program || ' ' || s.module AS program
     , s.status , ROUND ( s.last_call_et / 60 ) AS minutes , s.logon_time
     , s.state , s.event , s.seconds_in_wait , s.sql_id , q.sql_text
     , p.spid , s.service_name
     , CASE s.sql_trace WHEN 'ENABLED' THEN s.inst_id || ':' || p.tracefile END AS tracefile
  FROM gv$session s
  JOIN gv$sql q
    ON q.inst_id = s.inst_id
   AND q.sql_id = s.sql_id
  JOIN gv$process p
    ON p.inst_id = s.inst_id
   AND p.addr = s.paddr
 WHERE ( s.status = 'ACTIVE' OR last_call_et < 60 )  -- active in last minute
   AND s.username != 'SYS'
 ORDER BY s.inst_id , s.sid ;
Posted in Performance tuning, Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *

*