Oracle: OLEDB Resource(Session) Pooling
by Eric Ma EMa@ompus.jnj.com

Database connectivity is a prerequisite for today's dynamically generated web pages. However, connection to databases is one of the most expensive operations one does from within an ASP page, because of the overhead involved in database user authentication and allocation of database resources to handle user interaction with the database, among other things. All these can add significant latency to your web site. Based on the recent discussions in this list, it is apparent that everyone is keenly aware of this issue and would like to minimize the negative impact on performance by having to connect to databases. For a programmer with a client/server programming background, it is natural for the person to design a solution based on using a database connection that persists through an entire user session. However, for a web-based application, this is a big no-no (see the article at /advice/dbsessionapp.asp  for reasons why you should never do this).

The best practice for database connection (we limit our discussion to Oracle here) from an ASP application is to open the connection at late as you can, and close it as soon as you can, which means you open and close database connection on every ASP page. The encouraged practice is to use just one set of Oracle username/password for your entire application, not one set for each user as you may be used to do with client/server applications. Access control is no longer performed by the database, but by your application. You can save the ADO connection string in an application variable in the global.asa file. Some other alternatives include saving it in the windows Registry, or use an include file that has the ADO connection information, and include that file in the pages where database connection is required.

Opening a new database connection for each ASP page may not be as bad as you think, because from ADO 2.0 up you can utilize the "resource (session) pooling" feature offered by Microsoft's OLE DB Provider for Oracle. Resource pooling is similar to ODBC connection pooling, where a connection is returned to a pool instead of being destroyed immediately after it is closed and set to nothing in your code. See the following article for more details:
http://msdn.microsoft.com/library/techart/pooling2.htm.

The purpose of this FAQ is to demonstrate how to properly set the environment on your IIS/NT server to take advantage of OLE DB resource pooling. Unlike with ODBC, where you can enable pooling and set the timeout parameter from the 32-Bit ODBC utility in the Control Panel, with OLE DB you have to directly work on the Registry itself. Again, this is not as scary as it sounds. To do so, you first determine the version of OLE DB Provider you are using by running the script found at: http://www.learnasp.com/freebook/asp/connectioninfo.aspx, then dependent on the version you have, you may do the following steps:

· If you are using the OLE DB 2.0 provider you can do resource pooling but you cannot modify the default timeout configuration (60 seconds). To set up resource pooling, add the following subkey in the Registry:
HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLEDB_Services. Make sure you use the DWORD type, and enter a hexadecimal value of 0xffffffff.

However, I suggest that you don't use the 2.0 version of the Provider any more because of potential memory leaks. See the following KB article for more:
http://support.microsoft.com/support/kb/articles/q194/3/87.asp

· If you use the OLE DB 2.1 or 2.5 provider, in addition to doing the above step, you can configure the pooling timeout parameter by adding the following subkey:
HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\SPTimeout. Again this entry is of a DWORD type and you enter a decimal value in seconds. It is up to you to decide the number of second you want to set, depending on how busy your site is and how frequent the pages need to access Oracle data.

A KB article describing the above step can be found at:
http://support.microsoft.com/support/kb/articles/q237/9/77.asp

After you make the above changes, reboot your NT Server to make the Registry changes take effect. This is step is necessary.

Congratulations, now you are using the wonderful feature of OLE DB resource pooling! I hope from today on you will never ever think about caching connection objects in session variables anymore! Treat it as evil!

Some side notes and observations:

· With Oracle, in order to use resource pooling, you need to add the Registry subkey. Having only "OLE DB Services = -1" in your connection string is not enough.

· I came across the following KB article in MSDN saying OLE DB resource pooling is automatically enabled when you install MDAC 2.1 and consequently the 2.1 OLE DB Provider. This is true for MS SQL Server, but not true for Oracle. You have to physically enter the OLEDB_Services subkey in order to enable resource pooling.

See http://support.microsoft.com/support/kb/articles/Q240/1/66.asp  for the case of MS SQL Server.

· The resource pooling white paper above mentions that you need to leave at least one connection open per user session to utilize resource pooling. This seems to suggest that one should open a connection in session_onstart and don't close it and set it to nothing until session_onend. Personally, I don't think it is necessary and you should not do it. ADO takes care of creating the necessary initial connection and keep it in the pool, unlike in C++ where you have to do it yourself. One word of advise: explicit close every single one of Oracle connections and set it to NOTHING after you are done!

If you really want to see resource pooling in action, fire up the Oracle Instance Manager (part of Oracle Enterprise Manager), and watch the number of user sessions go up and down as connections are made and terminated, when ASP page requests are made. You will see the connections are reused before they are timed out, if you have resource pooling properly configured.

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.