Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • To: nkodner@xxxxxxxxx
  • Date: Tue, 24 Nov 2009 13:45:04 -0800

From the 10053 it appears that the different NDV calculation for
histograms in 10.2.0.4 may be root cause for the flip from NLJ to HJ
when changing OFE.  If you put these two files in a graphical diff
tool, it makes it fairly obvious.   Look at the Single Table
Cardinality Estimation section.

OFE=10.2.0.4
 Column (#11): PRTY_ID(NUMBER)
    AvgLen: 4.00 NDV: 232607 Nulls: 1694696 Density: 4.0792e-06 Min: 1
Max: 1591999
    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 247
  Using density: 4.0792e-06 of col #11 as selectivity of unpopular value pred
  Table: LETTER_BIN  Alias: LETTER_BIN
    Card: Original: 4739017  Rounded: 12  Computed: 12.42  Non Adjusted: 12.42

OFE=10.1.0
Column (#11): PRTY_ID(NUMBER)
    AvgLen: 4.00 NDV: 232607 Nulls: 1694696 Density: 4.8136e-04 Min: 1
Max: 1591999
    Histogram: Freq  #Bkts: 247  UncompBkts: 254  EndPtVals: 247
  Table: LETTER_BIN  Alias: LETTER_BIN
    Card: Original: 4739017  Rounded: 1465  Computed: 1465.41  Non
Adjusted: 1465.41

What is the difference in execution time of the two queries in
allstats_both_queries.txt ?

This may be one of those edge cases that is best dealt with by using
either a hint or an outline.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l


Other related posts: