Execute as Script (F5) Crashes/Freezes/Hangs

Hello,

In this post "Execute as Script" is referred to as F5 and "Execute Statement" is referred to as F9.

From Toad for Oracle's Editor:

  1. F5 of any DDL or DML (non-select) freezes (have to "End Task" for Toad for Oracle in Window's Task Manger). In another Oracle session, I can see that the original session is inactive and "done" processing the DDL or DML.
  2. F9 of DDL (e.g. create table) is slow (screen refreshes a number of times), but does eventually finish.
  3. F9 of DML is okay.
  4. F5 or F9 of select statement is okay.

The behavior of item 1 above has changed (though the Toad for Oracle version has not changed).

Using Toad for Oracle 13.3.0.181 on a Windows 10 Enterprise (64-bit) 20H2 Windows Feature Experience Pack 120.2212.4180.0 with three Oracle Instant Clients installed 12.1.0.2, 19.3.0.0, and 19.14.0.0 (all 64-bit) connected to an Oracle 19c EE version 19.16.0.0.0 (on a remote Red Hat server). My Toad for Oracle connection is configured to use the 19.3.0.0 Instant Client. I suspect either the latest Oracle patch on the remote Red Hat server, some recent Windows patch, or some interaction with the Instant Clients and Toad for Oracle.

I do not experience the issue when using SQL*Plus or Toad Script Runner.

Do you have any ideas as to what may be causing this change in behavior, so that I can pass along any advice/recommendations to my computer support group in my organization?

Thanks in advance.

Hi Space Cord

It's hard to say what's going on here. If I were you I would reset user files, then bring back connections and whatever other settings that you are interested in. Steps to do that are here

Also, if you can convince your mgmt to update to the latest version, I'd recommend doing that too. Version 16.2 will be coming out in about a month. We've been mostly focused on stability improvements in every release since version 13.3, but some nice features have been added also.

-John

Hello,

We upgraded to 16.2.98.1741 (64 bit) with DB Admin Module.

This issue is still occurring.

We have found that InstantClients can make a difference.

InstantClients 19.3.0.0 (64-bit) and 19.14.0.0 (64-bit) have very slow DDL (F5) from the Editor response times (F5 in TSR is fine). The actual DDL statements seem to occur fast, but it takes Toad for Oracle a long time (several minutes) before it let's me do anything else (mouse cursor is blue spinning circle).

InstantClient 12.1.0.2 (64-bit) is faster, but still not as fast as TSR.

Any further ideas?

Thank you.

I don't know what would cause that much of a delay. I don't have that problem here with a 19c client.

In Toad 16.2, you have the option of not using any Oracle client at all. (You can only change the option if you don't have an active connection). Does that help?

Check options -> Oracle -> Transactions. Is "Execute scripts in Toad Session" checked? It will be faster if it is.

Is tracing enabled in the Editor (look on the "trace" tab)? It will be faster if it is not.

Is DBMS Output enabled in the Editor (DBMS Output tab)? It will be faster if it is not.

Check Options -> Online -> Toad Improvement Program. Does it help if you set that to "No"?

You can try turning on spool SQL with timings to see if that reveals anything.

Thank you for the tips. Sorry for the delayed reply.

Correction: I am not experiencing differences between Oracle Clients (they are all equally slow). The reason for the previous incorrect assessment was that the first editor script execution (F5) of a brand new Toad for Oracle instance appears to be faster than subsequent executions. Previously, I didn’t close/re-start Toad for Oracle each time I changed the Oracle Client. However, this time I did restart Toad for Oracle with each test, and all Oracle Clients appear to behave roughly the same from a response time perspective.

Unchecking “Connect using Oracle Client” actually made things worse (slower).

Yes, “Execute scripts in Toad session” is checked.

Tracing is not enabled.

DBMS Output is disabled.

Toad Improvement Program was “Yes”. Changing it to “No” did not help.

The below is the output of the spool SQL. Please note the “Timestamp” seems to be more revealing than the “Elapsed Time” field.
There appears to be two culprits.

3rd to last step:

begin
  sys.dbms_output.get_lines(:LINES, :NUMLINES);
end;

and 2nd to last step:

select ... from user_constraints...

The full spool SQL:

----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.348
select 'Test connection' from dual;
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.502
BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.765
select PARAMETER,VALUE from nls_session_parameters where PARAMETER in('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT','NLS_CURRENCY');
-- Elapsed Time: 0.069 seconds
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:09.950
select to_char(9,'9C') from dual;
-- Elapsed Time: 0.069 seconds
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:10.034
create table MY_TABLE(id number);
-- Elapsed Time: 0.069 seconds
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:10.166
drop table MY_TABLE;
-- Elapsed Time: 0.068 seconds
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:10.235
begin
  sys.dbms_output.get_lines(:LINES, :NUMLINES);
end;
:LINES(VARCHAR[32767],)=<PL/SQL TABLE[10]>
:NUMLINES(INTEGER,)=0
-- Elapsed Time: 0.068 seconds
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:32:51.842
select user as owner, con.constraint_name
from user_constraints con
where owner = :own and con.table_name = :tn;
own(VARCHAR[11],IN)='MY_SCHEMA_NAME'
:tn(VARCHAR[18],IN)='MY_TABLE'
-- Elapsed Time: 0.069 seconds
 
----------------------------------
-- Session: MY_SCHEMA_NAME@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=url)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=my_service)))
-- Timestamp: 16:33:09.016
Select created, last_ddl_time, object_id, status
from sys.user_objects
where object_name = :nm
and object_type = :t;
:nm(VARCHAR[18],IN)='MY_TABLE'
:t(VARCHAR[5],IN)='TABLE'
-- Elapsed Time: 0.063 seconds

Thank you in advance for your continued assistance in resolving this on-going issue.

Do you have team coding enabled?

Looks like that's where the constraints query is coming from, and likely the cause of the slowness. I wonder if there is a Source Control login (or or something else souce-control related) happening in that delay period.

No Team Coding enabled. VCS Provider: <None>.

As an additional test, I unchecked all the options in the Team Coding Configuration User Settings dialogue, but this did not improve results.

EDIT: I incorrectly originally said "Local Settings" dialogue. I ought to have said "User Settings" dialogue. This post has been edited.

That SQL to user_constraints will run if the Team Coding tables exist in your database, even if you aren't using Team Coding. It runs after a table is created, because Team Coding wants to know if there are any a new constraints that it needs to control along with the newly created table.

Please go to main menu -> Team Coding -> Configuration.

Then see if this menu item says "Remove Team coding from database" or "Install Team Coding to Database"

image

If it says "Remove", then the Team Coding tables exist. Click it and Toad will drop them. Their names are TCX_CONFIG, TCX_OBJECTS, TCX_TEAM_PROJECTS, and TCX_OBJECT_HISTORY. There are also some related objects (sequences, triggers, and synonyms)

Then try your script again and let me know if that helped.

I think you'll find better performance overall in Toad with those tables dropped.

(Edit: For what it's worth, it seems like a bug that Team Coding is doing "work" if those tables are empty. I'm investigating that now)

Thanks.

The "Remove Team Coding from database" is greyed out.

The "Create Uninstall Script for database" is not greyed out. The script created is dropping 4 sequences, 4 tables, and one public synonym.

The 4 tables do exist with owner=TOAD (that have 10 total associated indices, 3 total associated LOB objects, and 6 total associated triggers). All 4 tables have 0 rows and appear to have been created in May of 2017.

I need to check with my team before dropping these TOAD owned objects. Will let you know the results. This sounds promising, thank you.

1 Like

Sounds good.

If you log in as a DBA type user (or as the TOAD user who owns those objects), I believe that menu item will be enabled.

Success. Thank you for your persistence with this one. Total elapsed time now is 1.75 seconds from spool SQL.

I was considering using Team Coding in the future, but a bit hesitant now. Would be nice to enable Team Coding without taking such a performance hit.

EDIT: Before the Team Coding TOAD owned objects where dropped, I think saw in one of the dialogues that the Team Coding objects were compatible with Toad version 8. So, perhaps such sluggish performance would not be experienced with Team Coding objects that were compatible with the version (16.2) of Toad for Oracle being used.

Thanks again.

I'm glad that was it!

TC shouldn't cause that much of a performance hit. If you decide you want to give it a try in the future and it still causes that delay once it is actually configured, let us know and we'll try to get it sorted out.