No question at this time
DBA Top 10
1 M. Cadot 11200
2 A. Kavsek 6000
2 B. Vroman 6000
4 P. Wisse 3400
5 J. Schnackenberg 3200
6 J. Péran 2000
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
8 D. Dave 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48559
Total active users1398
Act. users last 24h3
Act. users last hour0
Registered user hits last week55
Registered user hits last month810
Go up

Connection Management Call Elapsed Time in AWR Report quite high
Next thread: oracle database Migrate from AIX database 11.2.0.2 to linux 11.2.0.4
Prev thread: oracle 11.2.0.4 on linux 8

Message Score Author Date
Hi everyone, I hope everyone is fine and hea...... Joël Péran Aug 24, 2020, 11:19
Hi Joël, You have server=dedicated architecture...... Philip Wisse Aug 24, 2020, 17:05
Hi Philip, Yes, server is dedicated but the...... Joël Péran Aug 25, 2020, 08:02
Hi everyone, So we finally had the culprit :...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Joël Péran Aug 27, 2020, 11:43
Hi Joël, yes, it sounds familiar :). In our dat...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Sep 03, 2020, 15:02
Hi Ales, I just noticed that I was thinking ...... Joël Péran Sep 03, 2020, 15:37

Follow up by mail Click here


Subject: Connection Management Call Elapsed Time in AWR Report quite high
Author: Joël Péran, France
Date: Aug 24, 2020, 11:19, 92 days ago
Os info: Windows 2016
Oracle info: 12.1.0.2
Message: Hi everyone,

I hope everyone is fine and healthy, because this is the most important thing actually.
I am dealing with a strange case. Users are complaining that some device (wifi tablets, web devices ...) are quite long to connect to the database, or are disconnected or are in timeout. Nothing has really changed on the architecture. And sometimes too, the response time (about 4s which is long for some tasks) has increased as well.
Looking at awr reports, I noticed that the database is having a very high "connection management elapsed time" event percentage. It is just dealing with connection/deconnection on the database. There is not a lot of interesting documents dealing with that on MOS, well I mean that they do not refer to what I am facing. We have moved the database to another node (cluster architecture), and of course the database took time cleaning the connections but the problem occurred again.
I think the problem is from the application side : a bad way to connect to the database, not enough disconnection ou disposal of connections, a short pool of connections declared as the customer level or a too short timeout for connections that can be defined.
I am writing this post to know if anyone has faced that "connection management call .." feature, if someone has other ideas about that and so on.
The antivirus has been disabled from the oracle side, there is no firewall as well.
Thanks in advance for all your replies.

BR,
Joel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Connection Management Call Elapsed Time in AWR Report quite high
Author: Philip Wisse, Netherlands
Date: Aug 24, 2020, 17:05, 92 days ago
Message: Hi Joël,

You have server=dedicated architecture?

Are there any logon/logoff triggers?

Network test tools are ping and tnsping.
For instance, you can start tnsping every minute and save the last line in a logfile.

Stay save! Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Connection Management Call Elapsed Time in AWR Report quite high
Author: Joël Péran, France
Date: Aug 25, 2020, 08:02, 91 days ago
Message: Hi Philip,

Yes, server is dedicated but there are no logon/logoff triggers. Programmers of applications assure me that they do logon/logoff and use a connection pool. They are dealing with the oracle managed dll (oracle data access) in order to better manager pools and transactions. Usually, access points were used instead of IP adresses in TNSNAMES.ORA so I asked them to use IP instead of querying multiple dns. It seems a bit better but not perfect.
Colleagues from the network team have used some tools to gather information about where the time is spent when an embedded terminal is querying the database. Nothing really noticeable appeared. We are still investigating :)

Thanks alot for you reply,
Will do a feedback if we get into something interesting.

Regards,
Joël
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Connection Management Call Elapsed Time in AWR Report quite high
Author: Joël Péran, France
Date: Aug 27, 2020, 11:43, 89 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi everyone,

So we finally had the culprit :) ...
Well, we have still a lot of connections/deconnections to deal with but. There were latencies on embedded terminal due to a windows DLL ((Dns.GetHostEntry Method )
Reference :
https://docs.microsoft.com/fr-fr/dotnet/api/system.net.dns.gethostentry?view=netcore-3.1
) who was first querying the dns via IP6 address (and then IP4), but IP6 was not activated (or has been deactivated once ...) on the application servers.
Once it was reactivated on theses servers, the 5 seconds latency observed vanished. So Oracle is not always the culprit :)

Applications are mostly in C# or .Net with PHP and Javascript. To connect to Oracle Databases, they are using the oracle managed dll and this dll is using Windows dll as well. This was the reason why the windows DLL was involved.

Best Regards,
Joël
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Connection Management Call Elapsed Time in AWR Report quite high
Author: Ales Kavsek, Slovenia
Date: Sep 03, 2020, 15:02, 82 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Joël,

yes, it sounds familiar :). In our data center (running on IPv4) we sometimes disabled IPv6 on some Windows server, because we thought we could not do any harm anyway.
We were wrong (and MS confirmed that we should leave IPv6 stack active on all servers in AD), so nowadays we turn off ipv6 on Linux servers and leave it on Windows servers.
From security point of the view we would rather see IPv6 turned off until all IPv4 network migrates to ipv6 some day...the day bots takes a complete control of IT operations :-).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Connection Management Call Elapsed Time in AWR Report quite high
Author: Joël Péran, France
Date: Sep 03, 2020, 15:37, 82 days ago
Message: Hi Ales,

I just noticed that I was thinking "ipV6" and "ipV4" but I wrote "ip6" :) Though I can assure you that even after reading my post, I was still thinking IPV6 :)
Bots would control IT operations one day ? ;) Where did you read that ? ;-)
Have a great weekend my friend, and take care (I hope everything is ok for you regarding this COVID thing ...)

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here