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

Hybrid View

  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,134
    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,334
    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