Re: Connection delays causing problems.

Ed Prochak schrieb:
> The problem: ORACLE connections sometimes take tens of seconds to come
> up. This is true whether the connection is via sqlplus, ACCELL
> application program, or thru JAVA web application program.
>
> Environment:
> Clients and servers running LINUX 2.4.21
> Over 150 clients running ACCELL applications.
> A similar number connect via the web
>
> ORACLE Clients:
> SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jul 17 13:34:00 2006
>
> Web clients come thru JBOSS
> Release ID: JBoss [WonderLand] 3.2.3 (build: CVSTag=JBoss_3_2_3
> date=200311301445)
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> The server database has just been upgraded to release 10.2 from 10.1
> but from what we can tell, this problem also occurred on connecting to
> the 10.1 release.
>
> The delay happens at connection time so no SQL query is involved. I can
> tune SQL, but I have no idea what oracle settings might help or hurt
> the login time. The way our ACCELL applications are written, they
> make many short connections to our centralized Oracle DB. Typical
> queries are simple lookups in reference tables. Most of the
> application data is held in a local Database at the sites. (I just
> found out that tnsping also shows the delay.) Our DBA is doing a search
> in ORACLE, but I thought I'd try to get some insights from this group.
>
> So any suggestions of where to look or any outright solutions are
> welcome.
>
> Thanks,
> Ed
>
> here's a sample ping:
> tnsping MYID
>
> TNS Ping Utility for Linux: Version 10.1.0.2.0 - Production on
> 17-JUL-2006 13:56:34
>
> Copyright (c) 1997, 2003, Oracle. All rights reserved.
>
> Used parameter files:
> /home/oracle/product/10.1.0/client_1/network/admin/sqlnet.ora
>
>
> Used TNSNAMES adapter to resolve the alias
> Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
> (PROTOCOL = TCP)(HOST = hostname.COM)(PORT = 1521))) (CONNECT_DATA =
> (SID = myid)))
> OK (9570 msec)
>
> Ten seconds is way too long.
>

The most common causes of such delays, which i know - are the DNS issues. You should check your nsswitch.conf, resolv.conf and hosts files. Maybe , however , you don't see any obvious misconfiguration - then you should probably trace. An excellent document how to read Net 8 trace file is the Note 156485.1

Best regards

Maxim Received on Mon Jul 17 2006 - 13:23:55 CDT