Re: Oracle 8.1.7, OCI and opening/closing connection

On Tue, 04 Feb 2003 19:47:37 GMT, "Roberto Vespa" <wasp_at_inwind.it> wrote:

>Hi all.
>I'm developing a multi-db application, supporting Oracle and SQL Server
>2000. This requisites made me build a general data layer which allows
>business logic to switch transparently between db servers, using ADO with
>SQL Server and OCI with Oracle (through a COM DLL we built). Now, due to an
>architectural choice which is not the point of this email, I had to develop
>business logic functions that are opening and closing db connection for
>every little transactional block of instructions. Probably this will change,
>but anyway, doing a lot of open/close I observed server loosing memory
>resources, eventually causing ORA-04031 and problems related. Oracle is
>8.1.7.0 on Windows 2000 Advanced Server. I observed on our test server that
>more or less 50.000 of open/close connection couples caused resources
>exhaustion. Obviously hardware charecteristic of server could change this
>number, but this is not the point for me. I would just like to know if this
>server behaviour is by design because such a big number of open/close block
>is not a recommended dev pattern, or if it is a bug solved in earlier Oracle
>versions, or if there are server setting that can change this behaviour.
>Thanks in advance, bye.
>
>Wasp
>

The behavior you observe is to be expected. You are thrashing/fragmenting the shared pool, because a) you are sending sql-statements on the fly, which always need to stored in the shared pool and subsequently parsed b) once you disconnect the statements will be invalidated unless being used by a different session, which is probably not very likely in your case.

You could try increasing the shared pool, but most likely this will only make your problem less frequent, because your application design stinks all over the place.

Time to learn Oracle I guess, and turn back from the road to hell.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Feb 04 2003 - 14:22:56 CST