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