sqlplus - It hangs up during exit

[Mahima Kaushik_2]

Hi,
Has any one seen this? Login to sqlplus is slow (2 - 3 minutes) but OK. But once enter exit, it hangs for at least 10 minutes. We are noticing this issue since few months now. It is causing several problems since some users have scripts running where after exit they are waiting to get to next step. We have HP-UX 10.20 and 11i.
Need help.
THX

[Steve Steel]

Hi

Without a lot more info on revisions . difficult

Try

http://www.oracletuning.com/


Steve Steel

[Julio Yamawaki]

Hi,

need more information:
1. Where is the database running
2. Where is sqlplus running
3. What kind of sqls are you running is this session

[Mahima Kaushik_2]

Here is what user sees when logging in:

PL/SQL Release 2.3.2.3.0 - Production
HP10.20hostname:/path/home/userid 3 sqlplus usrid/passwd@databasename

SQL*Plus: Release 3.3.2.0.0 - Production on Mon Mar 22 10:35:41 2004

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.



Connected to:
Oracle7 Server Release 7.3.2.3.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.2.3.0 - Production

SQL>
SQL> exit
Disconnected from Oracle7 Server Release 7.3.2.3.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.2.3.0 - Production



It used to work fine before.

[Mahima Kaushik_2]

Database is running in a HP-UX 10.20 box. SQLPLUS is in the same box as well.

[Julio Yamawaki]

Hi,

Let's try to isolate the problem.
Can you make the same connection in a SQL for Windows to see if the same occurs?

[Mahima Kaushik_2]

Unfortunately this can not be run on Windows.
It's unix mounted only.

[Julio Yamawaki]

Don't you have another UNIX box?
This is for testing purpose only.
I need to know if you have problem with your UNIX or if it's in your database.
Also, verify if you have some difference in calling sqlplus user/password (without using databasename).

[Hein van den Heuvel]

> Login to sqlplus is slow (2 - 3 minutes) but OK

Whoah. That is NOT ok. That is totally unacceptable. sub-second it good, 2-3 seconds is annoying but acceptable, anything over 5 seconds means there is a serious problem on the box! Ivestigate! Isolate! What is it doing all that time? Paging its brains outs? Figure out, like tan other reply suggest, whether it is the client, the server or the combo. What is the path taken? Local connectt? tcp? ipc? Oracle MTS making you wait for a slot? Lot's more details needed for answers, but don't bother with the exit prolem untill you understand the entry problem, because there is a serious problem there.

fwiw,
Hein.

[James A. Donovan]

Since SQL*Plus and your database are located on the same box, please try the following:

set ORACLE_SID=

$ sqlplus userid/passwd

Leave off the "@databasename". This should determine if the problem is with the listener/network or with the database.


BTW....have you considered upgrading your version of Oracle? The version you're on has been desupported for a few years now.

[Jean-Luc Oudart]

Well, both database and user on same box.
Why not try
export ORACLE_SID=
sqlplus user/passwword

what the response time for tnsping ?

Regards,
Jean-Luc

[Mahima Kaushik_2]

On Oracle server itself, login and logout is fast. It's clients that we are experiencing the problem with.
Also we had made some changes to our /etc/inetd.conf file, arround the time since we are having this problem, to accomodate the security scan requirements. I am not sure if any of these changes might have caused this.
Following services have been commented out from inetd.conf:

bootpd
chargen
daytime
discard
echo
finger
echo
finger
ident
kerberose services (including klogin and kshell)
ntalk
portmapper
rpc.cmsd
rusers
time
tftp
walld
who


THX

[Jean-Luc Oudart]

How do you resolve IP@ ?

if you change hostname with IP@in tnsnames.ora , what is the response time ?

Regards,
Jean-Luc

[Mahima Kaushik_2]

Tried

sqlplus userid/passwd

This did not make any difference.

Also we use DNS server to resolve our hostname and IP. That doesn't seem any issue. Pinging and nslookup are positive for the server and clients both.
THX for the responses.

[Julio Yamawaki]

Hi,

let's try one more time.
What about your UNIX box, is it performing "normally"?
Do you know when this performance issue started? Do you have other applications running in this database? Are these applications running with a "normal" performance?
Did you check the database to see if it has some contention?

[Sanjay Kumar Suri]

Can you post your init.ora?

Are you using shared server connection or dedicated connection?

sks

[Jean-Luc Oudart]

Mahima,

in a previous post you wrote :
"On Oracle server itself, login and logout is fast"

and now : "Tried
sqlplus userid/passwd
This did not make any difference.
"

Did I miss anything ?

Also, Are you using dedicated server (as opposed to MTS), Have you tried to create another listener , different port number, new entry in tnsnames and see the results.

What about tnsping (you haven't said anything about it) ?

Jean-Luc

[Mahima Kaushik_2]

Unix box is performing good other then some space issue like some database File system 100% full. Itâ s a 9000/829/K400 Box.


Jean,
I tried â sqlplus userid/passwdâ on client.

Also what do you mean by tnsping, I am not sure. Can you explain it to me little more

[Michael Schulte zur Sur]

Hi,

can you see any errors in errorlog?
If this is a network problem, you could activate tracing.

greetings,

Michael

[Michael Schulte zur Sur]

Hi,

I meant alertlog and you could modify sqlnet.ora for tracing.

Michael

[Mahima Kaushik_2]

sqlnet.ora has following entry only:

AUTOMATIC_IPC=on

unable to locate init.ora

[Jean-Luc Oudart]

the init.ora refers to the init.ora file

default location is $ORACLE_HOME/dbs

Regards,
Jean-Luc

[Jean-Luc Oudart]

sqlnet.ora
if you don't use oracle names I would suggest you put
NAMES.DIRECTORY_PATH= (TNSNAMES)
in the sqlnet.ora

AUTOMATIC_IPC = OFF <-- interprocess communication variable for local connection requires an entry in tnsnames.ora

For more information on this you can check document id 69725.1 on Oracle Metalink site.

Regards,
Jean-Luc