White space counts as different.
I did have a developer that didn't believe that bind variables made a
difference. The software went into production. Customers complained
because when this batch import job kicked off it would bring the system to
its knees and no one else could do anything. Back in the shop we
benchmarked the section of code (the developer who didn't believe us that he
needed bind variables) and ona 1,000 record import it took 26 minutes and
pegged the CPU on the server. (no one else on the server, each imported
record generated a lot of queries) Then we had the developer spend the 2
hours (45 minutes to compile) to put in the bind variables. Now the same
benchmark on the same machines under the same conditions took just under 6
minutes and the CPU usage would peak at 10% and averaged 2 - 3 % for the 6
minute run. So it was worth it.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
news:MPG.18ac7402a50365a198968a_at_news.la.sbcglobal.net...
> This morning I was explaining to a developer why bind variables are a
> good thing. That led to a discussion of how Oracle first parses any SQL
> to produce a hash, which it uses to determine if it had seen that
> particular SQL before.
>
> I said that different white space caused a different hash and thus, a
> new hard parse. Then I got to thinking. It was a very long time ago that
> I attended an Oracle Education class (employers don't seem to be as
> willing to pay for education these days). But when I did attend, the
> instructor said that these two statements would cause a different hash:
>
> 1. select * from emp;
> 2. select *
> from emp;
>
> The only difference is the newline after the '*' in the second example.
> Is it still true that these are considered "different" statements? Or
> does Oracle now eliminate extra whitespace prior to calculating the
> hash?
>
> Just curious. And while I'm at it, let me thank all the regulars who
> post to the c.d.o NGs. I learn an awful lot just lurking. I'd be afraid
> to list your names ... someone would complain because I listed another
> poster before them ... and in reality, I've learned so much from each
> and every one of you. Thanks again.
>
> --
> /Karsten
Received on Thu Feb 06 2003 - 20:21:06 CST
