On Feb 22, 5:59 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 22, 4:40 pm, "Ben" <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > On Feb 22, 4:22 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > > On Feb 22, 3:06 pm, "Ben" <bal..._at_comcast.net> wrote:
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 5 (SYSTEM)
>
> > > > Rows Row Source Operation
> > > > ------- ---------------------------------------------------
> > > > 0 TABLE ACCESS BY INDEX ROWID F47027
> > > > 0 INDEX RANGE SCAN F47027_11 (object id 744882)
>
> > > > Rows Execution Plan
> > > > ------- ---------------------------------------------------
> > > > 0 SELECT STATEMENT GOAL: CHOOSE
> > > > 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'F47027'
>
> > > > ***************************************************************************ญญญญ*****
>
> > > > I'm a little confused here as to why the Row Source Operation shows an
> > > > index scan and the Execution Plan shows a fts.
>
> > >http://www.jlcomp.demon.co.uk/testing_02.html
> > > "...It is the 'proper' output, by the way, as it is the Row Source
> > > Operation section. The Execution Plan section is the one that may be
> > > misleading"
>
> > >http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-11-18.7...
> > > "I stumbled across the titles 'Row Source Operation' and 'Execution
> > > Plan'. I noticed that the access paths were different. Hmm, how could
> > > that be? I began to dig into the Oracle 9i documentation for TKPROF
> > > and found that 'Row Source Operation' is the access path that was
> > > taken during the statement's execution and the 'Explain Plan' (sic) is
> > > the access path that Oracle is predicting the statement will take
> > > before it executes."
>
> > > "Expert Oracle Database Architecture" by Tom Kyte pg 384
> > > "The cr=96384 in the TKPROF Row Source Operation line shows us exactly
> > > how many consistent reads were done..."
>
> > > >From the above:
>
> > > Execution Plan = Best Guess Prior to Execution
> > > Row Source Operation = How the Data was Retrieved
>
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > And then this tells me that it is indeed 'peeking' at the bind
> > variable, and that if I flush the shared_pool then the next time the
> > ERP system calls that select it should indeed use the index.
>
> 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?
- 2007-02-23 08:42:29.591
- SESSION ID:(1103.33589) 2007-02-23 08:42:29.407
QUERY
BEGIN :key1 := ' '; END;
- 2007-02-23 08:43:15.678
QUERY
select * from proddta.f47027 xyz where szedsp = :key1
PARAMETERS USED BY THE OPTIMIZER
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1024000
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 512000
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 8
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
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'
Received on Fri Feb 23 2007 - 08:01:18 CST
