tnsping very slow

[Ray Salter]

Hi,
I've got an Oracle 8.1.6 DB on a V2500, which is connected to by the app on another V2500 across a dedicated Gigabit crossover cable, on lan6. The DB server has 2 other Oracle DBs being connected to on the lan0 interface.
The problem I have is that the tnsping from the app server to the DB instance across the crossover cable is wildly varying, and more often than not very slow. (the worst I've seen today was an 86000 msec response!!)
However all normal lines of enquiry as to the performance problem have thrown up blanks - both servers are lightly loaded, the Gigabit lan interface is working fine (we can transfer data across v. quickly, and ping is an instant 0ms /1ms response) etc. I'm beginning to think the problem is at the tcp layer, perhaps some sort of ndd parameter (timeout, retry etc.) The other DBs that are connected to from the main 10/100 lan0 interface all have reasonable tnsping times, but the one at the end of the Gigabit is not!?
Any ideas would be appreciated.
thanks

[JI HUI]

1. how about response of telnet to the DB server?
2. how the DB instance is configured, I mean, are you using IP address? dns name? or host name in your oracle listener configuration file?
3. Have you try to tnsping from the db server itself?
4. how about ftp download test?

just suggestions. good luck.

[Hein van den Heuvel]

Most likely this will be a network name lookup problem. As asked in the other reply, are you using system names or tcp address numbers in tnsnames.ora and listener.ora?
Change to numbers only for testing!
traceroute?

You did give that db:lan-6 X-over app connection a dedicated subnet right? 10.0.x.n? You might as well use /etc/hosts entries for the names on that subnet as only 2 systems will ever be able to use it.

Check rlogin / telnet speed from app to db and also from db to app. Check the name resolution order through sam, or check resolv.conf. Is it using /etc/hosts? dns?

hth,

Hein.

[Ray Salter]

1) telnet to the DB server is fine.
2) we have the hostname in the tnsnames.ora file, but as the IP address is resolved from /etc/hosts I doubt this is a delay.
3) tnsping from teh server itself is just as bad, as it goes in on the same listener on the same port. Am I right in thinking that this removes a finger of suspicion from the physical lan connection itself?
4)I haven't done an ftp download test, but theres quite a bit of other network activity on all the lans which don't get this issue.

Thanks for the suggestions. As a bit of further info, we did a tusc on the tnsping command, and there appear to be 2 main culprits for the delay, in that sometimes they sit in a [sleeping] state - a connect, and a read. I've attached the output from one of the tuscs. Like I said, sometimes its OK, with a <1000msec response, and sometimes we watch it [sleeping] for several seconds.

[MRSG]

Can anyone help me with why this connect and read might be sleeping? It seems to get worse when there is a large amount of traffic trying to contact the oracle tns listener - is there a tcp issue here?

socket(AF_INET, SOCK_STREAM, 0).....= 3
connect(3, 0x7f7f3084, 16).........[sleeping]
connect(3, 0x7f7f3084, 16)..........= 0
...

...
read(3, 0x400d8f26, 2064)..........[sleeping]
read(3, "\0J \0\004\0\0\0" \0\0> ( D E S ".., 2064)....= 0

[Aaron MacLennan]

Have you tried using the raw ip address of you dbase server in the tnsnames.ora and listener.ora? This may help decide whether you are having a name lookup problem or not. Also, are you using the protocol.ora file. This may be causing lookups to validate the host you are accessing from. Just some thoughts...

[T G Manikandan]

I would also want you to check $ORACLE_HOME/network/admin/sqlnet.ora file.

make sure that there is no nameserver defined with Oracle.

REvert

[MRSG]

In response to the last two suggestions, the lookup for the DB server hostname is done locally; as this is a private LAN, only the app server's /etc/hosts file is aware of this 2nd IP address on the DB server, so the lookup part is OK.

A correlation has been highlighted between the number of reports the application is generating and the tnsping increasing. I guess a modification of my question would then be: what could cause a skyhigh tnsping from a lightly loaded system, across a lan cable (when looking under glance, anyway)that is also lightly loaded?
ps apologies for any confusion caused by changing login name halfway through a call!

[T G Manikandan]

i am not talking about the OS level lookup.I am talking about the Oracle level database lookup which is done using sqlnet.ora

Just a suggestion.

[T G Manikandan]

check the attached metalink note.There are lot of reasons,

[MRSG]

many thanks for that document, that will keep me busy for a while!!
I will assign points when I have a chance next week.

[janhanel]

Probably full tns log file.  Shrink it and response time will be acceptable again.