Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

Toad for Oracle
1

Hello

I have Toad 12.0.0.61 64bit fresh installed on Windows 7 64 bit Notebook Lenovo W510 (32Gb Memory). I have local Oracle 11.2.0.3/12.1.0.1 databases up and running (small dbs, only 1GB each). When I start new connection by SQL*Plus, the connection is open until milliseconds. When I start a new Toad connection to one of this databases, the duration is over 6 seconds. Oracle client version which Toad is using is 11.2.0.3.

I have already done:

Also the GUI seems to be slower than Toad 11. The Windows sometimes hangs for some milliseconds until they are open. In the Toad menubar i have the message [no response] and after some waits, the windows are open.

Any help is very appreciated.

Regards, Martin

2

Martin,

There is a patch available via support for that version (to 12.1). Many of the issues you're seeing were addressed there. Let us know if you're still having issues after upgrading.

Thanks,

Greg

On Sep 5, 2013, at 4:50 AM, "martin.berger" bounce-martinberger@toadworld.com wrote:

Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

Thread created by martin.berger
Hello

I have Toad 12.0.0.61 64bit fresh installed on Windows 7 64 bit Notebook Lenovo W510 (32Gb Memory). I have local Oracle 11.2.0.3/12.1.0.1 databases up and running (small dbs, only 1GB each). When I start new connection by SQL*Plus, the connection is open
until milliseconds. When I start a new Toad connection to one of this databases, the duration is over 6 seconds. Oracle client version which Toad is using is 11.2.0.3.

I have already done:

Also the GUI seems to be slower than Toad 11. The Windows sometimes hangs for some milliseconds until they are open. In the Toad menubar i have the message [no response] and after some waits, the windows are open.

Any help is very appreciated.

Regards, Martin

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

When you make a connection in SQLPlus, all SQLPlus does is make the connection. It does not run queries behind the scenes to determine what your privileges
are, what windows can be displayed, etc.

Toad has to do a lot of things besides just make the connection. It runs queries to determine:

a)
System privileges

b)
Default roles

c)
Database character set

d)
instance and server name

e)
Authentication type

f)
Oracle version

g)
If java is installed

h)
If you have privs to execute dbms_debug

i)
If Code tester is installed. If so, which version

j)
If Team coding is installed

k)
If you are connected to a RAC instance

l)
If you are connected to an Amazon RDS instance

m)
If Code Xpert tables are installed

n)
If you are restricted by Toad Security

o)
Direct grants on DBA_ views, if “Check for access to dba views” is checked, but you don’t have any role or system privilege that would give you such
privileges

p)
Schema names (based on options – all, only users with objects, etc)

Then if you have any windows set to auto-open, such as Schema Browser or Editor, those windows have some queries of their own to run.

So it really isn’t fair to compare Toad’s connection time to SQL*Plus’s connection time. Toad is probably connecting just as fast,
but all this other stuff takes time too.

Now, all that said….when you say “6 seconds”….if you are timing that from the moment you click on Toad, and then it starts and autoconnects….6
seconds is about right. If Toad is already running and you just make a connection (with no windows auto-opening), if you have a reasonably fast, local database, that should take only about 1 second. If it is taking longer, you can turn on spool sql to
see what Toad is doing and maybe isolate the slowest running query, if one stands out. I’ll be happy to look at spool sql output if you post it. I have also seen cases where the installation of an additional Oracle client can slow down the initial connection
time. But for me, when that happens, it slows things down much more than 6 seconds.

From: martin.berger [mailto:bounce-martinberger@toadworld.com]

Sent: Thursday, September 05, 2013 3:51 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

Toad 12 very slow initial connection - already tried the tips
in the posts / GUI slow

Thread created by martin.berger

Hello

I have Toad 12.0.0.61 64bit fresh installed on Windows 7 64 bit Notebook Lenovo W510 (32Gb Memory). I have local Oracle 11.2.0.3/12.1.0.1 databases up and running (small dbs, only 1GB each).
When I start new connection by SQL*Plus, the connection is open until milliseconds. When I start a new Toad connection to one of this databases, the duration is over 6 seconds. Oracle client version which Toad is using is 11.2.0.3.

I have already done:

Also the GUI seems to be slower than Toad 11. The Windows sometimes hangs for some milliseconds until they are open. In the Toad menubar i have the message [no response] and after some waits,
the windows are open.

Any help is very appreciated.

Regards, Martin

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

4

Hi John

Thank you for your answer. I have a fast local database on a SSD. Can you explain me where to enable the spooling of the Toad login sql?

Regards, Martin

5

From the main menu:

Database -> Spool SQL -> Spool to Screen.

From: martin.berger [mailto:bounce-martinberger@toadworld.com]

Sent: Monday, September 09, 2013 11:18 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

RE: Toad 12 very slow initial connection - already
tried the tips in the posts / GUI slow

Reply by martin.berger

Hi John

Thank you for your answer. I have a fast local database on a SSD. Can you explain me where to enable the spooling of the Toad login sql?

Regards, Martin

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

6

I have enabled spooling, but the inital (first) connect which is so slow is not traced. It looks for me that the first connection is slower than the second,third etc. to same database.

Is it possible to trace the first connect?

Regards, Martin

7

This may sound like a dumb question, but are you enabling spooling before you attempt to make the first connection?

If you have autoconnect set up on the login screen, you’ll have to turn that off temporarily.

If that’s not it, then I don’t know why it wouldn’t show connect info. So it is just the connection that’s not being traced, but the queries after the connection
is made are being traced? If the queries all seem fast, but the initial connection is slow, then it doesn’t sound like the problem is within Toad. I know you said SQL*Plus always connects fast….is it using the same Oracle Home as Toad?

From: martin.berger [mailto:bounce-martinberger@toadworld.com]

Sent: Monday, September 09, 2013 12:17 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

RE: Toad 12 very slow initial connection - already
tried the tips in the posts / GUI slow

Reply by martin.berger

I have enabled spooling, but the inital (first) connect which is so slow is not traced. It looks for me that the first connection is slower than the second,third etc. to same database.

Is it possible to trace the first connect?

Regards, Martin

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

8

Now I have understand what you mean - Toad is spooling. Here is the output from the spool action - can you see some significant waits?

The biggest time difference is here:

Session: SYS@CGM.TRIVADISTRAINING.COM

Timestamp: 19:44:09.135

SELECT u.NAME

FROM sys.USER$ u

WHERE u.TYPE# = 1

ORDER BY 1

Session: SYS@CGM.TRIVADISTRAINING.COM

Timestamp: 19:44:13.072

DECLARE

TYPE table_names IS VARRAY (3) OF VARCHAR2 (30);

currval_not_defined EXCEPTION;

PRAGMA EXCEPTION_INIT (currval_not_defined, -8002);

sys_prof_valid NUMBER;

toad_prof_valid NUMBER;

rst NUMBER;

sys_pack VARCHAR2 (17);

prof_seq VARCHAR2 (24);

tables table_names;

dbms_prof_tables CONSTANT table_names

:= table_names (‘plsql_profiler_data’,

‘plsql_profiler_runs’,

‘plsql_profiler_units’) ;

dbms_hprof_tables CONSTANT table_names

:= table_names (‘dbmshp_runs’,

‘dbmshp_function_info’,

‘dbmshp_parent_child_info’) ;

9

On another topic – WHY are you connecting as SYS ???

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/08/12/common-toad-security-connection-mistakes.aspx

Unless you’re running an oracle script from ?\rdbms\admin directory that requires SYS or starting/stopping instance via instance manager – there’s no reason to use the SYS account. That’s supposed to be like root on Unix – you don’t use
it unless you have a darn good reason ….

From: martin.berger [mailto:bounce-martinberger@toadworld.com]

Sent: Monday, September 09, 2013 12:49 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

RE: Toad 12 very slow initial connection - already
tried the tips in the posts / GUI slow

Reply by martin.berger

Now I have understand what you mean - Toad is spooling. Here is the output from the spool action - can you see some significant waits?

The biggest time difference is here:

Session:
SYS@CGM.TRIVADISTRAINING.COM

Timestamp: 19:44:09.135

SELECT u.NAME

FROM sys.USER$ u

WHERE u.TYPE# = 1

ORDER BY 1

10

The query against sys.user$ should execute very quickly, so I don’t think it is the problem. If you have tens of thousands of users, that might slow things
down a bit here as Toad processes all those records, but somehow I doubt that is what’s happening here.

That pl/sql block is executed by the Editor when it opens. So it sounds like you have the Editor configured to auto-open when a connection is made, and maybe
the Editor is opening slowly for some reason.

As a test, try disabling the auto-opening of the Editor (in Options -> Windows) and see if that helps your connection times.

If it does, you may have a large sql history file or something like that which is causing the Editor to open slowly.

From: martin.berger [mailto:bounce-martinberger@toadworld.com]

Sent: Monday, September 09, 2013 12:49 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12 very slow initial connection - already tried the tips in the posts / GUI slow

RE: Toad 12 very slow initial connection - already
tried the tips in the posts / GUI slow

Reply by martin.berger

Now I have understand what you mean - Toad is spooling. Here is the output from the spool action - can you see some significant waits?

The biggest time difference is here:

Session:
SYS@CGM.TRIVADISTRAINING.COM

Timestamp: 19:44:09.135

SELECT u.NAME

FROM sys.USER$ u

WHERE u.TYPE# = 1

ORDER BY 1

11

Hi John

After disabling the auto-opening, the connection is created in half of a second. My issue seems to be in the editor.