RE: Need some 10053 Guidance to help me solve a puzzler
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <mwf@xxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <niall.litchfield@xxxxxxxxx>
- Date: Thu, 11 Oct 2012 14:16:45 -0500
Clustering factor for the index I used in the hint:
Index: MAPY_CALC_SVC_PERF1 Col#: 2
LVLS: 2 #LB: 71350 #DK: 9820620 LB/K: 1.00 DB/K: 1.00 CLUF: 11728286.00
Mark - I think Jonathan hinted at what you're saying about the clustering
factor but I'm "new" to this detailed analysis using 10053 so I'm behind you
guys in regard to how clustering factors, NDVs, random fetches, et.al all
play/fit together. I'm trying to come up to speed on it now - hence this
thread.
Yes, the stats are cascaded.
I'm interested in hearing any thoughts you guys have - learning a lot from this.
Chris
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Thursday, October 11, 2012 2:05 PM
To: Taylor Christopher - Nashville; jonathan@xxxxxxxxxxxxxxxxxx;
niall.litchfield@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Need some 10053 Guidance to help me solve a puzzler
Returning about 10% of the table - but what is the cluster factor on the
index? The CBO might well assess that you'll read fewer total blocks via
FTS.
I *thought* that was already explained in this thread. If the cluster factor
is lousy, the CBO figures the block read via that index to fetch the next
row will have to be largely random and therefore you might have to fetch it
again if ever the index points you back at a block you've read before to
fetch a row. That's on top of the drilling down to the particular rowid you
need from the index.
I didn't catch whether your stats gathering was cascaded to do the index or
not. If using the index plan beats the fts, then it probably has a good
actual cluster factor and a stale bad cluster factor in the stats. Or you
could be hitting the algorithm gaps JL mentioned.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
