Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle 8.1.7, OCI and opening/closing connection

Re: Oracle 8.1.7, OCI and opening/closing connection

From: Roberto Vespa <wasp_at_inwind.it>
Date: Tue, 04 Feb 2003 23:00:31 GMT
Message-ID: <jCX%9.132290$0v.3758354@news1.tin.it>


Ok, thanks a lot again.
Just 2 things:

Anyway, thanks again for great help, really. But keep "sqlserver beastie report" for the next one, ok? :oP

Wasp

> 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 - 17:00:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US