oracle11g 应用程序慢,【DB】在windows系统中oracle11g TNSPING延迟连接缓慢

In this Document

APPLIES TO:

Oracle Net Services - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]

Information in this document applies to any platform.

SYMPTOMS

Connections from Windows 7 clients are very slow, taking 20 to 30 seconds to connect.

TNSping is also reporting very high value for round trip time

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 31-JAN-2011 12:37:51

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

c:\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = NodeA) (PORT = 1521))) (CONNECT_DATA = (SID = ORCL) (SERVER = DEDICATED)))

OK (21240 msec)

Oracle Net level 16 client trace of connection, shows the time is lost on using the socket 2500, with ntt2err: soc 2500 error - operation=1, ntresnt[0]=505, ntresnt[1]=60, ntresnt[2]=0

(4808) [31-JAN-2011 13:14:12:704] ntctst: size of NTTEST list is 1 - not calling poll

(4808) [31-JAN-2011 13:14:12:704] sntseltst: Testing for WRITE on socket 2500

(4808) [31-JAN-2011 13:14:33:733] sntseltst: FOUND: write request on socket 2500

(4808) [31-JAN-2011 13:14:33:733] ntt2err: entry

(4808) [31-JAN-2011 13:14:33:733] ntt2err: soc 2500 error - operation=1, ntresnt[0]=505, ntresnt[1]=60, ntresnt[2]=0

(4808) [31-JAN-2011 13:14:33:733] ntt2err: exit

(4808) [31-JAN-2011 13:14:33:843] nttcni: exit

(4808) [31-JAN-2011 13:14:33:843] nttcon: exit

(4808) [31-JAN-2011 13:14:33:843] nserror: entry

(4808) [31-JAN-2011 13:14:33:843] nserror: nsres: id=0, op=65, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

This time gap is seen before the connection handshake is started.

CHANGES

New installation on Windows 7

CAUSE

Windows 7 is setup with IPV6 and IPV4. Bind order for TCPIP is setup for the client, such that IPV6 is attempted first.  Network is not setup to use IPV6 or can be the TNS listener is not using IPV6.

In the Oracle Net client trace before the time gap, we can see Oracle Net taking the host name of the calling address and resolving to IPV6 and IPV4.

Example values here

Server Machine host name : NodeA

IPV6 IP : fe81::54b6:e777:dcb4:d57e%11

IPV4 IP : 111.22.22.333

(4808) [31-JAN-2011 13:14:12:704] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=NodeA)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)(CID=(PROGRAM=C:\Oracle\sqlplus.exe)(HOST=MachineA)(USER=oracle))))

(4808) [31-JAN-2011 13:14:12:704] nsgettrans_bystring: entry

(4808) [31-JAN-2011 13:14:12:704] nsgettrans_bystring: exit

(4808) [31-JAN-2011 13:14:12:704] nscall: entry

(4808) [31-JAN-2011 13:14:12:704] nsmal: entry

(4808) [31-JAN-2011 13:14:12:704] nsmal: 216 bytes at 0x10dd7c18

(4808) [31-JAN-2011 13:14:12:704] nsmal: normal exit

(4808) [31-JAN-2011 13:14:12:704] nscall: connecting...

(4808) [31-JAN-2011 13:14:12:704] nlad_expand_hst: Expanding NodeA

(4808) [31-JAN-2011 13:14:12:704] snlinGetAddrInfo: entry

(4808) [31-JAN-2011 13:14:12:704] snlinGetAddrInfo: exit

(4808) [31-JAN-2011 13:14:12:704] snlinGetNameInfo: entry

(4808) [31-JAN-2011 13:14:12:704] snlinGetNameInfo: exit

(4808) [31-JAN-2011 13:14:12:704] nlad_expand_hst: Adding IP fe81::54b6:e777:dcb4:d57e%11

(4808) [31-JAN-2011 13:14:12:704] snlinGetNameInfo: entry

(4808) [31-JAN-2011 13:14:12:704] snlinGetNameInfo: exit

(4808) [31-JAN-2011 13:14:12:704] nlad_expand_hst: Adding IP 111.22.22.333

(4808) [31-JAN-2011 13:14:12:704] snlinFreeAddrInfo: entry

(4808) [31-JAN-2011 13:14:12:704] snlinFreeAddrInfo: exit

(4808) [31-JAN-2011 13:14:12:704] nlad_expand_hst: Result: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=fe81::54b6:e777:dcb4:d57e%11)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=111.22.22.333)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)(CID=(PROGRAM=C:\Oracle\sqlplus.exe)(HOST=MachineA)(USER=oracle))))

(4808) [31-JAN-2011 13:14:12:704] nladini: entry

Build connection string with IPV6 address and attempts to find the host / machine.

(4808) [31-JAN-2011 13:14:12:704] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fe81::54b6:e777:dcb4:d57e%11)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)(CID=(PROGRAM=C:\Oracle\sqlplus.exe)(HOST=MachineA)(USER=oracle))))

(4808) [31-JAN-2011 13:14:12:704] nttbnd2addr: entry

(4808) [31-JAN-2011 13:14:12:704] snlinGetAddrInfo: entry

(4808) [31-JAN-2011 13:14:12:704] snlinGetAddrInfo: exit

(4808) [31-JAN-2011 13:14:12:704] nttbnd2addr: using host IP address: fe81::54b6:e777:dcb4:d57e%11

This request fails as network / TNS listener not setup to use IPV6

(4808) [31-JAN-2011 13:14:12:704] nttcni: trying to connect to socket 2500.

(4808) [31-JAN-2011 13:14:12:704] ntt2err: entry

(4808) [31-JAN-2011 13:14:12:704] ntt2err: exit

(4808) [31-JAN-2011 13:14:12:704] ntctst: size of NTTEST list is 1 - not calling poll

(4808) [31-JAN-2011 13:14:12:704] sntseltst: Testing for WRITE on socket 2500

(4808) [31-JAN-2011 13:14:33:733] sntseltst: FOUND: write request on socket 2500

(4808) [31-JAN-2011 13:14:33:733] ntt2err: entry

(4808) [31-JAN-2011 13:14:33:733] ntt2err: soc 2500 error - operation=1, ntresnt[0]=505, ntresnt[1]=60, ntresnt[2]=0

(4808) [31-JAN-2011 13:14:33:733] ntt2err: exit

(4808) [31-JAN-2011 13:14:33:843] nttcni: exit

(4808) [31-JAN-2011 13:14:33:843] nttcon: exit

(4808) [31-JAN-2011 13:14:33:843] nserror: entry

(4808) [31-JAN-2011 13:14:33:843] nserror: nsres: id=0, op=65, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

(4808) [31-JAN-2011 13:14:33:843] nsopen: unable to open transport

Connection string build with IPV4 IP address

(4808) [31-JAN-2011 13:14:33:843] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=111.22.22.333)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)(CID=(PROGRAM=C:\Oracle\sqlplus.exe)(HOST=MachineA)(USER=oracle))))

(4808) [31-JAN-2011 13:14:33:843] nttbnd2addr: entry

(4808) [31-JAN-2011 13:14:33:843] snlinGetAddrInfo: entry

(4808) [31-JAN-2011 13:14:33:843] snlinGetAddrInfo: exit

(4808) [31-JAN-2011 13:14:33:843] nttbnd2addr: using host IP address: 111.22.22.333

Successful to use the socket and find the host / server

(4808) [31-JAN-2011 13:14:33:843] nttcni: trying to connect to socket 2500.

(4808) [31-JAN-2011 13:14:33:843] ntt2err: entry

(4808) [31-JAN-2011 13:14:33:843] ntt2err: exit

(4808) [31-JAN-2011 13:14:33:843] ntctst: size of NTTEST list is 1 - not calling poll

(4808) [31-JAN-2011 13:14:33:843] sntseltst: Testing for WRITE on socket 2500

(4808) [31-JAN-2011 13:14:33:843] sntseltst: FOUND: write request on socket 2500

(4808) [31-JAN-2011 13:14:33:843] nttctl: entry

(4808) [31-JAN-2011 13:14:33:843] nttctl: Clearing non-blocking mode

(4808) [31-JAN-2011 13:14:33:843] snlinGetNameInfo: entry

(4808) [31-JAN-2011 13:14:33:843] snlinGetNameInfo: exit

(4808) [31-JAN-2011 13:14:33:843] nttcni: connected on ipaddr 111.22.22.333

SOLUTION

Change the bind order of TCPIP on the client within Windows 7, such that IPV4 is list first. Configuration of the adapter can be found under Network and Sharing Center, Change Adapter Settings. Then select your Adapter, Right hand mouse click and select properties.

or

Use IPV4 IP address in the alias

ORCL =

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)(HOST=111.22.22.333)(PORT=1521))

(CONNECT_DATA=

(SID=ORCL)

)

)