Kill SQL Query

[gk4u]

Hi SQL Gurus,
I have a sql query which is taking too long to execute becasue of millions of data in database. I would like to identify and kill this sql query if it is taking more then 2 mins to execute.
Yes, we can identify the session and kill the session. But I dont want to kill session. I want to kill the particular SQL query in Oracle 9i.

Thanks in Advance.

Thanks
GK

 

[KenCunningham]

GK - which OS is this?

I want to be good, is that not enough?

 

[SantaMufasa]

GK,

As you have discovered, Oracle has an atrocius (i.e., virtually non-existent) interrupt mechanism for in-flight queries. (I'm looking forward to discovering what tricks Ken has up his sleeve on this topic.)

What I do when testing a query that may run "forever", is I first do a "count(*)" to get a feel for how many records are involved, and if I predict that the time involved may run too long, I throw into the WHERE statement (at the correct logical location) a "WHERE...rownum <= <some small value>" to cut down logically the "millions of data in database" that may exist.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[gk4u]

Hi Ken,
The database is on UNIX SUN 5.8 box and I am firing query through .NET program.