Toad World® Forums

Statement still executes, even after a STOP !

Hi

When I run a SELECT with a related database (@...), I'm not able to stop this one. It still runs to the end.

Regards

Martin

Hi

I does the same with a local table.

Regards

Martin

Hi Martin, how long is your SELECT taking to run normally? Are you sure cancelling it still takes the same amount of time?

Cancelling an execution would normally take 1 or 2 seconds, and in certain cases it may take a little bit longer but it should not be too long like 1 minute or so.

I have the following “bad” query we used to use. It takes around 1 min to run to the end. If I cancel it at some point, it ends in 2 seconds.

SELECT object_name, DECODE (status, ‘VALID’, 0, 1) status
FROM sys.dba_objects ociinterval
WHERE owner = ‘SYS’
AND object_type = ‘PACKAGE’
AND object_name NOT LIKE ‘BIN$%’
AND object_name LIKE ‘%’
UNION ALL
SELECT DISTINCT s.name object_name, 0 status
FROM sys.dba_source s
WHERE s.TYPE = ‘PACKAGE’
AND s.owner = ‘SYS’
AND NOT EXISTS
(SELECT ‘x’
FROM sys.dba_objects o_sub
WHERE o_sub.owner = s.owner
AND o_sub.object_name like s.name
AND o_sub.object_type = s.TYPE)
AND s.name LIKE ‘%’
ORDER BY 1

Thanks,

Vincent

Hi Vincent

The statements I cancel are most of the time “consuming ones”, they takes minutes.

The one that takes most time to be canceled (minutes) are most of the time the one with related databases or with group by

I tried your select and canceled it : cancel was “immediately” in comparison with the ones I often cancel.

Regards

Martin

Hi Vincent

One of my colleague upgraded to the 7.3 commercial version. He does have exactly the same behaviour on consuming statements.

If he tries to stop a statement he gets an error saying “Script execution has already terminated”, but he can’t work in this session. The statement runs to the end, and then he’s able to work again.

Regards

Martin

Hi Martin,

Does it only happen to specific statements (like one you mentioned with @db…)?

Thanks,

Vincent

From: martin_goblet [mailto:bounce-martin_goblet@toadworld.com]

Sent: Thursday, February 16, 2017 8:50 PM

To: sqlnav@toadworld.com

Subject: RE: [SQL Navigator - Discussion Forum] Statement still executes, even after a STOP !

RE: Statement still executes, even after a STOP !

Reply by martin_goblet

Hi Vincent

One of my colleague upgraded to the 7.3 commercial version. He does have exactly the same behaviour on consuming statements.

If he tries to stop a statement he gets an error saying “Script execution has already terminated”, but he can’t work in this session. The statement runs to the end, and then he’s able to work again.

Regards

Martin

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Navigator Forum
notifications altogether.

SQL Navigator - Discussion Forum

Flag
this post as spam/abuse.

Hi Vincent

It’s for a lot of different statements, but you can only see it on consuming ones, because the other are already ended :wink:

The ones with @ are automatically slower, due to the distance of the other database.

I can’t find a rule to distinguish the ones that are stoppable and the ones you’ve to wait the execution end.

Regards

Martin

Hi Vincent

My colleagues upgraded from 6.7 to 7.3 and they all have the same problem.

Regards

Martin

Hi Vincent

In v 7.5.0.64 32bit the problem is still the same. Very very annoying.

Regards

Martin

Same Problem… From 7.3.4053

https://www.toadworld.com/products/sql-navigator/sql_navigator_beta_program/f/101/t/25184