ODBC and Oracle Connection Pooling

From: Dan Thompson <danl_thompson_at_hotmail.com>
Date: 23 May 2003 08:39:55 -0700
Message-ID: <806b620f.0305230739.1b0fe86b@posting.google.com>

My problem is that I neither prove nor disprove that connection pooling is working. Although the evidence points to it not pooling. I'm running out of things to try and I have a very unhappy customer.

I have an IIS application running on a Windows 2000 box. It uses the Oracle ODBC driver to connect to an Oracle 8.1.7 server. The connection pooling tab in the ODBC setup has a retry wait time of 120. Perfmon is enabled. Oracle ODBC driver version on says it is "Oracle in ORACLE_iSetup Version 9.02.00.00"

The ASP code gets a connection, does the query and closes the connection each time. As shown here:

  dim cn
  dim rs
  dim cnString
  cnString = "Provider=msdaora;" _
& "Data Source=ORATEST;" _
& UserID=ORATEST;" _
& Password=ORATEST;"

Set cn = Server.CreateObject("ADODB.Connection")

  cn.ConnectionTimeout = 15
  cn.CommandTimeout = 30
  cn.Open   cnString

Set rs = cn.Execute("SELECT * FROM BRAND")

  rs.MoveFirst
  Do Until rs.EOF
    'yadda yadda
     rs.MoveNext
  Loop

Set rs = Nothing

cn.Close
Set cn = Nothing

PerfMon always shows 0 pooled ODBC connections. In fact all of the ODBC perfmon data shows 0.

The TNS Listener log on the Oracle box shows that a new connection is being created each time this code runs.

The IIS code runs very slowly, as if it were getting a new connection every time.

Any help you can provide would be... well... helpful.

Best Regards,
Dan Thompson Received on Fri May 23 2003 - 10:39:55 CDT