SQLPLUS connection slow to database

I have Oracle 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?
LVL 23
Steve WalesSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Assume you did a normal ping?

Using the HOST entry from the tnsnames.ora, ping that.

If that is slow, call the network folks...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
what are the top processes at the OS level?

once you're in is performance normal?
Steve WalesSenior Database AdministratorAuthor Commented:
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!
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

slightwv (䄆 Netminder) Commented:
Does sound like routing to me.

Might try a traceroute to help support things before you run off yelling at network folk.
Steve WalesSenior Database AdministratorAuthor Commented:
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.

slightwv (䄆 Netminder) Commented:
Is there some HUGE upload/download filling up the pipe?

Sort of a self-inflicted DOS attack?
slightwv (䄆 Netminder) Commented:
>>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.
Steve WalesSenior Database AdministratorAuthor Commented:
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...
slightwv (䄆 Netminder) Commented:
>>It can be a funny double edged sword.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.