Re: Need some 10053 Guidance to help me solve a puzzler
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "niall.litchfield@xxxxxxxxx" <niall.litchfield@xxxxxxxxx>
- Date: Thu, 11 Oct 2012 14:15:59 -0700 (PDT)
Clustering factor is a number calculated by Oracle to 'describe' how scattered
the index keys are in the table data. Each index has its own clustering factor
based on the index keys, so you can, and likely will, see different clustering
factors for the same table. [The clustering factor affects how Oracle chooses
indexes but rebuilding an index won't change the clustering factor -- reloading
data in the table in key order will help, but it will help only one index, the
one with the columns you chose to order the table data by. The rest of the
indexes on that same table will end up with worse clustering factors.] The
larger the clustering factor is the 'farther' Oracle has to travel between
index keys and the value you posted doesn't do you any favors with respect to
choosing index scans over full table scans.
If you didn't use cascade=>true in your stats runs you might want to consider
rerunning stats with cascade set to include the indexes. Using a larger sample
size (if you didn't use the entire data set) can also help on the clustering
facter as it gives Oracle a better sample to base its calculations on, and that
sample could include better representations of any data skew that might be
present.
David Fitzjarrell
________________________________
From: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>
To: mwf@xxxxxxxx; jonathan@xxxxxxxxxxxxxxxxxx; niall.litchfield@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, October 11, 2012 1:16 PM
Subject: RE: Need some 10053 Guidance to help me solve a puzzler
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
