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

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.1078911078   "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. Received on Fri Feb 23 2007 - 09:07:32 CST