This discussion is archived
3 Replies Latest reply: Dec 7, 2010 6:59 AM by 821857 RSS

how to find long running query

690175 Newbie
Currently Being Moderated
hi
how to find the long runnign query

Regards
9841672839
  • 1. Re: how to find long running query
    user3266490 Newbie
    Currently Being Moderated
    hi,
    try this one
    [http://www.experts-exchange.com/Database/Oracle/Q_21753146.html]
  • 2. Re: how to find long running query
    703801 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points