Toad 12.1 hangs for long when connecting to database via oracle connection manager

Toad for Oracle
1

Hi Experts,

we have recently migrated to Oracle cloud and testing database connection from desktop, we are using oracle connection manager(OCM) to serve database connection, we see our Toad connections from desktop hangs for long time but "sql developer" connection is good without issues, our tns entries has database port and OCM port as all our database connections in cloud is served by OCM

Kindly help if any have faced this issue

Thanks,
Tanveer

2

What do you mean by "it hangs"? It fails to connect and just sits there? Eventually connects but takes longer than expected? Connects OK but then later hangs?

Also, SQL Developer is perhaps not the best comparison because that uses JDBC and Toad uses OCI for connectivity. SQL*Plus would be a better comparison.

3

HI,

Today we patiently tested multiple times and below is the result

1- Latest version of sqldeveloper connects without issues
2-SQL*Plus connects fine without issues
3- Eventually Toad connects but takes longer(20 mins approx.), after 20 mins it connects and works fine. From my backed session I checked v$session during the hung period(20 mins), I noticed there are eight p00x parallel slave processes launched for one toad connection and those parallel slave processes disappear once toad connection is established(after 20 mins those slave processes disappear), I am suspecting these parallel processes are causing 20 mins delay for toad connections to establish. Please let me know if anyone else has faced this issue and how to fix this

Note - my tnsentries has both OCM(oracle connection manager) and database port as database connections in cloud is served by OCM

Thanks,
Tanveer

4

Toad isn't doing anything to intentionally trigger p00x parallel queries. The database decides if it wants to do stuff in parallel. I am not sure if OCM has anything to do with that or not.

However, Toad may make some connections in the background for various tasks. It might help if you configure Toad it to not do that, and do everything in one session. See the following on how to do that. Let me know how it goes after that change.

Toad does execute a big pl/sql block when a connection is made. We've done our best to keep that executing as quickly as possible. Normally it should take just a second or two. But I wonder if something in there is what is causing the delay and all the parallel queries. Maybe turning on spool SQL in Toad before you make your connection will give some clues (database -> Spool SQL -> Spool to screen).

5

Hi,

Thanks for the guidance, I worked with developer to trace the Toad session and below are the findings

while connecting to PDB(pluggable) Oracle Database, there is a package (not DB stored package) which is being executed for every session whenever user connects from TOAD.

While debugging , developer has found that ALL_SYNONYMS is used in the package where it is taking long time to execute, and that block is being errored out with "ORA-03113 end-of-file on communication channel".

select count(*) from sys.all_synonyms; <<=== This took 8 mins from the back end session, hence culprit for toad connection to be slow

Doc ID 2297471.1 - Queries Against SYS.ALL_SYNONYMS Inside A PDB Are Very Slow
Doc ID 2210513.1 - SQLLDR Utility is Running Slow when ALL_SYNONYMS are Used/Slow ALL_SYNONYMS in 12c CDB

Doc ID 2297471.1 is applicable in my case, after disable the hidden parameter "_common_data_view_enabled", query to sys.all_synonym was coming back quick and toad connection are threw without any delay.

Note - super user login via Toad is quite as it uses DBA_SYNONYMS, users which doesn't have grants to dba_synonyms uses all_synonyms and take long way to login due the issue unless fix given in the note id is not applied

Thanks for the support

6

Thank you for the follow-up.

Did the slowness not occur before you migrated to OCM? Toad doesn't do any different queries for OCM, so it should have been running the exact same PL/SQL block before, unless you were connecting with a different user (one that could select from DBA_SYNONYMS instead of ALL_SYNONYMS, for example)

7

No, we did not face this before migration as the database was Non-CDB before migration.

Issue is with respect to PDB database(multi-tenant), we migrated database from On-premise datacentre to Oracle cloud, On-Premise was Non-CDB(not multi-tenant), in cloud it is mandatory to use multi-tenant, hence we hit the issue connecting to database in cloud

8

Oh, that makes sense, thanks.