-
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.
-
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"
-
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.
-
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.
-
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.
-
Thanks for all the info on V$SESSION_LONGOPS.
So is there no other database view that I can query to find out ?
-
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"
-
and how are they going to show how long a query ran for
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|