Re: Oracle 8.1.7, OCI and opening/closing connection

Thanks a lot Sybrand :o)
I admit my ignorance, the matter is that I'm not supposed to know this level of detail due to the complexity of app, I have to take care of too many details and resources are low... I don't bother u with these details, but if u could give me some more answers... I ask, hoping you have time to help me :o)

• if I open connection when app starts, and close when exiting, chances are that situation would improve? even if my sql stamements are built using OCI binding, and in every transactions data are different? I have to use sql statements on the fly, and not procedures, because of requirements to support Oracle andf SQL server, and requirements to support different versions of the same database, and so on...
• if yes (situation would improve), I suppose shared pool will be "thrashed" anyway, even if far less, I hope... for what I understand from what you said, every application that must use shared pool with sql statements on the fly sooner or later would trash shared pool... is that true? if yes, what should be done to avoid troubles? and if no, why?? :o)
• changing a little my scenario, what about a web application? should I mantain connection open between pages? does this scale?

Thanks a lot again :o)

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 - 15:53:21 CST