Toad World® Forums

Connection time too long

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

Can someone help please.

Amith

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

Try this on the first possibility

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

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

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

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:

Hello,
I have managed to fix this :slight_smile:

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**

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

Jeff