8 Replies Latest reply on Aug 9, 2010 9:53 AM by 785014

    Regarding V$SESSION_LONGOPS

    785014
      Hi folks,

      When does the query will go to v$session_longops?

      Thanks,
      Prathap.
        • 1. Re: Regarding V$SESSION_LONGOPS
          user00726
          It will give you about a brief idea about long running query
          For Example:
          
          
          SELECT sid, serial#, context, sofar, totalwork,
                 round(sofar/totalwork*100,2) "% Complete"
          FROM v$session_longops
          WHERE opname LIKE 'RMAN%'
          AND opname NOT LIKE '%aggregate%'
          AND totalwork != 0
          AND sofar <> totalwork
          /
          Edited by: user00726 on Aug 9, 2010 2:28 AM

          Edited by: user00726 on Aug 9, 2010 2:28 AM
          • 2. Re: Regarding V$SESSION_LONGOPS
            BelMan
            V$session_longops will give you an estimated time for particular long operation and such operations generally might be from zero till n for a query

            see this Demo
            http://www.gplivna.eu/papers/v$session_longops.htm
            • 3. Re: Regarding V$SESSION_LONGOPS
              618702
              Dear 782011,

              We need to define the "query". You could see the backup operations either, it should not be necessarily a "query". Backup operation has a query too and you may see in the relevant view. You can see index scans, backups, gathering statistics ETC.

              Actually the meaning of that fixed view is written in the view name, "SESSION LONG OPERATIONS".

              Hope That Helps.

              Ogan

              Edited by: Ogan Ozdogan on 09.Ağu.2010 12:36

              Read below documentations but they are for 11gR2. I did not know your Oracle version..;

              http://www.oracle.com/pls/db112/search?remark=quick_search&word=v%24session_longops
              • 4. Re: Regarding V$SESSION_LONGOPS
                785014
                Ok, i have a query which is running for more than 2 hours but its not showing in v$session_longops. Thats why i want to know what type of query will go to this V$session_longops.
                • 5. Re: Regarding V$SESSION_LONGOPS
                  user00726
                  You may use the below query:

                  select * from (

                  select opname, target, sofar, totalwork,

                  units, elapsed_seconds, message

                  from v$session_longops

                  where sid = <sid> and serial# = <serial#>

                  order by start_time desc)

                  where rownum <=1;
                  • 6. Re: Regarding V$SESSION_LONGOPS
                    618702
                    Dear 782011,

                    Can you please show the relevant query that took more than 2 hours?

                    Thanks.

                    Ogan
                    • 7. Re: Regarding V$SESSION_LONGOPS
                      Hemant K Chitale
                      A Long Running Query doesn't necessarily mean a Long Running Operation.

                      A Query Execution Plan consists of a number of steps -- some of which are Operations. An operation like a FullTableScan may be long running if it takes more than 6seconds at each pass. However, a Nested Loop which does a lookup on a table a million times would take very long to run but would be executing a million operations -- each of which is not long running.

                      If you see the documentation or see the section titled "Built in types of long operations" in the URL provided earlier (i.e. http://www.gplivna.eu/papers/v$session_longops.htm#_Toc155540263) you would see a list of defined long (running) operations.


                      It seems to be a very popular misconception that V$SESSION_LONGOPS identifies long running queries. It identifies long running operations.


                      Hemant K Chitale
                      http://hemantoracledba.blogspot.com
                      • 8. Re: Regarding V$SESSION_LONGOPS
                        785014
                        Hemant,

                        Thank u so much for the clarification.