Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ODBC and Oracle Connection Pooling

Re: ODBC and Oracle Connection Pooling

From: Justin Cave <jocave_at_yahoo.com>
Date: 27 May 2003 17:57:59 -0700
Message-ID: <233b7a65.0305271657.3f1f950d@posting.google.com>


danl_thompson_at_hotmail.com (Dan Thompson) wrote in message news:<806b620f.0305240532.47ffb496_at_posting.google.com>...
> jocave_at_yahoo.com (Justin Cave) wrote in message news:<233b7a65.0305231329.4711edc_at_posting.google.com>...
> > danl_thompson_at_hotmail.com (Dan Thompson) wrote in message news:<806b620f.0305230739.1b0fe86b_at_posting.google.com>...
> >
> > The retry wait time has nothing to do with connection pooling. It's a
> > parameter that has to do with TAF (Transparent Application Failover).

Correction, I thought by "ODBC Setup" you meant the DSN setup where there is an option for the retry interval for TAF. Seems you were referring to the "Connection Pooling" tab of the ODBC Data Source Administrator. My apologies.

> > If you want to enable ODBC connection pooling, you need to go into the
> > ODBC Data Source Administrator, go to the "Connection Pooling" tab,
> > and then enable connection pooling for the Oracle ODBC driver.
> >
>
> Yes, well, the connection pooling tab SAYS it is enabled (by default)
> for every driver, including the Oracle driver. Yet, as far as I can
> tell, no ODBC connection pooling is taking place. PerfMon shows no
> pooled connections, the SQL queries are extremely slow, and the TNS
> listener on the Oracle box shows new connections. So this is still
> very confusing. Code sample in original posting.

By default, connection pooling should be disabled-- you have to explicitly enable it. I'm not able to reproduce your problem on my machine once I enable connection pooling.

Since ODBC connection pooling is handled by the ODBC Driver Manager, not by the drivers, you might want to check that your version of the MDAC is up to date. You could download an updated driver from OTN as well, but I doubt this would help.

This is probably a dumb question, but may I assume that when you're doing your testing
- you're waiting for the first page to close its connection before the second page gets its connection
- fewer than 2 minutes pass between the time the first page closes its connection and the second page opens its connection.

Justin Cave

>
> Thanks, Dan
> >
> >
> > > 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 Tue May 27 2003 - 19:57:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US