Re: Oracle 8.1.7, OCI and opening/closing connection

On Tue, 04 Feb 2003 21:53:21 GMT, "Roberto Vespa" <wasp_at_inwind.it> wrote:

>- 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)

The broad picture

• You submit a sql-statement
• A hash value for the statement is determined
• if the hash value exists in the sql area in the shared pool a soft parse follows (which is basically re-evaluating)
• if the hash value doesn't exist, the statement will be hard-parsed (check the existence of the tables, check privileges, and so and so on) and get stored in the sql area
• 'Old' statements are aged out the cache using a LRU (Least Recently Used) algorithm
• For a 'hit' the statement must be *exactly* identical with an already existing statement, without variation (no different spacing etc)
• Hence select * from emp where empno = 10 select * from emp where empno = 20 are 2 *different* statements
• However if you have the statement select * from emp where empno = :b1 where :b1 is a bind variable, you simply need to change the bind variable, re-open the cursor and that is it
• Developers assuming Oracle is just Sqlserver sold by a different vendor are the *worst NIGHTMARE* of all experienced DBAs, because we always end -up writing longish reports to demonstrate the Sqlserver crap is NEVER going to work against Oracle, and the only solution is to throw lots of extra hardware at the problem app.
• Consequently, you are suffering from false assumptions and on the road to HELL
• You are strongly urged to READ the first 3 chapters of Thomas Kyte's Expert One-on-One Oracle book, they are almost fully devoted to the differences between Oracle and the toy whose name shallst further remain unmentioned.

In a properly designed app, after a while, the cache will hold all possible variants of all statements, so it will never thrash the shared pool. However in an app designed for sqlserver, just because of the hardcoded literals, the situation will only get worse. Setting cursor_sharing = force which should be considered as trying to kill a wasp with an elephant, doesn't seem to work in all situations. (Yes, I just delivered a report on yet another Sqlserver beastie running under Oracle, and guess what is happening. I will turn the report into a template, the basic outline is the same everywhere)

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Feb 04 2003 - 16:31:18 CST