3 Replies Latest reply: Dec 7, 2010 8:59 AM by 821857 RSS

    how to find long running query

    690175
      hi
      how to find the long runnign query

      Regards
      9841672839
        • 1. Re: how to find long running query
          user3266490
          hi,
          try this one
          [http://www.experts-exchange.com/Database/Oracle/Q_21753146.html]
          • 2. Re: how to find long running query
            703801
            Query V$Session_longops

            SELECT s.SID,
            s.serial#,
            s.machine,
            ROUND(sl.elapsed_seconds/60) || ':' ||
            MOD(sl.elapsed_seconds,60) elapsed,
            ROUND(sl.time_remaining/60) || ':' ||
            MOD(sl.time_remaining,60) remaining,
            ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
            FROM v$session s,
            v$session_longops sl
            WHERE s.SID = sl.SID
            AND s.serial# = sl.serial#
            ORDER BY 4 DESC

            And then run the following query

            SELECT a.sql_text
            FROM v$sqltext a,
            v$session b
            WHERE a.address = b.sql_address
            AND a.hash_value = b.sql_hash_value
            AND b.sid = &1 --> From above sql
            ORDER BY a.piece;
            • 3. Re: how to find long running query
              821857
              col username format a10
              col sql_text format a100
              set linesize 400

              SELECT l.sid, l.start_time, l.username, l.elapsed_seconds
              a.sql_text, a.elapsed_time
              FROM v$session_longops l, v$sqlarea
              WHERE a.elapsed = l. elapsed_seconds
              AND l.elapsed_seconds > 1
              /