SQLPLUS connection slow to database
I have Oracle 10.2.0.4 on AIX 5.3
I'm told that there's general slowness on the machine this morning. Machine itself isn't under any particular load but connection to the database is super slow.
Trying to connect via sqlplus on the database server:
sqlplus username - doing local connection - 45 seconds
sqlplus username@alias - using TNS - 3 minutes
sqlplus / as sysdba - 90 seconds
tnsping alias - taking 45000 msec to 48000 msec to respond.
I'm quite baffled. I was wondering if it was maybe the listener, but since a local connection without using the listener is also taking an extended period of time, I don't think that is necessarily relevant.
I have checked - there are no logon triggers.
Doesn't appear to be any auditing going on.
Any ideas?
I'm told that there's general slowness on the machine this morning. Machine itself isn't under any particular load but connection to the database is super slow.
Trying to connect via sqlplus on the database server:
sqlplus username - doing local connection - 45 seconds
sqlplus username@alias - using TNS - 3 minutes
sqlplus / as sysdba - 90 seconds
tnsping alias - taking 45000 msec to 48000 msec to respond.
I'm quite baffled. I was wondering if it was maybe the listener, but since a local connection without using the listener is also taking an extended period of time, I don't think that is necessarily relevant.
I have checked - there are no logon triggers.
Doesn't appear to be any auditing going on.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
slightwv: You may have hit the nail on the head. From typing "ping hostname" to first ping ... about 45 seconds. Once they pings start coming, they come regularly at 0ms (since it is the local host).
ststuber: Top processes - nothing out of the ordinary, they keep changing: perl, java, assorted application process.
Certainly sounding like a network issue, I wonder if they screwed something up in routing last night.
Off to talk to network people, thanks!
ststuber: Top processes - nothing out of the ordinary, they keep changing: perl, java, assorted application process.
Certainly sounding like a network issue, I wonder if they screwed something up in routing last night.
Off to talk to network people, thanks!
Does sound like routing to me.
Might try a traceroute to help support things before you run off yelling at network folk.
Might try a traceroute to help support things before you run off yelling at network folk.
ASKER
Funny, I was thinking the same thing.
Traceroute takes about 45-50 seconds to come back and then responds with 1 hop to itself for 0ms.
Sending something off to the network folks now.
(Because networking is the tool of the devil and is a necessary evil thing that exists between me and the database :) )
EDIT: looks like it might be a DNS issue...
traceroute and ping return immediately if I use the IP address.
Using the hostname causes the problem.
Thanks!
Traceroute takes about 45-50 seconds to come back and then responds with 1 hop to itself for 0ms.
Sending something off to the network folks now.
(Because networking is the tool of the devil and is a necessary evil thing that exists between me and the database :) )
EDIT: looks like it might be a DNS issue...
traceroute and ping return immediately if I use the IP address.
Using the hostname causes the problem.
Thanks!
Is there some HUGE upload/download filling up the pipe?
Sort of a self-inflicted DOS attack?
Sort of a self-inflicted DOS attack?
>>EDIT: looks like it might be a DNS issue...
I always use IP address in my Oracle config files.
I remember many years ago a 'feature' where Oracle would grab the first network card it found in the list when resolving hostname.
The problem? There wasn't a cable plugged into that network card... ALL client connections timed out because Oracle was trying to send packets back out the wrong card.
I always use IP address in my Oracle config files.
I remember many years ago a 'feature' where Oracle would grab the first network card it found in the list when resolving hostname.
The problem? There wasn't a cable plugged into that network card... ALL client connections timed out because Oracle was trying to send packets back out the wrong card.
ASKER
It can be a funny double edged sword.
I've been on the back end of a complete re-IP of the internal network once.
TNSNAMES files out in the wilderness with IP address instead of domain name resolution stopped working.
They eventually got around it by pushing a new "Master" tnsnames file out via global policy, but it's one of those things that can bite you either way...
I've been on the back end of a complete re-IP of the internal network once.
TNSNAMES files out in the wilderness with IP address instead of domain name resolution stopped working.
They eventually got around it by pushing a new "Master" tnsnames file out via global policy, but it's one of those things that can bite you either way...
>>It can be a funny double edged sword.
Yes, yes it can...
Yes, yes it can...
we use ldap instead of trying to keep everyone's tnsnames in order.
I use ldap at home just to keep my various XE instances straight. Much simpler.
I use ldap at home just to keep my various XE instances straight. Much simpler.
once you're in is performance normal?