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

On Feb 23, 10:07 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 23, 9:01 am, "Ben" <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > On Feb 22, 5:59 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > > I don't know for certain that you can tell that it is peeking at the
> > > bind variable from the above. The cost based optimizer in Oracle
> > > assumes that 5% of the rows will be returned when bind variables are
> > > used, if peeking is not permitted. The predicted 5% is small enough
> > > so that Oracle will probably choose to use an index to access the
> > > data, even if that is not the best access method. A 10053 trace will
> > > tell you for certain. If you still want to experiment:
>
> > > In SQLPlus:
> > > ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
>
> > > Set up your SQL statement to use bind variables as you did before.
> > > Make a small change to the SQL statement to make certain that Oracle
> > > performs a hard parse (capitalization, spacing, etc). Then execute
> > > your SQL statement. Then:
>
> > > ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
>
> > > Open the trace file using a text editor: Notepad, EDIT, vi, jed, etc.
> > > See if the trace file contains something that looks like this (this is
> > > from a 10053 trace on 10.2.0.2):
> > > *******************************************
> > > Peeked values of the binds in SQL statement
> > > *******************************************
> > > kkscoacd
> > > Bind#0
> > > oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
> > > oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0
> > > kxsbbbfp=3444ef58 bln=32 avl=07 flg=09
> > > value="Y"
>
> > > If you see something like the above, you will know for certain that
> > > Oracle peeked at the bind variables.
>
> > > It has been a while since I have looked at the output of TKPROF. I
> > > usually dig directly in the 10046 trace file for the information of
> > > interest. Based on "Optimizing Oracle Performance" by Cary Millsap
> > > (pg 83), the "Row Source Operations" indicated in the TKPROF output
> > > are identified by STAT lines in the trace file.
>
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks for all the info Charles, I really appreciate the help.
> > I ran the 10056 like you suggested and I don't see anything about bind
> > variable in it.
> > I notice all the parameters listed at the first that the cbo uses.
> > Which of these are related to bind peeking?
> > BASE STATISTICAL INFORMATION
> > ***********************
> > Table stats Table: F47027 Alias: XYZ
> > TOTAL :: CDN: 278209 NBLKS: 67794 AVG_ROW_LEN: 1226
> > -- Index stats
> > INDEX NAME: F47027_0 COL#: 4 5 3 6
> > TOTAL :: LVLS: 2 #LB: 1696 #DK: 278209 LB/K: 1 DB/K: 1
> > CLUF: 55649
> > INDEX NAME: F47027_10 COL#: 13
> > TOTAL :: LVLS: 2 #LB: 1704 #DK: 3207 LB/K: 1 DB/K: 17 CLUF:
> > 56511
> > INDEX NAME: F47027_11 COL#: 12 4
> > TOTAL :: LVLS: 2 #LB: 1310 #DK: 27320 LB/K: 1 DB/K: 2 CLUF:
> > 59938
> > INDEX NAME: F47027_8 COL#: 16 6 50
> > TOTAL :: LVLS: 2 #LB: 2558 #DK: 278123 LB/K: 1 DB/K: 1
> > CLUF: 62539
> > INDEX NAME: F47027_9 COL#: 16 50
> > TOTAL :: LVLS: 2 #LB: 2653 #DK: 277675 LB/K: 1 DB/K: 1
> > CLUF: 96023
> > _OPTIMIZER_PERCENT_PARALLEL = 0
> > ***************************************
> > SINGLE TABLE ACCESS PATH
> > Column: SZEDSP Col#: 12 Table: F47027 Alias: XYZ
> > NDV: 3 NULLS: 0 DENS: 1.7972e-06
> > FREQUENCY HISTOGRAM: #BKT: 278209 #VAL: 3
> > TABLE: F47027 ORIG CDN: 278209 ROUNDED CDN: 458 CMPTD CDN: 458
> > Access path: tsc Resc: 10291 Resp: 10291
> > Skip scan: ss-sel 0 andv 45
> > ss cost 45
> > index io scan cost 0
> > Access path: index (scan)
> > Index: F47027_11
> > TABLE: F47027
> > RSC_CPU: 0 RSC_IO: 104
> > IX_SEL: 1.6462e-03 TB_SEL: 1.6462e-03
> > BEST_CST: 104.00 PATH: 4 Degree: 1
> > ***************************************
> > OPTIMIZER STATISTICS AND COMPUTATIONS
> > ***************************************
> > GENERAL PLANS
> > ***********************
> > Join order[1]: F47027[XYZ]#0
> > Best so far: TABLE#: 0 CST: 104 CDN: 458 BYTES:
> > 561508
> > Final - All Rows Plan:
> > JOIN ORDER: 1
> > CST: 104 CDN: 458 RSC: 104 RSP: 104 BYTES: 561508
> > IO-RSC: 104 IO-RSP: 104 CPU-RSC: 0 CPU-RSP: 0
> > *** 2007-02-23 08:43:40.830
> > QUERY
> > ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'- Hide quoted text -
>
> > - Show quoted text -
>
> _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. Received on Fri Feb 23 2007 - 10:28:18 CST