Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.media.kyoto-u.ac.jp!newshub.sdsu.edu!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!g47g2000cwa.googlegroups.com!not-for-mail From: "Daniel Fink" Newsgroups: comp.databases.oracle.server Subject: Re: using bind variables makes the optimizer choose a bad plan Date: 9 Sep 2005 13:15:52 -0700 Organization: http://groups.google.com Lines: 19 Message-ID: <1126296952.363303.97150@g47g2000cwa.googlegroups.com> References: <1126180708.428528.259710@g14g2000cwa.googlegroups.com> <1126182812.036037.98650@o13g2000cwo.googlegroups.com> <1126189989.404474.323550@g47g2000cwa.googlegroups.com> <1126211766.498568.107440@g14g2000cwa.googlegroups.com> NNTP-Posting-Host: 216.88.130.72 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1126296957 2477 127.0.0.1 (9 Sep 2005 20:15:57 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 9 Sep 2005 20:15:57 +0000 (UTC) In-Reply-To: User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.8) Gecko/20050511 Firefox/1.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: g47g2000cwa.googlegroups.com; posting-host=216.88.130.72; posting-account=P-kTng0AAAD3g70oUy_l14RYHR_8opyJ Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251205 Using bind variables - less parse time, less stress on shared pool, more scalable, may produce less than optimal execution plan Not using bind variable - may produce better execution plan, more parse time, more stress on shared pool, less scalable This is a very simplistic comparison, but it illustrates that there is a trade off to using bind variables. For most applications/statements, using bind variables will produce acceptable performance. For a few applications/statements, bind variables might cause the optimizer to choose a bad plan. It is the responsibility of the designer/developer/dba to identify the exceptions and find the appropriate solution. There is not an absolute "Bind Variables are always GOOD|BAD" rule. They are 'usually' a good thing, but not always. Regards, Dan Fink