2 Replies Latest reply on Feb 26, 2018 8:27 AM by John_K

    Select statement to get all running select statement with all detailed

    936921

      Hi,

       

      Am working on Oracle Application 12.1.3

      DB 11.2.0.3

      OS linux RedHat 6

       

      I need to generate an SQL statement to get all the running select statement on the DB with the started time

      And the when ti will be finished

      And what is the percentage that the SQL accomplish until now

      And the username with the Session ID

      And what the tables name.

       

      Thanks

        • 1. Re: Select statement to get all running select statement with all detailed
          936921

          I am using this

           

          select b.SID,b.SERIAL#,b.OPNAME,b.SOFAR,b.TOTALWORK,b.START_TIME,b.TIME_REMAINING,b.ELAPSED_SECONDS,b.USERNAME,g.CLIENT_IDENTIFIER,
          round(b.SOFAR*
          100 / b.TOTALWORK,0) || '%' as "%DONE",b.TIME_REMAINING,to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') START_TIME
          from V$SESSION_LONGOPS b,GV$SESSION g
          where b.SID=g.SID
          and TIME_REMAINING <>
          0 order by b.SOFAR/b.TOTALWORK;

           

          But it's not given all the select statement

           

          • 2. Re: Select statement to get all running select statement with all detailed
            John_K

            Longops only gives you sessions which have a step in the plan which is running long (hence the name). So for example, if you have a step in the plan which is a full table scan and that has been running for 20 seconds and has (approx) 10 second remaining, you will see that step in longops, however you won't see statements which are running slow because they're doing something many times (i.e. a 1ms index access being done a million times will be slow, but as each step only takes 1ms you don't see it in longops).

             

            Other than the long steps in longops, there isn't really anything which is going to tell you "this is the time left on my sql statement" - because all manner of things can happen. Plus it's set based processing - not linear.

             

            You can utilze v$active_session_history to see where a statement is up to in the plan, however that is just timed snapshots of what the session is doing - not what it has left to do etc.