SQL Query to display Current Statement of Active Session/Program

Following SQL query will fetch "Current Statement" for the active session passed as parameter:-

 Parameters:-

     p_inst_id -- Instance Id
     p_sid      -- Session Id
 
     SELECT s.sid,
       s.client_info,
       s.machine,
       s.program,
       s.type,
       s.logon_time,
       s.osuser,
       sq.sorts,
       sq.disk_reads,
       sq.buffer_gets,
       sq.rows_processed,
       sq.sqltype,
       sq.sql_text
  FROM gv$session s,
               gv$sql sq
 WHERE s.sql_hash_value = sq.hash_value
   AND s.inst_id = :p_inst_id           -- replace with ID from above
   AND s.sid = :p_sid                   -- replace with instID from above
   AND sq.inst_id = s.inst_id;

 

 -- Select statement which provides information on sessions and queries which cause maximum disk reads and waits

 

  SELECT SUBSTR (ss.username, 1, 8) username,
         ss.osuser "USER",
         ar.module || ' @ ' || ss.machine client,
         ss.process pid,
         TO_CHAR (ar.last_load_time, 'DD-Mon HH24:MM:SS') load_time,
         ar.disk_reads disk_reads,
         ar.buffer_gets buffer_gets,
         SUBSTR (ss.lockwait, 1, 10) lockwait,
         w.event event,
         ss.status,
         ar.sql_fulltext sql
    FROM v$session_wait w,
         v$sqlarea ar,
         v$session ss,
         v$timer t
   WHERE     ss.sql_address = ar.address
         AND ss.sql_hash_value = ar.hash_value
         AND ss.sid = w.sid(+)
         AND ss.status = 'ACTIVE'
         AND w.event != 'client message'
ORDER BY ss.lockwait ASC, ss.username, ar.disk_reads DESC;