Sybrand Bakker wrote:
> 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
Any chance of that template getting published?
Frank
Received on Wed Feb 05 2003 - 14:49:12 CST
