Re: Oracle 8.1.7, OCI and opening/closing connection

Hi Mr Bakker,

I'm a wasp colleague and I was fair interested in your thread. So, we works normally with a lot of different database system and normally we found misterious problem only using Oracle. We are a lot interested in how a server manage the memory allocation, in
the way it use for defragment the memory, cause we are server appllication developer too. The big deal is that when we study that kind of problem, we are
debugging our server, not Oracle. Workin on SQL Server, the poor server as you said, we never mind about the server internal, we semple call queryes and execute store procedures. Maybe sometimes our job is not optimized, but we never expect that the server crash, or stop working due to the fact we open
too frequently a connection !! This is simple ridicolous ! I suggest you to study SQL Server as well, and became a 'Senior SQL Server Dba', start study Microsoft Access today ! From what I read, I feel Access a more robust platform than the Oracle one :P.

Thanks for your suggestions,
Best Regards

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<13f04v456f56tkp22vrvgp6g4hpo9omj4k_at_4ax.com>...
> 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 Wed Feb 05 2003 - 03:48:35 CST