Slow login over TCP
616027Jan 4 2008 — edited Jan 7 2008Hi All, and Happy New Year!
I am using a clean install of 11g on Centos 5, with some data imported from a 8.x server.
The actual problem I am trying to solve are slow connect/logins via some perl using DBI/DBD::Oracle, but this basic test with sqlplus shows the same problem nicely.
I can connect without TCP in 0.05s, but using TCP it takes 6+ seconds!
[oracle@legacy ~]$ time echo exit |sqlplus myuser/mypass
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 4 10:47:14 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
real 0m0.123s
user 0m0.030s
sys 0m0.037s
[oracle@legacy ~]$ time echo exit |sqlplus myuser/mypass@ora816
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 4 10:48:16 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
real 0m6.172s
user 0m0.037s
sys 0m0.030s
Further testing shows that if I connect using sqlplus myuser@ora816 I get the password prompt immediately - the delay is after entering the password. Not sure if that is relevant or not.
I have not been able to figure out how to enable tracing for sqlplus (but can for tnsping). The closest thing I can find to a problem is the following from running "adapters"
Error!!! Oracle Names Server Naming is not completely installed!
I ran netca and checked everything and everything looked fine, so I am not sure how to resolve this one (if it is indeed the cause of my slowness).
Here is all the relevant info I can think of. Please let me know if I need to provide anything else.
[oracle@legacy ~]$ set | grep ORACLE
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.1.0
ORACLE_SID=ora816
[oracle@legacy ~]$ adapters
Installed Oracle Net transport protocols are:
IPC
BEQ
TCP/IP
SSL
RAW
SDP/IB
Installed Oracle Net naming methods are:
Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
Error!!! Oracle Names Server Naming is not completely installed!
Installed Oracle Advanced Security options are:
RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication
[oracle@legacy ~]$ tnsping ora816
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 04-JAN-2008 10:54:10
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.1.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = perseus-legacy)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora816)))
OK (0 msec)
[oracle@legacy ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-JAN-2008 10:54:58
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 04-JAN-2008 09:04:53
Uptime 0 days 1 hr. 50 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/legacy/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=legacy)(PORT=1521)))
Services Summary...
Service "ora816" has 2 instance(s).
Instance "ora816", status UNKNOWN, has 1 handler(s) for this service...
Instance "ora816", status READY, has 1 handler(s) for this service...
Service "ora816XDB" has 1 instance(s).
Instance "ora816", status READY, has 1 handler(s) for this service...
Service "ora816_XPT" has 1 instance(s).
Instance "ora816", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@legacy ~]$ lsnrctl services
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-JAN-2008 10:55:51
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "ora816" has 2 instance(s).
Instance "ora816", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "ora816", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:90 refused:0 state:ready
LOCAL SERVER
Service "ora816XDB" has 1 instance(s).
Instance "ora816", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: legacy.my.corp, pid: 2934>
(ADDRESS=(PROTOCOL=tcp)(HOST=legacy)(PORT=3863))
Service "ora816_XPT" has 1 instance(s).
Instance "ora816", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:90 refused:0 state:ready
LOCAL SERVER
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[oracle@legacy ~]$ cat /u01/app/oracle/product/11.1.0/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.1.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# debug
TNSPING.TRACE_LEVEL = 16
TNSPING.TRACE_DIRECTORY = /tmp
TRACE_LEVEL_CLIENT = support
TRACE_DIRECTORY_CLIENT = /tmp
TRACE_FILE_CLIENT=sqlnet.trc
TRACE_UNIWUE_CLIENT = TRUE
TRACE_DIRECTORY_SERVER=/tmp
TRACE_FILE_SERVER=server.trc
TRACE_LEVEL_SERVER=support
[oracle@legacy ~]$ cat /u01/app/oracle/product/11.1.0/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ora816)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = extproc)
)
)
ORA816 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = legacy)(PORT = 1521))
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = legacy)(PORT = 1521))
)
)
[oracle@legacy ~]$ cat /u01/app/oracle/product/11.1.0/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA816 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = legacy)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora816)
)
)
[oracle@legacy ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost loopback
::1 localhost6.localdomain6 localhost6
10.111.11.6 legacy legacy.my.corp