Stopping SQL code execution

views:

answers:

+3 Q:

Stopping SQL code execution

We have a huge Oracle database and I frequently fetch data using SQL Navigator (v5.5). From time to time, I need to stop code execution by clicking on the Stop button because I realize that there are missing parts in my code. The problem is, after clicking on the Stop button, it takes a very long time to complete the stopping process (sometimes it takes hours!). The program says Stopping... at the bottom bar and I lose a lot of time till it finishes.

What is the rationale behind this? How can I speed up the stopping process? Just in case, I'm not an admin; I'm a limited user who uses some views to access the database.

+5 A:

Two things need to happen to stop a query:

The actual Oracle process has to be notified that you want to cancel the query
If the query has made any modification to the DB (DDL, DML), the work needs to be rolled back.

For the first point, the Oracle process that is executing the query should check from time to time if it should cancel the query or not. Even when it is doing a long task (big HASH JOIN for example), I think it checks every 3 seconds or so (I'm looking for the source of this info, I'll update the answer if I find it). Now is your software able to communicate correctly with Oracle? I'm not familiar with SLQ Navigator but I suppose the cancel mechanism should work like with any other tool so I'm guessing you're waiting for the second point:

Once the process has been notified to stop working, it has to undo everything it has already accomplished in this query (all statements are atomic in Oracle, they can't be stopped in the middle without rolling back). Most of the time in a DML statement the rollback will take longer than the work already accomplished (I see it like this: Oracle is optimized to work forward, not backward). If you are in this case (big DML), you will have to be patient during rollback, there is not much you can do to speed up the process.

If your query is a simple SELECT and your tool won't let you cancel, you could kill your session (needs admin rights from another session) -- this should be instantaneous.

Vincent Malgrat 2010-10-15 08:52:21

Thanks for your answer. Let me give further information for my situation: I don't have any modification rights on the database. I only use `SELECT` statements to fetch 1000s of rows (max. 250,000 rows). SQL Navigator is a licenced software which our company's been using for a long time. I haven't experienced any connection problems with it. So killing my session is my best bet, right?

Mehper C. Palavuzlar 2010-10-15 09:06:20

@Mehper C. Palavuzlar: Cancelling a SELECT shouldn't take much time (some SELECT do some data cleaning but I'm not sure this is actually rolled back -- it is just maintenance and should be quite rare anyway). Does the cancel button work with small queries?

Vincent Malgrat 2010-10-15 09:13:42

@Vincent Malgrat: Again it takes a long time to stop small queries (OK, it takes a shorter time than the big `SELECT` queries; but not less than half an hour).

Mehper C. Palavuzlar 2010-10-15 09:21:05

@Mehper C. Palavuzlar> This definitively sounds like a client issue, why don't you test it by trying to use different clients and see how they behave? (you can grab a number of free or evaluation clients). Also when you say smaller query and bigger query, what is it that you refer to? Number of rows returned? Size of the script?

Unreason 2010-10-15 12:16:37

@Unreason: Sorry but what do you mean by "clients"? By smaller and bigger queries, I mean both # of rows and the content of the script i.e. calculations, used functions, period (# of days, months, years), # of sales points etc. Think of a huge database which contains all kinds of data belonging to all magazines sold in Türkiye.

Mehper C. Palavuzlar 2010-10-15 12:34:35

@Mehper C. Palavuzlar, I mean front ends - some of which are listed here: http://stackoverflow.com/questions/1038991/whats-a-quality-development-environment-for-writing-oracle-sql

Unreason 2010-10-15 12:48:19

@Unreason: OK, I got it now. I have no chance to use another client because of IT Department's policies. Anyway, thanks for your response.

Mehper C. Palavuzlar 2010-10-15 12:57:48

+1 A:

When you cancel a query, the Oracle client should send OCIBreak() but this isn't implemented on a Windows server, that could be the cause.

Also, have your DBA check the value of SQLNET.EXPIRE_TIME.

Gaius 2010-10-15 19:51:06

ansaurus

tags:

views:

answers:

Stopping SQL code execution

related questions