ODP.NET – “Pooling” and “Connection request timed out”


Oracle - Pooling - smallRecently, I had to look at an issue in our code regarding “Connection request timed out”. Our Oracle connections are set to use the Pooling service offered by ODP.NET. Oracle documentation as the following description:

“The Max Pool Size attribute of the ConnectionString property sets the maximum number of connections for a connection pool. If a new connection is requested, but no connections are available and the limit for Max Pool Size has been reached, then the connection pooling service waits for the time defined by the Connection Timeout attribute. If the Connection Timeout time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the connection pool request has timed-out.” [Ref-01, p3-6]

This post is about enabling the tools (traces and performance counters) that will help you to monitor and trace Oracle DB pooling activities in your applications. Also, I am providing some information about the way Oracle client connection pooling works from my observations. In the end, this helped me to track down the “leak” in my application.

We are using on the client side the Oracle Data Provider for .NET (aka ODP.NET) for Oracle DB 11g and our server is using Oracle DB 10g.

The “Connection pool performance counters” is a new feature of  the ODP.NET for Oracle DB 11g.

Enabling ODP.NET Traces

The first thing I tried was to enable Oracle ODP.NET traces. However, the way oracle ODP.NET was installed on the machine was by “copy deployment”, so the registry entries were not set.

Oracle documentation (see [Ref-01] for the doc) provides the required information, i.e.:

  1. ODP.NET tracing features are controlled by registry values under the entry key “HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\<version>“.
    1. <version> is the version number of your “Oracle.DataAccess.dll”.
    2. This DLL should found under “<ORACLE_HOME>\odp.net\bin\2.x”
      1. “2.x” for .NET 2.0 applications (and also 3.0 and 3.5)
      2. “1.x” for .NET 1.1 applications
    3. Mine is version “2.112.2.0″, so my entry key is “HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.112.2.0″.
  2. There are 3 keys that really matters for Tracing (all ‘sz’ entries, i.e. string values):
    1. “TraceLevel”
      • Traces is enabled depending on the value of key.
      • Here are the possible values:
      • Value Description
        0 None
        1 Entry, exit, and SQL statement information
        2 Connection Pooling statistics
        4 Distributed transactions (enlistment and delistment)
        8 User-mode dump creation upon unmanaged exception
        16 HA Event Information
        32 Load Balancing Information
        64 Self Tuning Information
        127 All the above
        Default: 0
      • ODP.NET does bit-wise checking on the value, so you can enable multiple logs by adding their values.
  3. “TraceFileName” (string value)
    • A valid path and filename for the log. E.g. “C:\temp\oracle.txt”
    • TraceFileName specifies the filename that is to be used for logging trace information. If TraceOption is set to 0, the name is used as is. However, if TraceOption is 1, the Thread ID is appended to the filename provided.
  4. “TraceOption” (string value)
    • TraceOption specifies whether to log trace information in single or multiple files for each Thread ID. If a single trace file is specified, the filename specified in TraceFileName is used. If the multiple trace files option is requested, a Thread ID is appended to the filename provided to create a trace file for each thread.
      • 0 = Single trace file
      • 1 = Multiple trace files

For pooling info, I used “Traceslevel=2″. However, this was not enough and too verbose. So I turned to “Perfomance counters”.

32/64-bit – Specific details

Be careful regarding whether your application runs as a 32-bit or 64-bit image, as the registry entry key is not the same:

  • In a 32-bit OS, all applications will run as a 32-bit image and the registry key in that case is “HKEY_LOCAL_MACHINE\Software\Oracle”.
  • In a 64-bit OS, applications might run either as a 32-bit image or 64-bit image.
    • If it runs as 32-bit image, then the entry key in the registry is “HKEY_LOCAL_MACHINE\Software\WOW6432node\Oracle”.
    • If it runs as 64-bit image, then the entry key in the registry is “HKEY_LOCAL_MACHINE\Software\Oracle”.

Enabling ODP.NET Performance Counters

As for the “enabling Traces”, because Oracle was not installed in the machine by a deployment tool, I had to configure the performance manually.

Registering the Performance counters

In order to find what performance counters ODP.NET was instantiating, I used Reflector on “Oracle.DataAccess.dll” and found these counters:

Name Type Description
HardConnectsPerSecond RateOfCountsPerSecond64 Number of sessions being established with the Oracle Database Server every second
HardDisconnectsPerSecond RateOfCountsPerSecond64 Number of sessions being severed with the Oracle Database Server every second
SoftConnectsPerSecond RateOfCountsPerSecond64 Number of active connections originating from connection pools every second
SoftDisconnectsPerSecond RateOfCountsPerSecond64 Number of active connections going back to the connection pool every second
NumberOfActiveConnectionPools NumberOfItems64 Total number of active connection pools
NumberOfActiveConnections NumberOfItems64 Total number of connection in use
NumberOfFreeConnections NumberOfItems64 Total number of connections available for use in all the connection pools
NumberOfInactiveConnectionPools NumberOfItems64 Number of inactive connection pools that may be disposed soon
NumberOfNonPooledConnections NumberOfItems64 Number of non-pooled active connections
NumberOfPooledConnections NumberOfItems64 Number of pooled active connections
NumberOfReclaimedConnections NumberOfItems64 Number of connections which were garbage collected implicitly
NumberOfStasisConnections NumberOfItems64 Number of connections that will be soon available in the pool. User has closed these connections but they are currently awaiting certain actions like transaction completion before they can be placed back into the pool as free connections.

Those counters are “multi-instance”, aka different applications running at the same time have each their set of counters. Furthermore, ODP.NET creates a set of counters per Application domain for .NET processes (See [Ref-03] for further details about “single-instance” vs. “multi-instance” of performance counters).

I did a little C# application to create those counters using the class “System.Diagnostics.PerformanceCounterCategory” to create (or delete) a caterogy of  performance counter (here, ODP.NET uses “” as its category of counters, so I used the same).

Here is a sample of code to create the category of performance counters:


// Create the collection for the counters

CounterCreationDataCollection counterDataCollection = new CounterCreationDataCollection();
 // Add counters
 CounterCreationData rateCount64 = new CounterCreationData();
 rateCount64.CounterType = PerformanceCounterType.RateOfCountsPerSecond64;
 rateCount64.CounterName = "HardDisconnectsPerSecond";
 rateCount64.CounterHelp = "Number of sessions being severed with the Oracle Database Server every second";
 counterDataCollection.Add(rateCount64);

...

// Create and Register the category.
 string categoryName = OracleCategoryConst;
 string categoryHelp = @"Oracle Data Provider for .NET counters";

PerformanceCounterCategory.Create(categoryName, categoryHelp, PerformanceCounterCategoryType.MultiInstance, counterDataCollection);

To remove it, you can use this piece of code:


PerformanceCounterCategory.Delete(categoryName);

Enabling PerfCounters for ODP.NET

Once the performance counters are created and registered in the performance system, you need to tell ODP.NET to start creating and updating the counters. To achieve that, you need to add another string key in the registry called “PerformanceCounters” under “HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\<version>” (see section “Enabling ODP.NET Traces”).

The possible values are:

Value Description
0 Not Enabled
1 Number of sessions being established with Oracle Database every second.
2 Number of sessions being severed from Oracle Database every second.
4 Number of active connections originating from connection pools every second.
8 Number of active connections going back to the connection pool every second.
16 Total number of active connections.
32 Number of inactive connection pools.
64 Total number of connections in use.
128 Total number of connections available for use in all the connection pools.
256 Number of pooled active connections.
1024 Number of non-pooled active connections.
2048 Number of connections that will be soon available in the pool. User has closed these connections, but they are currently awaiting actions, such transaction completion, before they can be placed back into the pool as free connections.
4095 All the above
Default: 0

Launching PerfMon.exe

Launch “Perfmon.exe” from a command line window. You should be able to see the Oracle ODP.NET performance counters called “Oracle Data Provider for .NET” when trying to add them to the monitoring screen:

Be careful to not confuse it with “.NET Provider for Oracle” which is the category of performance counters registered and exposed by Microsoft .NET Provider.

In screenshot below I have added both categories of counters:

  • The top one is from Microsoft (in fact from Visual Studio “Server Explorer” view that I used to connect to Oracle databases for development).
  • The bottom one is from ODP.NET (from a test application).

The naming convention for the instance of perfomance counters for ODP.NET is as follow:

<AppDomain name>[<ProcessID>, <AppDomain Index>]

So, we can see that in my test application there are 2 AppDomains, one called “ad #2” and the other one “testoraclepoolapp2.exe” both from the process (ProcessID: 5736). Each application pool has it owns set of pools (2 for “ad #2” and 1 for “testoraclepoolapp2.exe“) and connections.

* Before launching your application using ODP.NET, you should have the performance counters created and registered as well as enabling them via the registry entry “” (see previous sections). If your application was already running, you must restart it (this is because ODP.NET will create its counters when the application (to be more precise the AppDomain) is launched and uses Oracle Db connection the first time).

32-bit processes on 64-bit OS machines

On 64-bit OS machines, when you launch “Perfmon.exe” it will execute the 64-bit version which monitors 64bit counter applications. If like me you need to launch “Perfmon.exe” 32-bit to monitor 32 bit counter applications, then you can do it using this command: “mmc /32 perfmon.msc”  (thanks to Tony Rogerson’s post [Ref-02]).

Connection Pool (by ODP.NET)

Now that I have got access to traces and performance counters I can monitor my application more closely and see what is going on in the world of Oracle Db connections within my application.

However, to be able to read the information about ODP.NET performance counters you need first to understand how “connection pooling” works.

From Wikipedia

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

The key – Connection string

How does oracle identifies you need a connection from the pool or not?

It is from the connection string. The connection for Oracle ODP.NET has about 22 parameters that can be set. Usually, you will pass at least 3 of them: “Data Source“, “User ID” and “Password“. All the others will be set with their default values (see [Ref-01]). The parameter that switch ON or OFF the pooling service for the the connection is named “Pooling“. By default, it is set to ‘True‘. So , if do not specify a value of ‘False‘, then your connections are coming from a pool.

As long as you used the same connection string (order does not matter, as ODP.NET internally will parse your connection string to rebuild a ordered one), then the Pooling service of ODP.NET can retrieve a connection from the pool.

There 7 parameters in the connection string that control the behavior of the Pooling service: “Connection Lifetime“, “Connection Timeout“, “Decr Pool Size“, “HA Events“, “Incr Pool Size“, “Load Balancing“, “Max Pool Size“, “Min Pool Size“, “Pooling“, “Validate Connection“. I am going to explained some of them but not all (for “HA Events” and “Load Balancing” see [Ref-01, p3-5] for further details]).

Life in the Pool and outside it

When you ask ODP.NET to open a connection [<OracleConnection>.Open()] and “Pooling” is enabled, then the pooling service will search for a pool that matches your connection string:

  • If none exists (i.e. first time this connection was used in your application), then it will create a pool and initialize it with the number of <OracleConnection> defined by “Min Pool Size” (default: 1). This number is the lower limit of the pool, i.e. there will always be at least “Min Pool Size” connections in the pool. Only when the pool gets discarded/released the number of connection will go below this value.
  •  If the pool already exists, it will check whether there is a connection available.
    • If there is one available, the connection is taken out of the pool and pass it back to the application.
      • If “Validate Connection” is enabled, then a round-trip to the database will be done to ‘validate’ the connection.
    • Else (there is none available) it will check whether the pool has reached its upper limit of connections defined by “Max Pool Size” (default: 100).
      • If ( NbConnectionsInPool == “Max Pool Size“), then it will wait for a period of time defined by “Connection Timeout” to see whether a connection gets back into the pool, so it can be reused.
        • If (at least) one connection got back into the pool, then  the connection is taken out of the pool and pass it back to the application.
        • Otherwise, it will raise the (in)famous “Connection request timed out” exception (hence this blog entry).
      • Else it will create new instance(s) of <OracleConnection> defined by “Incr Pool Size(default: 5).  One of the created connection is taken out of the pool and passed back to the application.

Once your application has finished to use the <OracleConnection>, then it must call Close()/Dispose(). The pooling service checks whether the lifetime of connection has exceeded the value defined by “Connection Lifetime” (default: 0 – i.e. no checks).:

  • If the lifetime of the connection exceeds this property value, the connection is closed – i.e. the physical network connection between your process and the machine hosting the database is closed.
  • The lifetime of a connection is only checked and enforced by the polling service when a connection is put back into the pool.
  • However, Connections that have exceeded their lifetimes are not closed  if doing so brings the number of connections in the pool below the “Min Pool Size“.

The connection pooling service closes connections when they are not used – connections are closed every 3 minutes. The pooling service is allowed to close connection instances every 3 minutes up to the limit defined by the parameter “Decr Pool Size” (default: 1) of the connection string.

Also you have to remember that ODP.NET is for .NET applications. In .NET, the excution boundary is defined at the Application Domain level (aka AppDomain). ODP.NET manages the pools per AppDomain. This means that an application using a connection string “A” used in AppDomain #1 and the same connection string used in AppDomain#2 (within the same process) will end up with one pool created in AppDomain#1 and another in AppDomain #2.

Putting the information in Picture

Here is a sample picture that show you the relation ship between ODP.NET performance counters and the various connection objects in a fictive .NET:

Connection pools and Performance countersI hope this information can help you. As for me, we identified the issue in our application: leak overtime of our connection objects – i.e. not being disposed/closed properly.

[Ref-01] Oracle ODP.NET, web documentation

[Ref-02] “Missing PERFMON counters using 64 bit Windows -> MMC /32 PERFMON.MSC”, Tony Rogerson’s blog

[Ref-03] BCL team Blog, PerformanceCounters: single instance, multi-instance and WMI [David Gutierrez]

[Ref-04] Connecting to Oracle Database, web documentation

About these ads
Leave a comment

16 Comments

  1. Thanks Joao for this great enlightning post. Can you elaborate more on your leaked connections and how you solved your issue.

    Regards
    Jorge Cossio

    Reply
    • Thank you Jorge.

      In my case, there were a few scenarios which raised an exception and prevented the Close() (or Dispose()) on the connection to be called. Thus, the connection was not returning to the pool. By using the performance counters I could monitor the connections counter which was going up.

      Let me know, if you need more information.

      Regards,
      Joao

      Reply
  2. Anas

     /  February 16, 2012

    HI Joao,

    this is really nice posts and clearing many doubts and provide lot of useful information.

    I would like to point out on one point

    Enable traces

    Mine is version “2.102.2.20″, so my entry key is “HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.112.2.0″.

    your version and registry version is different. it might be typo.

    Actually we are using 2.102.2.20 of ODP.net and I want to set performance counter for my application but i was not able to do same.

    After detail investigation, i found that performance counter is only available in 2.112.2.0.
    Please correct me if i am wrong.

    i would like to clarify one more doubt that we are using oracle 10g database but ODP.net 2.112.2.0 is created for oracle 11g. If we start using odp.net 2.112.2.0 with oracle 10g then it will work or it will create any problem.

    Awaiting your valuable inputs.

    Regards,
    Anas

    Reply
    • Hi Anas,

      Thank you for your comments and thank you for pointing out my typo mistake – I have updated the blog entry.

      Also, I have added a couple of information (top of the blog entry) that relates to your questions:

      1) Yes, “Connection pool performance counters” is a new feature of ODP.NET for Oracle DB 11g.
      2) Yes, you can use on the client side the ODP.NET for Oracle Db 11g to talk to a server using Oracle DB 10g. In fact, this was my case when I wrote this blog entry.

      For further details about the client and server side on Oracle Db products you can have a look at the following note “726240.1 Oracle Data Provider for .NET (ODP) Supported Configurations”. You can access it via a link on this page, but you will need to have a user account with Oracle web site (registration is free).

      Regards,
      Joao

      Reply
  3. Anas

     /  February 22, 2012

    Hi Joao,
    Thanks for your answer. Actually we already logged our fault call with them and we are following up with them. There are providing many useful articles and information in order to fine tune connection pooling configuration.

    Still I am unable to configure trace for ODP.net. Let me know if you have any inputs for the same.

    It is nice move to mention software versions and configuration at top so whoever anyone look it will give clear idea for targeted audience. I will also make note of it while writing my blog or article.

    Regards,
    Anas

    Reply
    • Hi Anas,

      No, sorry I did not experienced any errors whilst setting up the ODP.NET performance counters – at least no more than the 32 and 64 bit issues that I explained as well in this blog entry.
      However, if you find out why, let’s know. Thanks in advance.

      Good luck,
      Joao

      Reply
  4. What is the value of OracleCategoryConst in example? If it’s empty I’m getting error “Invalid category name. Its length must be in the range between ’1′ and ’80′. Double quotes, control characters and leading or trailing spaces are not allowed.”

    Reply
    • Hi Nikolay,

      My mistake – I put parts of the code and forgot to put the constants use in them.

      The value of that constant should be the name of Oracle performance counter’s category name, i.e. “Oracle Data Provider for .NET”. (see this link on Oracle web site).

      Regards,
      Joao

      Reply
  5. Pratik

     /  October 21, 2013

    Hi, As per your blog I was able to capture the performance counters for ODP .Net but based on that I am not sure how can I identify if the issue is in my application. It would help if you can share your thoughts about it and also how exactly you identified a connection leak in your application.

    Thanks,
    Pratik

    Reply
    • Hello Pratik,

      The way I was able to found the connection leak in my application was by measuring and monitoring the number of connections that would remain opened for a period of time and having that number growing and almost never going down. Then it is just the case to find the activity in your application triggering that leak (e.g. using the logs of your application).

      I hope this help.

      Joao

      Reply
      • Pratik Thakker

         /  November 11, 2013

        Hi Joao,

        Thanks for your reply. My application is behaving somewhat differently in the sense that I am getting the connection request timeout error after some amount on inactivity on the application. I tried simulating heavy load on my application to try and replicate this error but from the performance counters I saw that the # of soft connects and disconnects were quite high and almost equal in number based on which I conclude that the connections are picked up from the pool and returned to the pool as expected.

        Would you have any idea on what could be happening here?

        Regards,
        Pratik

  1. Database connection pooling with multi-threaded service

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 67 other followers

%d bloggers like this: