Recently, 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:

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:

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:

I 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