I am running following query to find sql's which are taking more than 1 minute 6000000 micro seconds.
select a.sql_id, to_char(a.elapsed_time/(1000000*60) , 99999999) Minutes, a.executions, b.sid, b.serial#
2 from v$sql a, v$session b
3 where a.sql_id=b.sql_id
4 and a.elapsed_time > 60000000 -- (60*6= 6-hours Threshold)
5* and b.username='TRACKWISE_OWNER'
SQL> /
I ran this query multiple times 1-2 seconds interval. I am getting different results. What surprises me all of a sudden a query with 943 minutes will show up, then next second it is
gone. I am pretty sure that 943 minute query did not complete, it is still running, it is just that it is not being captured in above query, why??
Appreciate your isight. Thanks.
SQL_ID MINUTES EXECUTIONS SID SERIAL#
------------- --------- ---------- ---------- ----------
0zpjssm11266c 4 116035 1013 24775
3typnv2cgc0z7 6 801368 1051 29592
cnjtp6gcc4xf3 10 791718 1098 34411
SQL> /
SQL_ID MINUTES EXECUTIONS SID SERIAL#
------------- --------- ---------- ---------- ----------
7vcvu4y2nf255 2 391 486 10348
0zpjssm11266c 4 116036 1013 24775
3typnv2cgc0z7 6 801368 1051 29592
cnjtp6gcc4xf3 10 791718 1098 34411
SQL> /
SQL_ID MINUTES EXECUTIONS SID SERIAL#
------------- --------- ---------- ---------- ----------
3bp8x40sguhd2 943 321090905 494 49948
0zpjssm11266c 4 116036 1013 24775
3typnv2cgc0z7 6 801368 1051 29592
cnjtp6gcc4xf3 10 791718 1098 34411
SQL> /
SQL_ID MINUTES EXECUTIONS SID SERIAL#
------------- --------- ---------- ---------- ----------
0zpjssm11266c 4 116036 1013 24775
3typnv2cgc0z7 6 801368 1051 29592
cnjtp6gcc4xf3 10 791718 1098 34411