# Connection time too long

**URL:** <https://forums.toadworld.com/t/connection-time-too-long/10650>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [August 31, 2011, 11:22pm UTC](https://forums.toadworld.com/t/connection-time-too-long/10650 "2011-08-31T23:22:52Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![AUPPAL](https://avatars.discourse-cdn.com/v4/letter/a/7bcc69/32.png) [@AUPPAL](https://forums.toadworld.com/u/AUPPAL)\
**Post date:** [August 31, 2011, 11:22pm UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/1 "2011-08-31T23:22:52Z")

</div>

After we upgraded our Oracle ERP database to 11gR2, the connection time on Toad has increased to almost 3 minutes.

Before upgrade, we could connect within seconds.

This is not the case with other non-ERP 11gR2 databases - they connect fine.

Our Toad version is 9.5

Please help

Amith

---

<div class="post-metadata">

**Author:** ![AUPPAL](https://avatars.discourse-cdn.com/v4/letter/a/7bcc69/32.png) [@AUPPAL](https://forums.toadworld.com/u/AUPPAL)\
**Post date:** [September 6, 2011, 8:13am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/2 "2011-09-06T08:13:58Z")

</div>

Can someone help please.

Amith

---

<div class="post-metadata">

**Author:** ![HillbillyToad](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/hillbillytoad/32/190_2.png) [@HillbillyToad](https://forums.toadworld.com/u/HillbillyToad)\
**Post date:** [September 6, 2011, 8:13am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/3 "2011-09-06T08:13:58Z")

</div>

It could be options related (toad) or stats related (Oracle).

Try this on the first possibility

[http://asktoad.com/DWiki/doku.php?id=troubleshooting](http://asktoad.com/DWiki/doku.php?id=troubleshooting)

For the 2nd, ask your DBAs if the data dictionary stats have been collected  
since the upgrade. Toad queries them heavily and would be negatively impacted if  
they had poor plans.

Jeff

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [September 6, 2011, 8:13am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/4 "2011-09-06T08:13:58Z")

</div>

If it’s still slow after checking stats….I’ve seen comments  
from users that dba\_synonyms and all\_synonyms are slow on 11gr2. So under  
options -\> Schema Browser -\> Data, make sure the “User Lists”  
option is set to “Show all users” or “show all users that own  
objects”. That will prevent queries to all\_synonyms on startup.  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/4/4cc1ebbd976bdcc71c657a5c9932c7a292e25038.jpeg)

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [September 6, 2011, 8:13am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/5 "2011-09-06T08:13:58Z")

</div>

If it’s still slow after checking stats….I’ve seen comments  
from users that dba\_synonyms and all\_synonyms are slow on 11gr2. So under  
options -\> Schema Browser -\> Data, make sure the “User Lists”  
option is set to “Show all users” or “show all users that own  
objects”. That will prevent queries to all\_synonyms on startup.  
 ![image001.jpeg](https://global.discourse-cdn.com/quest/original/2X/6/6cbfe84af14cce3022795769804ecfa84832b5fd.jpeg)

---

<div class="post-metadata">

**Author:** ![AUPPAL](https://avatars.discourse-cdn.com/v4/letter/a/7bcc69/32.png) [@AUPPAL](https://forums.toadworld.com/u/AUPPAL)\
**Post date:** [September 9, 2011, 12:12am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/6 "2011-09-09T00:12:45Z")

</div>

12:13:41:843 Checking for TOAD\_PROFILER

12:17:10:030 get nls\_charset from server

12:17:10:046 Checking for RAC support…

12:17:10:046 Out of RAC support check.

12:17:10:046 Fetching owner names

12:17:10:109 TTeamCoding.AddTCSession

12:17:10:109 TTeamCoding.CheckSupport

12:17:10:109 -\> TTeamCoding.ExecuteQuery( SELECT table\_name, table\_owner FROM ALL\_SYNONYMS WHERE owner = ‘PUBLIC’ AND synonym\_name = ‘!SYN\_TC!’ )

12:17:10:109 SessionID: 0, Database: FINT, SQL: SELECT table\_name, table\_owner FROM ALL\_SYNONYMS WHERE owner = ‘PUBLIC’ AND synonym\_name = ‘QUEST\_COM\_TEAM\_CODING’

12:18:52:544 \<- TTeamCoding.ExecuteQuery

Is it mandatory to have the toad profiler on .

Amith

Hello,  
I managed to get startup debug output and as per that most of the time is spent on the following steps:

---

<div class="post-metadata">

**Author:** ![AUPPAL](https://avatars.discourse-cdn.com/v4/letter/a/7bcc69/32.png) [@AUPPAL](https://forums.toadworld.com/u/AUPPAL)\
**Post date:** [September 9, 2011, 2:08am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/7 "2011-09-09T02:08:04Z")

</div>

Hello,  
I have managed to fix this 🙂

The issue is with the bug in 11G version with queries against all\_synonyms.

Refer metalink note : \*\*Selects Against ALL\_SYNONYMS Very Slow in 11.2.0.1. and 11.2.0.2. [ID 1242844.1]

I have recreated the all\_synonyms view as per the note and that fixed the connection time issue.

Thanks everyone for the inputs.

Wihout TOAD tracing , I could not have figured this out.

Amith\*\*

---

<div class="post-metadata">

**Author:** ![HillbillyToad](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/hillbillytoad/32/190_2.png) [@HillbillyToad](https://forums.toadworld.com/u/HillbillyToad)\
**Post date:** [September 9, 2011, 7:23am UTC](https://forums.toadworld.com/t/connection-time-too-long/10650/8 "2011-09-09T07:23:58Z")

</div>

So glad you got to the bottom of this. Thanks for sharing the fix!

Jeff
