Re: Connection delays causing problems.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 17 Jul 2006 11:48:13 -0700
Message-ID: <1153162093.148819.119600@75g2000cwc.googlegroups.com>

Maxim Demenko wrote:
> 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

If he is running into DNS problems, he should be able to use Ethereal on his Linux boxes to see where the packets are going, and what the responses to those packets are.

In dedicated server mode, the listener on Linux/Unix tries to spawn an additional process when a client connects - this may account for some of the delay. Did you adjust the kernel memory parameters? What does the following show at a prompt:
cat /etc/sysctl.conf

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 17 2006 - 13:48:13 CDT