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: Question about using bind variables in static sql

Re: Question about using bind variables in static sql

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 16 Mar 2002 21:32:06 -0600
Message-ID: <uelij1z34.fsf@rcn.com>


On 14 Mar 2002, rkg100_at_erols.com wrote:

> I read the SQLPlus book on the market a few weeks ago. Its the same
> thing as the documentation. Tough to retain everything though.
>
> I know how bind variables improve performance in dynamic sql... do
> they improve performance if they are being used in SQL plus or just
> running a sql query?

Yes. But you won't notice it. When a large number of processes are all running the same query,

        select x from y where id = n

instead of

        select x from y where id = :n

then, Oracle will have to parse each one of those, as well as put them in the shared pool, ie getting rid of other sql that might be shared by incoming processes ...

This is when the system can start slowing down and the non-parsing can cause performance issues.

For a single user/developer typing sqlplus commands, you won't notice the performance hit. But, if you notice Thomas Kyte's answers, he uses them in his single queries as well. Probably just so he always stays in the habit. I'm not so disciplined, and not even as close to as talented.

Get his book. It really explains this well, and in many different places in the book.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Received on Sat Mar 16 2002 - 21:32:06 CST

Original text of this message

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