Applications with performance issues

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
kcsimmons72
Posts: 3
Joined: Sun 07 Apr 2013 17:20

Applications with performance issues

Post by kcsimmons72 » Sun 07 Apr 2013 18:00

We have an Oracle 11G database and are using Devart dotConnect v5.7 for 2 of our applications (A and B) calling the db. There are 6 other applications that use the same db, but are not having any performance issues.

By mid-day, users for applications A and B begin to experience performance degradation. We are not seeing any utilization spikes, network problems, or deadlock anywhere. There does seem to be some blocking behavior when applications A and B are requesting new connections to the db.

Have you seen anything like this before?

We have not set the connection string with any parameters, but are using all default settings.

Applications A and B are deployed to different application servers so the requests are originating form different places.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Applications with performance issues

Post by Pinturiccio » Mon 08 Apr 2013 15:08

Perhaps, some connection is not closed in your application, and in time the number of connections in the pool reaches the MaxPoolSize value (that equals 100 by default).
Try using dbMonitor to get the number connections at the moment when the issue occurs. For more information For more information, please refer to http://www.devart.com/dotconnect/oracle ... nitor.html
kcsimmons72 wrote:There are 6 other applications that use the same db, but are not having any performance issues.
Do these 6 applications use dotConnect for Oracle too, or they use some other provider?

kcsimmons72
Posts: 3
Joined: Sun 07 Apr 2013 17:20

Re: Applications with performance issues

Post by kcsimmons72 » Tue 09 Apr 2013 01:23

Thanks for your response.

We do have monitoring for all of our servers now in production and we have seen the connections get up to 21 while experiencing the degradation. For the most part, there are no more than 1 or 2 open connections at a time during normal operations. We have never been anywhere near 100 open connections from any particular client.

The other applications utilize the ADO.Net provider for Oracle and are not experiencing any issues with performance.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Applications with performance issues

Post by Pinturiccio » Wed 10 Apr 2013 12:29

Can you find out the reason of the number of connections in the pool growing to 21 using dbMonitor, or debug the process of your application during performance decrease?

The reason may be the fact that Oracle server has session and process limit. The value of the maximum number of sessions and processes acceptable in your Oracle server can be found using the following SQL query:

Code: Select all

SELECT name, value 
  FROM v$parameter
 WHERE name = 'sessions';

SELECT name, value 
  FROM v$parameter
 WHERE name = 'processes';
When you discover performance decrease again, you can check the current number of sessions and processes and compare this value with the maximum:

Code: Select all

-- the number of sessions
SELECT COUNT(*) FROM V$SESSION;

-- the number of processes
SELECT COUNT(*) FROM V$PROCESS;
If possible, create and send us a small test project with the corresponding DDL/DML scripts and describe the steps for reproducing the issue.

kcsimmons72
Posts: 3
Joined: Sun 07 Apr 2013 17:20

Re: Applications with performance issues

Post by kcsimmons72 » Wed 17 Apr 2013 14:33

Thanks again for the suggestions. We have moved onto another aspect of this ongoing issue:

Working with Oracle, we have noted a steady increase in the number of records appearing in the Oracle sys.reg$ table that is at least partially attributable to the applications using the Devart provider. Based on their recommendation, we deleted the records in that table and noted that for 2 days there were no performance problems observed.

However, the number of records in that table is once again growing and we are concerned that we will again hit a threshold on the DB and the performance problems will return.

In order to attempt to trace the connectivity between our application servers and the DB, we attempted both an Oracle client trace and an ODBC trace. For the Devart applications, these traces showed no logging at all.

So, I have 2 questions:

1. Have you heard of this behavior of Devart with relation to the Oracle sys.reg$ table?

2. How is Devart managing its network connection to the DB and how best can we trace that connection?

Thanks for your help!

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Applications with performance issues

Post by Pinturiccio » Fri 19 Apr 2013 14:46

Could you please notify us whether you use Advanced Queuing Technology in your application? For more information about the Advanced Queuing Technology, please refer to http://www.devart.com/dotconnect/oracle/docs/?AQ.html

Post Reply