Taking a long time to connect

[SPRJ]

PL/SQL Developer takes a long time to connect to a database. The screen freezes and I have to wait several minutes.

Can anybody help me?

 

[Marco Kalter]

Maybe this is an environment problem. Can you start PL/SQL Developer without connecting, go to Help > Support Info and send me the text from the "Info" tab page?

 

[T-Gergely]

See my old topic about it.

Neither Marco, nor Oracle was eager to do something about it.

 

[SPRJ]

PL/SQL Developer
Version 13.0.0.1883 (64 bit)
443.122660 - Unlimited user license
Service Contract: 01/01/2020
Windows 10 Build 15063

Physical memory : 16,637,816 kB (8,915,440 available)
Paging file : 28,925,816 kB (18,088,388 available)
Virtual memory : 137,438,953,344 kB (137,438,595,288 available)

Parameters
C:\Program Files\PLSQL Developer 13\plsqldev.exe
nologon
nosplash

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False
Allow Multiple Connections: True

Preference Files
C:\Users\prv892\AppData\Roaming\PLSQL Developer 13\Preferences\default\Default.ini
C:\Users\prv892\AppData\Roaming\PLSQL Developer 13\Preferences\prv892\default.ini

License File
C:\Users\prv892\AppData\Roaming\PLSQL Developer 13\aalf.dat

Debug file
C:\Users\prv892\AppData\Roaming\PLSQL Developer 13\PlSqlDev.elf

Plug-Ins
*Active Query Builder (C:\Program Files\PLSQL Developer 13\PlugIns\ActiveQueryBuilder.dll)
*PL/SQL Documentation (plsqldoc) (C:\Program Files\PLSQL Developer 13\PlugIns\plsqldoc.dll)
(* is Active)

Aliases
ALMDSV
ALMPRD
AUDITPRD
CASE
CATPRD
...

Homes
OraClient11g_home1 (C:\Oracle\product\11.2.0\client_1)

DLLs
C:\Oracle\product\11.2.0\client_1\bin\oci.dll

TNS File
C:\Oracle\product\11.2.0\client_1\Network\Admin\tnsnames.ora

Using
Home: OraClient11g_home1
DLL: C:\Oracle\product\11.2.0\client_1\bin\oci.dll
OCI: version 11.1 (11.2.0.1.0)

Process
Working Set = 127,737,856
Memory = 26,804,504
GDI Objects = 1393
User Objects = 365
Handles = 413

Monitors
PixelsPerInch = 96

Id = 0
PPI = 96
Primary = True
Handle = 65612
Left = 0
Top = 0
Width = 1920
Height = 1080

Id = 1
PPI = 96
Primary = False
Handle = 131073
Left = 1920
Top = 0
Width = 1920
Height = 1080

MainFormOnTaskbar = True

 

[Marco Kalter]

Thanks. Can you also send me C:\Oracle\product\11.2.0\client_1\Network\Admin\sqlnet.ora?

 

[SPRJ]

Thanks. This is it:

# sqlnet.ora Network Configuration File: C:\oracle\Dev10gR2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

#TRACE_LEVEL_CLIENT = OFF

#NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN = world

NAME.DEFAULT_ZONE = world

 

[Marco Kalter]

Can you change this line:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

To:

#SQLNET.AUTHENTICATION_SERVICES= (NTS)

Let me know if this helps.

 

[SPRJ]

It didn`t help. Still taking a long time to connect.

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 13\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 13 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 13).

 

[SPRJ]

I have just found something:

00:00:06.511 TOracleQuery $0F2DE410 Start Query.Execute
SQL = select null from all_synonyms where 1=0
00:00:13.629 TOracleQuery $0F2DE410 End (0 records processed)
Duration = 7.125
------------------------------------------------------------------------------------------
00:00:13.928 TOracleQuery $0F2DE410 Start Query.Describe
SQL = select * from sys.dba_tab_columns where rownum < 1
00:00:17.138 TOracleQuery $0F2DE410 End
Duration = 3.219
------------------------------------------------------------------------------------------
00:00:17.292 TOracleQuery $0F2DE410 Start Query.Describe
SQL = select * from sys.dba_triggers where rownum < 1
00:00:19.918 TOracleQuery $0F2DE410 End
Duration = 2.625
------------------------------------------------------------------------------------------
00:00:19.919 TOracleQuery $0F2DE410 Start Query.Describe
SQL = select * from sys.dba_constraints where rownum < 1
00:00:20.558 TOracleQuery $0E2E2140 End (100 records processed)
Duration = 14.281
------------------------------------------------------------------------------------------
00:00:34.480 TOracleQuery $0F2DE410 Start Query.Describe
SQL = select * from sys.dba_tables where rownum < 1
00:00:58.896 TOracleQuery $0F2DE410 End
Duration = 24.407
------------------------------------------------------------------------------------------
00:01:03.752 TOracleQuery $0F2DE410 Start Query.Describe
SQL = select * from sys.dba_tab_privs where rownum < 1
00:01:41.787 TOracleQuery $0F2DE410 End
Duration = 38.015

These querie are taking a long time.

 

[Marco Kalter]

These queries are just described (not executed) to check if a view is accessible, and this should be very fast. To omit these activities you can disable the "Use DBA views if available" option on the Oracle / Options preference page.

 

[T-Gergely]

That's what I mentioned. If you're good at Oracle, refer to their unsolved Bug 25874121 - PARSING TAKING LONG TIME IN QUERIES RELATED TO SYS.DBA_CONSTRAINTS ON 12.1.0.2 they opened for me a long time ago.

Plus Bug 19913929 : QUERIES AGAINST DATA DICTIONARY VERY SLOW AFTER UPGRADE TO 12.1 etc.

AND/OR

try to convince Marco that these checks should be skipped or done in a more efficient way.