Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    8

    Unanswered: Stop SQL query in Stored Procedure

    hi,

    Wish you all a very Happy and Prosperous New Year 2007

    we are using Stored Procedures in our application. In some cases, the Select Query takes more that 10 Min. But our client application will throw timed out execption after 2 min. But the SQL will be running at the database.

    Is there any way to kill or Stop the SQL query execution or Is there any way to time out the Stored Procedure.

    Thanks in advance.

    Thanks,
    Thrishankar

  2. #2
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20
    There doesn't appear to be a "clean" way to do it.

    http://forums.oracle.com/forums/thre...sageID=1360604

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Have you tried tuning the sql query so that it runs within the timeout?

    Alan

  4. #4
    Join Date
    Aug 2006
    Posts
    8
    hi,

    Our table contains 3 Millions records and we are using Top N queries for Pagination and also using order by in non indexed field. since our requirement was like that, and also we were not supposed to create any functional index on the database.

    Hence while timed out, we are trying to avoid filling of temp space and terminate the SQL query.

    Thrishankar

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your queriy is running for that long its likely to be hammering the database (especially as users like to rerun queries when they dont come back quickly). You should get onto the DBAs and point out that the cost of function based indexes (and or doing other tuning like partitioning etc) is likely to be far outweighed by the advantages

    Alan

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by thrishankar
    using order by in non indexed field
    Ouch!

    My DBA would kill me if I did that

Posting Permissions

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