Toad 13, Extreme compile times using F9

Toad for Oracle
1

Hello. After upgrading to Toad 13 (Currently 13.1.0.78) multiple developers in out company have started experiencing extreme compile times when compiling using F9 in the editor.

If i leave the editor alone for EXACTLY 5 minutes and then press F9, it will take between 30 seconds to 2 minutes to complete the compile. Further compilations within the 5 minute window are instant. Compiling with F5 is instant no matter how long we wait.

Starting an SQL tracker on the following statement...

create or replace package Testpackage
  authid definer
as
end;

...Yields these results:

Timestamp: 03-06-2020 16:28:15

create or replace package Testpackage
  authid definer
as
end;

Elapsed time: 0.003

--------------------------------------------------------------------------------
Timestamp: 03-06-2020 16:28:15

Select line, position, SUBSTR (text, 1, 200) text
FROM SYS.user_errors
WHERE name = 'TESTPACKAGE' AND TYPE = 'PACKAGE'
ORDER BY sequence


oname=['TESTPACKAGE']
otype=['PACKAGE']

Elapsed time: 0.023

--------------------------------------------------------------------------------
Timestamp: 03-06-2020 16:28:15

begin  :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;

Result=['D892559']

Elapsed time: 0.001

This confirms that the compile is indeed instant, but it still takes up to 2 minutes before Toad responds again.
Multiple experienced Toad users and a few DBA's have spent considerable time trying to figure out what happens to Toad after 5 minutes, but we have given up.

2

Sounds like you might be getting timed out. Have you tried Main menu -> Session -> Test All Connections first, if you are after the 5 minute mark?

3

Thanks for the quick answer :slight_smile: It's not connected to any time-outs. Often i'm working on a query and then returning to the package 5-10 minutes later and encounter the problem. Also, as mentioned, F5 compiles are instant :slight_smile:

4

Are you sure? Have you tried it?

Depending on the settings in Options -> Oracle -> Transactions, the query that you are working on could be in a different database session than then package that you are trying to compile.

To get everything in the same session, set:
Execute queries in threads: Unchecked
Execute scripts in Toad session: Checked
Session for Explain Plan: Main Toad session

If your settings are different, please try Test All Connections after a 5 minute time out.

5

I should probably have been clearer in my previous answer :slight_smile: I have worked with Toad for 10 years so i'm an experienced user. Yes i have tried all basic connection based tweaks many times without luck.
My settings are identical to what you posted above, and i have also tried a lot of different combinations of settings.

My argument is that if it was connected to time-outs or database-level settings, i should be experiencing the same issues in SQL Plus or SQL Developer, but that has never happened.

6

OK, thanks for the clarification. I have to ask. :slight_smile:

I don't know offhand why that's happening.

So, you mentioned having DBAs involved. Have you tried an Oracle trace on the session while that delay is happening? If so, can you share the trace file with me? you can email it to john.dorlon@quest.com.

-John

7

Excellent idea. Though the SQL Tracker register the actual statements to only take 0.003 seconds.
Corona-lockdown makes it problematic to set up things with the DBA's, but I'll see if I can get one of them on Zoom for half an hour and try out some tracing.

8

For what it's worth, I just tried it here with 13.1 and could not reproduce it, even after letting the connection go idle for almost an hour.