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

Home -> Community -> Usenet -> c.d.o.server -> Re: newbie : BIND VARIABLES

Re: newbie : BIND VARIABLES

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 17 Jun 2002 16:25:14 +0400
Message-ID: <aekkfc$45m$1@babylon.agtel.net>


Oops, sorry. CURSOR_SHARING setting for autobinding is FORCE, not EXACT (which turns the feature off).

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:aekkc1$454$1_at_babylon.agtel.net...

> You can use bind variables anywhere. PL/SQL actually converts all local
> variables to bind variables in your static SQL automatically. With SQL,
> your host application has to supply actual values for bind variables if
> you use them.
>
> NOT using bind variables where appropriate (and it's almost everywhere)
> is a major performance and scalability inhibitor. Without them, all your
> SQL is unique to Oracle, and each statement is hard-parsed and is kept
> in the library cache as new unique SQL, which quickly fills up the SGA with
> unique non-shareable statements and introduces library cache latch
> contention among other bad things. There are cases when you would want
> not to use bind variables (to allow CBO to see actual values so that it can
> come up with optimal plan for your particular data distribution), but these
> are actually rare.
>
> Tom Kyte's book (Expert one-on-one: Oracle) lists a method for detecting
> SQL statements with literals, which allows to spot such statements quickly
> and reliably.
>
> In 8i and later, there is CURSOR_SHARING parameter, which automatically
> replaces literals with bind variables when set to EXACT (or SIMILAR in 9i),
> but it has a number of bugs and caveats associated with it, so it's not a
> silver bullet, though it is particularly useful with dynamic SQL. You may want
> to look at it, but I would discourage setting it at instance level and only set
> it to EXACT in session when it is really needed. Ideally, you should explicitly
> use bind variables where they are appropriate and not rely on some database
> setting to do it for you automatically.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
>
> "cava123" <cava123_at_noos.fr> wrote in message news:3D0D1F4C.C4333D4_at_noos.fr...
> > Jim Kennedy a écrit :
> >
> > > Bind variables are place holders in DML (eg select .. from mytable where
> > > field1:=hvField1)
> >
> > >>Only in PL/SQL code ? Which V$ vues give the information if the code use bind
> > variables or not ? (EXPLAIN PLAN can help to detect if variables are bound or
> > not -what does HASH JOIN mean- ?
> >
> > >
> > > Without bind variables you are forcing Oracle to serielize on a latch and
> > > you are causing earch and every statement to be reparsed.(high CPU) It will
> > > mean each sql statement will take longer. Once the statement is parced, is
> > > you use bind variables, then it does not need to be reparsed and the
> > > execution plan does not need to be redone.
> > > Jim
> > > "cava123" <cava123_at_noos.fr> wrote in message
> > > news:3D0CB6CC.B3A0C34_at_noos.fr...
> > > > Hi all,
> > > > We have to use "bind variables" : What does bind variables means exactly
> > > > ? relation ship with performance ? and how I can see if it used or not ?
> > > > (V$SQLAREA,V$SQL,V$SESSION, ...)
> > > > I have also execute an ALTER SESSION SET AUTOTRACE TRACEONLY EXPLAN PLAN
> > > > for .... and get 'HASH JOIN' : What does it mean HASH JOIN and HASH
> > > > TABLES ?
> > > > Thanks
> > > >
> > > >
> >
>
Received on Mon Jun 17 2002 - 07:25:14 CDT

Original text of this message

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