Re: Need some 10053 Guidance to help me solve a puzzler
Date: Thu, 11 Oct 2012 14:15:59 -0700 (PDT)
Message-ID: <1349990159.67241.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>
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_at_parallon.net" <Christopher.Taylor2_at_parallon.net> To: mwf_at_rsiz.com; jonathan_at_jlcomp.demon.co.uk; niall.litchfield_at_gmail.com Cc: oracle-l_at_freelists.org
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_at_rsiz.com]
Sent: Thursday, October 11, 2012 2:05 PM
To: Taylor Christopher - Nashville; jonathan_at_jlcomp.demon.co.uk; niall.litchfield_at_gmail.com
Cc: oracle-l_at_freelists.org
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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 23:15:59 CEST