Re: OLE DB Connection Pooling
- From: "Norman Dunbar" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <oracledba.williams@xxxxxxxxx>
- Date: Thu, 03 Nov 2005 10:43:14 +0000
Morning Dennis,
>> We have a vendor application that is making a connection to Oracle
>> within a loop. Really stupid, but unfortunately we can't change
that
>> immeditately.
Yes, been there, saw the results, hated it !
My app (third party) was a VB using ADO (no bind variables either).
There were connections and disconnections for everything. If a VB form
had a dozen fields on it requiring data from the database, there was a
connect, query, disconnect for each and every field on the form !
We had to go over to MTS to handle this because after a while, the
whole database just hung. I'm told that the following applies, but I
can't confirm. The server was Windows 2000 by the way, Oracle 9203 at
the time.
... when a connection gets a port to use from the listener, that port
is used for the duration of the session and then disconnects.
... on disconnect, that port cannot be reused by the listener until
some time has passed. I don't know if this delay is configurable at the
OS level, or even at the Oracle level.
... with the number of connect/disconnects we were having, we very
quickly ran out of ports that could be used.
The solution was to use MTS in the meantime and fix the application in
the longer term. When my contract expired at the location, MTS was still
in use and the application only (!) connected once per form. As you can
imagine, I had a lot of fun trying to convince the developers of that
application that it was, politely, a big pile of poo, and that it had to
be sorted out to connect once, do all the required work (even if that
means staying connected all day) then logout at going home time. They
were still havinig difficulties with that concept when I left.
Don't ask about the lack of bind variables, I was told that that was
because ADO didn't allow them :o) It took me about 3 days to (a) learn
VB and ADO and knock up a small demo using binds for them. They hadn't
fixed that when I left either.
However, I digress.
<SNIP>
>> Someone here suggested we try Oracle MTS to cope with this problem,
>> but it seems to me that is on the wrong end of the connection and
>> wouldn't make connections occur faster. But I haven't used MTS.
In the short term it will probably help, in the longer term, get the
developers to fix the app or 'run away very quickly' from it !
Good luck.
Cheers,
Norman.
Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.
Internal : 7 28 2051
External : 0113 231 2051
Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should
still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under
the Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may
also be accessed by someone other than the sender or recipient, for business
purposes.
If we have sent you information and you wish to use it please read our terms
and conditions which you can get by calling us on 08708 506 506. Find out more
about the Environment Agency at www.environment-agency.gov.uk
--
//www.freelists.org/webpage/oracle-l
Other related posts:
