Re: sqlplus scott/tiger@tnsname hangs

From: Dan Norris <dannorris_at_dannorris.com>
Date: Sat, 19 Apr 2008 00:14:47 -0500
Message-ID: <48097FC7.6050604@dannorris.com>




  
  


Careful--I wouldn't enable trace until you know you can reproduce the
issue. Too much logging will not only affect performance, but if it
isn't managed, it can create a denial of service if a filesystem fills
or the listener is too busy logging to service requests.

I'll be interested in your results if/when you do get to dig deeper into diagnosis.

Dan

Prasad wrote:
Thanks Dan/Krish .

I was not able to get any valuable info so far . For time being I have suggested to enable the listener logging with trace_level_client=16 and wait for the next occurrance. I heard that they have a similar issue before 2 months . so hopefully I will be able to catch  it the next time by following both of your suggestion..

Thanks
-Prasad

On Fri, Apr 18, 2008 at 4:26 PM, Dan Norris <dannorris@dannorris.com> wrote:
If restarting the listener "fixed" the problem, then I'd have to believe that your issue was either related to networking issues on the DB server, the listener process somehow became unresponsive, or your dispatcher(s) became unresponsive. The only way to investigate would be to reproduce the issue with SQL*Net tracing on (trace_level_client=16) and see what's in the trace.

It might also be interesting to know how many connection requests the listener is servicing. I suppose it's possible that the tcp queuedepth is too small to handle all the requests if there some some sort of a login storm or something. Without listener logging enabled, you'd only be able to check v$sysstat (where name = 'logons cumulative') periodically to see how many logins are occurring. Statspack should also be gathering that information, so consult there for historical purposes.

Dan


Prasad wrote:

On 17/04/2008, Prasad <p4cldba@gmail.com> wrote:

Greetings.

This was something that happened today in one of the database and I am looking for list's
response on this. This is a Oracle 9.2.0.7 database running solaris 9.

Around 10am the Unix group called me and told me that one of the osprocess is taking more cputime
then what it normally takes. and upon investigation I found that this database was running on
shared server mode with the  osprocess being the only dispatcher at that time . So I created another
3 dispatcher and stopped the existing dispatcher d000.

and everything was going fine until 2.30pm when the user called me and told me that he is not able to
make connection  using servicename. so I did a test

test1- sqlplus '/as sysdba'
test2- sqlplus scott/tiger 
test3- sqlplus scott/tiger@tnsname


I was able to logon to database successfully with test1 and test2 . However when I did test3
it just hangs and it didnt allow me to interrupt it also.

I checked if the dispatchers were busy or not  and didnt found anything significant.
The listener logging was not enabled so the initial reaction was to shutdown and start the
listener which I did and at this time the test3 worked fine . However the user was still not able to
access its application and I observed a large number of session in dba_blocker and dba_waiter
and also few deadlock error in alert log file . So I killed the session on the beginning of the queue
and it immediately releases all the sessions in dba_blockers and dba_waiters and the user was
able to access the application.

Here comes the hard part what is the root cause of this issue?

Please advise.

Thanks
-Prasad



-- http://www.freelists.org/webpage/oracle-l Received on Sat Apr 19 2008 - 00:14:47 CDT

Original text of this message