4

When it stops the execution of a long running script. The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad.

2nd EDIT:

Just found that this is a feature of Toad and not of Oracle

In SQL developer, you can stop execution of long running queries and similar to SQL Server Management Studio you get can modify your query immediately.

5

Trace your session. I expect you will find Toad is waiting for Oracle to roll back your transaction - which Oracle will continue to do in the background even if Toad is killed.

Under the covers, when you abort a query, an Oracle client application sends OCIBreak(). However in order to actually process this, the shadow process on the Oracle side has to come back and read that message - it is a request only, there is no way to send a true interrupt (i.e. a signal in Unix terms) across the network. If the shadow process is in a state where it is itself waiting for an operation to complete (such as a big I/O request to the OS) then it is not until that completes that it can advance to the next "tick" and find the break command waiting for it.

If you are really impatient, you could get the same result by finding the shadow process (v$process.spid where addr = v$session.paddr and sid=<your sid>) on the server and just issuing a kill command at the shell.

Incidentally there is some clean-up to do when a long-running SELECT is aborted: the previous versions of the rows are no longer needed.

  • I#M not inserting or updating anything in that schema. No use of global temporary tables. Why does a long running query create any redo/undo or what else? Are there hidden 'temporay tables' ? – bernd_k May 3 '11 at 6:03
  • Oracle has to ensure that the version of the row that existed when your query started is kept for the duration of the query. This is called MVCC. Also are you using the TEMP tablespace at all for sorting? That will need to be cleaned up too. – Gaius May 3 '11 at 7:52
  • @Gaius - I am curious why there would be any noticeable wait for cleanup, I think that would be done asynchronously by server processes after processing OCIBreak() - is that wrong? Not sure what version/platform bernd is on but the link you provided says: "OCIBreak() is not yet supported if the server is an NT system" for 9.2 so perhaps worth asking – Jack Douglas May 4 '11 at 18:15
  • @JackPDouglas the server side doesn't see the OCIBreak() until its next scheduler tick – Gaius May 5 '11 at 8:40
  • @Gaius - and are you saying that only happens after all the cleanup is done? Isn't that handed over to other server processes to do? (not the MVCC cleanup but things like cleaning up temp space)? Either way it looks like this is TOAD specific rather than something happening on the server side? – Jack Douglas May 5 '11 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.