On Feb 23, 2:00 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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:6...
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
just double checked it was a 10053 trace.
Received on Fri Feb 23 2007 - 13:15:08 CST
