RE: Need some 10053 Guidance to help me solve a puzzler

  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 Received on Thu Oct 11 2012 - 21:16:45 CEST