Long Running Query not in SESSION_LONGOPS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Long Running Query not in SESSION_LONGOPS

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Long Running Query not in SESSION_LONGOPS

    Hello, I have this query running on the database for the past 20 minutes but I do not see it in V$SESSION_LONGOPS. Where else can I go to find long running queries ?

    Thanks.

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Have you set TIMED_STATISTICS???

    you can add info about longrunning queries to session_longops by using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.
    "What is past is PROLOGUE"

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    OEM will show it too, under session you will see a little alarm clock thingymabob icon, but it is not very reliable.
    I have noticed in OEM that some sessions will show "inactive" when in fact they are active, maybe they like to take naps?
    I remember when this place was cool.

  4. #4
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    rshivagami, V$SESSION_LONGOPS do NOT works regarding "execution time" for long SQLs, it works timing long OPERATIONS for the SQLs : this way, IF a given SQL is doing a lot of SMALL operations , you WILL NOT "see" this in v$session_longops, the views shows ONLY if the SQL is doing JUST ONE large operation, such a large full table scan, thatīs it. And more, tipically int the same SQLs many times the bd spend time doing some "large" steps (like FTSs) AND doing small operations (like indexed-key reads and the like), V$SESSION_LONGOPS show only the large ones.

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Yup, I believe that it's any operation taking longer than 6 secs qualifies it for a spot in LONGOPS. Thats any individual select or sort etc. within the overall transaction.

    Got the 6 sec thing from July's 'Oracle Professional' journal (Pinnacle Publishing) ... http://www.pinnaclepublishing.com/me...e/op200607.zip
    Last edited by JMac; 08-03-2006 at 04:09 AM.

  6. #6
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks for all the info on V$SESSION_LONGOPS.
    So is there no other database view that I can query to find out ?

  7. #7
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by rshivagami
    So is there no other database view that I can query to find out ?
    Involving these V$SQL, V$SQLAREA, V$PROCESS, V$SESSION - write a query........
    "What is past is PROLOGUE"

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    and how are they going to show how long a query ran for

  9. #9
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    May be i should be specific, user has the query which is running for a long time. Why?

    Need to know how the resources are used for the query? With the above views can get the details of the resource usage - i think this is what the user needs.

    Above threads explain why the query is not seen in session_longops.
    "What is past is PROLOGUE"

  10. #10
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    It will not show the time spent, of course, BUT if one checks and see query X being executed, 2 minutes later see again and again X is there, and another 2 minutes X stiil is there, sure X is a query "slow" , something is wrong with X, this is what one can do to identify "long-running" queries ....

    Regards,

    Chiappa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width