Home » RDBMS Server » Performance Tuning » Slow connection in Linux 64 bit oracle 9i (Oracle 9.2.0.6, Linux 64 bit)
Slow connection in Linux 64 bit oracle 9i [message #447353] Sat, 13 March 2010 21:08 Go to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
Recently, our production database is migrated from Windows 32 bit RAC environment to Linux 64 bit environment. After, this migration, we have noticed connection opening time has been increased significantly. Some time, it is taking up to 40 to 50 seconds with 20 concurrent connections. We are trying to open the connection in dedicated mode.

I have attached a image containing the configuration details of both the environments. /forum/fa/7572/0/
  • Attachment: 1.JPG
    (Size: 72.42KB, Downloaded 5894 times)

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447354 is a reply to message #447353] Sat, 13 March 2010 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Some time, it is taking up to 40 to 50 seconds

Where is time being spent?
Find & fix slowness.

Slow from all clients?

Slow for application but fast for sqlplus or simple JDBC thin client?

Is slowness network related or DB related?

Enable SQL*Net tracing.
Enable SQL_TRACE=TRUE

In case it might be relevant, DNS timeout is 30 seconds in some/many/most cases.

Are connections faster using IP# instead of hostname?

[Updated on: Sat, 13 March 2010 21:29]

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447357 is a reply to message #447354] Sat, 13 March 2010 22:01 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
We could reproduce this slow connection from all type of clients such as OLE DB, OCI driver, java thin client, and sqlplus. We have tested the connection time both with IP address and host name, and the result is same. tnsping and ping to the server is very fast. We have done tracerout and did not notice any problem with the network. This is a random problem and happens only with concurrent connections 10 and above. However, we have noticed there is a relationship between long connection time and CPU usage in the machine. During slow connection CPU usage spikes to 100% and R value of the machine crosses number of CPU. We are unsure whether this is a Linux bug or database bug. We are able to reproduce this problem with a simple java testing tool, which opens 10 concurrent connections simultaneously. These connections are immediately closed after launch.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447359 is a reply to message #447357] Sat, 13 March 2010 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Assuming what you posted reflects reality, then I'd would concentrate on OS and/or DB.

doing below during slowdown might capture clues (or not)

vmstat 3 20

sar data won't help since glitches are too transitory.

Relatively speaking opening a new DB session is resource intensive.

If application was OK prior to moving to new hardware,
then can rule out application root cause.

Assuming initSID.ora parameters are reasonably the same,
then DB not likely culprit.

Linux should be able to handle 10 new processes without breaking a sweat.

So, how to catch clues that points to root cause?

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447361 is a reply to message #447359] Sat, 13 March 2010 22:38 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
We have noticed query execution performance has been dramatically improved in the new environment as new environment has faster IO. We are puzzled why the connection is very slow. We are thinking of switching to shared mode. However, we are bit worried to implement shared connection as query execution time might get deteriorated with shared mode.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447363 is a reply to message #447361] Sat, 13 March 2010 22:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>We are thinking of switching to shared mode
I have not used SHARED mode since V7.3.4.5
It might help when system is memory starved(swapping).
I can assure you that batch job performance will degrade if they connect via SHARED.

The real question is "where is time being spent?" when it takes 30+ seconds to establish new session.

I suggest that you refrain from shooting in the dark by making random changes (READY, FIRE, AIM) method of troubleshooting.

If you do not have hard facts, just keep searching & taking measurements.

You have two challenges.
1) you can't predict when you need to be taking measurements.
2) you don't know which measurements are needed to identify the root cause

Bottlenecks typically occur in 1 of 4 areas
1) CPU
2) Disk I/O
3) RAM
4) Network.

Where/which is your bottleneck?

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447377 is a reply to message #447363] Sun, 14 March 2010 07:57 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
As I mentioned before, new system has faster IO and more memory compare to old system. We are very sure that there is no bottleneck in the IO, memory or in Network. However, we have noticed bottleneck in CPU. Connection slowness we are observing is in random in nature. With same load connection time varies from 600 milli-seconds to 50000 milli-seconds( 50 seconds).

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447378 is a reply to message #447377] Sun, 14 March 2010 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>However, we have noticed bottleneck in CPU.


Post hoc ergo propter hoc


Which comes first; new sessions or CPU bottleneck?

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447625 is a reply to message #447378] Tue, 16 March 2010 20:01 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
I have investigated this issue further. I have recorded both CPU usage and the R vale at every second over a period of 10 minutes. During this period, I was running my load test utility.
From the collected data, I found OS CPU is not a bottleneck. I have observed slow connection during normal R value.
I am suspecting some resource contention in the database level causing sporadic slow connection.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447641 is a reply to message #447625] Wed, 17 March 2010 00:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
naren.sarkar,

I am not saying you are right or wrong.
I am just asking for details.

>From the collected data, I found OS CPU is not a bottleneck. I have observed slow connection during normal R value.

Please clarify/post what you actually saw from which you made the conclusion.

>I am suspecting some resource contention in the database level causing sporadic slow connection.

Based upon exactly what is the report of "slow connection" made.

How can you capture hard data to support the report of "slow connection"?

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447726 is a reply to message #447641] Wed, 17 March 2010 10:10 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
I am attaching my collected data enclosed. I wrote a perl script to record the usage of R value at every seconds. I use the sar utility to record the CPU usage at every seconds. My tool lunch 20 concurrent connections at one go from 20 different threads. These twenty threads does not use any shared resources. Hence, you can rule out the possibility of any inter threads contention. Out of twenty threads one of the thread record the connection time. Collected data showed sporadic slowness of connection with normal R value. For example, in run 2, we have noticed 23.576 seconds of connection opening time. This connection time was recorded at 03/16/2010 17:21:19.00. If you see the CPU usage and R value of OS from (03/16/2010 17:21:19.00 - 23.576 seconds) onwards, you will not notice anything wrong.
I hope, I am able to explain.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447730 is a reply to message #447726] Wed, 17 March 2010 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I hope, I am able to explain.
I am impressed with Perl & methodology.

Now what you need to do is to enable SQL_TRACE via LOGON trigger in order to capture activity within DB for only your sessions.

Then using TKPROF to analyze trace files, compare & contrast the sessions which are fast to those that are slow.

Unless & until you really understand exactly what is "slow", you stand little change to applying the needed fix.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447761 is a reply to message #447730] Wed, 17 March 2010 14:45 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
I have captured sql traces both for the slow connection and the normal connection. I have not noticed any differences in this two files. Please see the sql trace for the slow connection attached.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447786 is a reply to message #447761] Wed, 17 March 2010 19:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Previous reports were that a "slow" connection took 20 - 50 seconds or longer to complete.

Trace completed in less than 1 second.

Trace does not confirm assertion concerning session is "slow".

Can't fix what is not broken.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447930 is a reply to message #447786] Thu, 18 March 2010 09:31 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
In fact, trace file is for slow connection which took 8 seconds to create the connection.
Trace file proves there is no post login slowness. Problem could be related with prelogin activities. I am an application developer and certainly not the DB expert. This is my guess. Can we anyway trace the pre-login activities?

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447936 is a reply to message #447930] Thu, 18 March 2010 10:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
So it your initial connection that is slow and activities after connection are just normal?
>>database is migrated from Windows 32 bit RAC environment to Linux 64 bit environment
Still in RAC?
Are you using proper tns entries for load balancing?
Check your sqlnet.ora.
If you find an entry like
SQLNET.AUTHENTICATION_SERVICES= (NTS)



change it to 
SQLNET.AUTHENTICATION_SERVICES= (NONE)

and try again. First try with simple sql*plus login and then with others.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447938 is a reply to message #447936] Thu, 18 March 2010 10:37 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
Yes, only the initial connection is slow. Query execution is very fast compare to Windows 32 bit RAC.
SQLNET.AUTHENTICATION_SERVICES= (NTS), This line has already been commented out.
We are not using any load balancing.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447940 is a reply to message #447938] Thu, 18 March 2010 10:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>This line has already been commented out
Where?
Client? Server? both?

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447942 is a reply to message #447940] Thu, 18 March 2010 10:53 Go to previous messageGo to next message
naren.sarkar
Messages: 16
Registered: April 2008
Junior Member
JDBC client does not use tnsname. Hi Mahesh, if you don't mind, could you please read the whole discussion that I had with BlackSwan.

Report message to a moderator

Re: Slow connection in Linux 64 bit oracle 9i [message #447944 is a reply to message #447930] Thu, 18 March 2010 11:25 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Problem could be related with prelogin activities.
This is much more difficult to resolve.
Is slowness at client?
Is slowness at DB server (prior to starting SQL_TRACE)?
is slowness due to intervening networking component between client & DB server?
You may need assistance from Network Engineer to do packet tracing & timing captures between client & DB server using Ethereal or similar tool.

Report message to a moderator

Previous Topic: 11g index can't be rebuild
Next Topic: Optimize the Query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 12 06:53:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.