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: flush share pool for one sql to force new plan

Re: flush share pool for one sql to force new plan

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 23 Feb 2007 11:00:11 -0800
Message-ID: <1172257210.408916.295300@j27g2000cwj.googlegroups.com>


On Feb 23, 11:28 am, "Ben" <bal..._at_comcast.net> wrote:
> On Feb 23, 10:07 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > _optim_peek_user_binds controls whether or not bind variables are
> > peeked (viewed) when a plan is generated, as in the following partial
> > list of parameters from a 10053 trace on 10.2.0.2:
> > _like_with_bind_as_equality = false
> > _table_scan_cost_plus_one = true
> > _cost_equality_semi_join = true
> > _default_non_equality_sel_check = true
> > _new_initial_join_orders = true
> > _oneside_colstat_for_equijoins = true
> > _optim_peek_user_binds = true
> > _minimal_stats_aggregation = true
> > _force_temptables_for_gsets = false
> > workarea_size_policy = auto
> > _smm_auto_cost_enabled = true
> > _gs_anti_semi_join_allowed = true
> > _optim_new_default_join_sel = true
> > optimizer_dynamic_sampling = 2
> > _pre_rewrite_push_pred = true
>
> > There are complications with bind variables and histograms. I recall
> > reading books/articles a couple years ago stating that histograms are
> > useless on columns, when applications make proper use of bind
> > variables when querying on those columns. Something along the lines
> > of this (see Addendum 15th Aug 2001):http://www.jlcomp.demon.co.uk/faq/sqlplsql.html
>
> > Most of the issues with the use of bind variables together with
> > histograms seem to have diminished. The same author who wrote the
> > above also wrote many articles and even a book or two that better
> > covers the topic of bind variables used together with histograms on
> > more recent releases of Oracle. I am sure that he covers the topic in
> > his "Cost Based Oracle Fundamentals" book, but I am only 2/3 the way
> > through the second read-through of the book. Links to help explain
> > the issues:http://www.jlcomp.demon.co.uk/stats.doc
> > "The full power of the histogram is available only when you use
> > literal values in your code (or when Oracle peeks at the bind
> > variables to find the values)"
>
> >http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2007-01-19.1...
> > "Bind peeking may lead to the predicted access path not matching the
> > access path taken during execution."
>
> >http://www.dbazine.com/oracle/or-articles/goodrum4
> > Discusses _like_with_bind_as_equality and _optim_peek_user_binds and
> > the impact of binds on plans, for Oracle 9.2.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > - Show quoted text
>
> I'm pretty sure that my bind variables are not being 'peeked' into. I
> just tried explictly setting the _optim_peek_user_binds via
>
> SQL> alter session set "_optim_peek_user_binds"=true;
>
> Session altered.
>
> and then reproducing the above test but changing the statement a
> little to ensure a hard parse. When I browse back through the 10056
> trace file there is nothing listed about BIND value and the parameter
> doesn't show.
>

You might want to make certain that you are performing a 10053 trace and not a 10056 trace:
http://www.adp-gmbh.ch/ora/tuning/diagnostic_events/list.html   10053 CBO Enable optimizer trace
  10056 dump analyze stats (kdg)

I am running 10.2.0.2 here. I know that there have been a few changes in the 10053 trace file between version 9.2 and 10.2, so I am not sure how much information is provided in such a trace with 9.2. I don't have access to Oracle 9.2 - anyone with Oracle 9.2 want to run a 10053 trace when bind variables are used and post how bind variable peeking is indicated in the file?

10053 trace intpretation on Oracle 9.2:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:63445044804318

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Feb 23 2007 - 13:00:11 CST

Original text of this message

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