Xref: alice comp.databases.oracle.misc:46572 comp.databases.oracle.server:73483 Path: alice!news-feed.fnsi.net!netnews.com!feed1.news.rcn.net!rcn!dispose.news.demon.net!demon!news.demon.co.uk!demon!jlcomp.demon.co.uk!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc Subject: Re: There is NOT value in using BOUND variables!!! ??? Date: Thu, 11 Nov 1999 10:12:57 -0000 Message-ID: <942315282.18291.0.nnrp-02.9e984b29@news.demon.co.uk> References: <80cqfc$igb$1@nnrp1.deja.com> X-Trace: news.demon.co.uk 942315282 nnrp-02:18291 NO-IDENT jlcomp.demon.co.uk:158.152.75.41 X-Complaints-To: abuse@demon.net X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Lines: 81 It's always possibly to circumvent Oracle's best guesses at optimisation heading out into extreme cases. The argument for bind variables applies to high-use OLTP types of system with frequently-repeated copies of the 'same' SQL statement. Under these circumstances the pressure on the library and shared pool latches becomes extreme if literals are used. By the same token if you had a statement that was being processed with extreme frequency and you KNEW the optimum access path for it, then you would code it with a hint to bypass the limitations imposed on the optimiser by bind variables. Your example chooses an unrealistic statement in an inappropriate environment to emphasise a particular drawback to using bind variables - it should not be extrapolated to a generalised condemnation of bind variables. Specifically - the case with 255 bind variables would almost certainly have persuaded the CBO to use a full tablescan, whereas the equivalent query with only 7 different literal values was probably executed as an iterated or concatenated index access. I would agree with your scepticism, though about the phrase: 'using bound variables where possible'; as with all things to do with Oracle there are special cases that need proper consideration. BTW - are you really using 8.0.1, and are you really using Raid level 3, or where these just a couple of typos ? -- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Deja User wrote in message <80cqfc$igb$1@nnrp1.deja.com>... >we are using Oracle 8.01 on NT4/SP5, 512MB RAM, Dual Pentium Pro 200, >RAID array level 3. > >I was intrigued by the suggestions made in Oracle Performance Tuning >about using bound variables where possible. I always had the hunch that >bound variable queries actually run slower but I decided to test it out >in a more disciplined fashion. > >I ran following query 100 times, each time criteria coming from a >random number generator so it was guanrenteed to be unique in my case. >Select x, y, z from ABC where field in (set of 6 values). > >And then I ran following query >select x, y , z from ABC where field in (:1, :2, :3, :4, :5, :6) >and then did the binding. > >The first query (without bound variables) took 10 seconds to execute >100 versions. The second one (with bound variables) took 13 seconds! > >Then I changed the bound variable query to use all 255 bound variables >in the *IN* clause (to have a more generic query, >i.e. :1, :2, ..., :255) and then assigned random values to first 6 >variables and '0' to the rest of the 249. The resultant query just >slowed to a crawl. I did not have the patience to see it run 100 times. > >So what does it tell me? I remember that the perception about bound >variables forced developers in our shop to change queries NOT to use >bound variables (a few months ago) and todays test just confirms it.