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:23:28 +0400
Message-ID: <aekkc1$454$1@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:23:28 CDT

Original text of this message

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