How can I get an Oracle connection pool shared across all applications/virtual directories/versions in my IIS instance?

Question

How can I get an Oracle connection pool shared across all applications/virtual directories/versions in my IIS instance?

Answer 1

Connection pools are managed by the .NET Framework and are unique per connection string. Therefore, if you are using the exact same connection string (including the user identity if you are using Windows Integrated authentication) it will share the connection pool.

If you run Performance Monitor you can add the counter '.NET Data Provider for Oracle' and choose the process (E.g. w3wp.exe) to monitor the connection pool statistics.

If you have multiple virtual directories (sub-webs) they can share the same application pool (w3wp.exe process.) However, based upon a test harness I wrote and a little further investigation I see that each sub-web has it's own app domain within the same w3wp.exe worker process. This is visible when looking at the performance counter for the w3wp process.

Therefore, multiple sub-webs sharing the exact same connection string will create their own connection pool.

Therefore, one approach would be to have your data service layer hosted under a separate process which is shared amongst multiple sub-webs. One such approach would be to use a .NET serviced component (C# class inheriting from ServicedComponent and installed under COM+.)

I created a test harness and proved that this indeed will work and share the same connection pool from various sub-webs.

However, this approach provides a very low level of isolation and therefore makes your entire website vulnerable (single point of failure.)

If you are worried about the amount of resources that are being used by having multiple connection pools, you can always control this within your connection string. For example, set the maximum pool size for each individual application.