Re: Re: hash join cardinality missestimate

Hi,
Investigating a case we found that distinct keys (DK) in an unique Index can go into the join estimate. As shown here DK was messed up by real time stats: https://twitter.com/LotharFlatz/status/1429740984271884292 Here an excerpt from two 10053 traces that show how the DK goes into the selectivity estimate and indirectly into the join estimate. Correct Stats:
Index Stats::
  Index: STORE_PK Col#: 1
  LVLS: 1 #LB: 9 #DK: 8325 LB/K: 1.00 DB/K: 1.00 CLUF: 169.00 NRW: 8325.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 16384 BSZ: 1   KKEISFLG: 1
  Index: STORE_UK1 Col#: 6 7
  LVLS: 1 #LB: 11 #DK: 8325 LB/K: 1.00 DB/K: 1.00 CLUF: 1070.00 NRW: 8325.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 16384 BSZ: 1   KKEISFLG: 1
Wrong Stats:
Index Stats::
  Index: STORE_PK Col#: 1
  LVLS: 1 #LB: 9 #DK: 0 LB/K: 1.00 DB/K: 1.00 CLUF: 169.00 NRW: 8325.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 16384 BSZ: 1   KKEISFLG: 1
  Index: STORE_UK1 Col#: 6 7
  LVLS: 1 #LB: 11 #DK: 0 LB/K: 1.00 DB/K: 1.00 CLUF: 1070.00 NRW: 8325.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 16384 BSZ: 1   KKEISFLG: 1
******** Cost index join ********
Correct Stats:
  ColGroup (#1, Index) STORE_UK1
    Col#: 6 7 CorStregth: 14.95
Wrong Stats:
  ColGroup (#1, Index) STORE_UK1
    Col#: 6 7 CorStregth: 0.00
Correct Stats:
Join selectivity using 1 ColGroups: 1.2012e-04 (sel1 = 1.000000, sel2 = 1.000000) Outer Join Card: 1409990.000000 = max ( outer (1409990.000000),(outer (1409990.000000) * inner (8325.000000) * sel (1.2012e-04))) Join Card - Rounded: 1409990 Computed: 1409990.000000 Wrong Stats:
Join selectivity using 1 ColGroups: 1.000000 (sel1 = 1.000000, sel2 = 1.000000) Outer Join Card: 11738166750.000000 = max ( outer (1409990.000000),(outer (1409990.000000) * inner (8325.000000) * sel (1.000000))) Join Card - Rounded: 11738166750 Computed: 11738166750.000000 Regards
Lothar
----Ursprüngliche Nachricht----

Von : laurentiu.oprea06_at_gmail.com
Datum : 05/10/2021 - 09:42 (MS)
An :
Cc : oracle-l_at_freelists.org
Betreff : Re: hash join cardinality missestimate  Thanks a lot Jonathan.    

My understanding was that for join selectivity the computed sel value was 1/greatest(NDVs) where in my case I was expecting to be 1/(greatest(6,8), , but looks like computed sel is 2.1058e-04 . Any level of dynamic sampling(from 0 to 8) looks like it is not influencing this value.         

  So my assumption is wrong?    

  În mar., 5 oct. 2021 la 10:27, Jonathan Lewis <   jlewisoracle_at_gmail.com> a scris:              

    Insufficient information                          

    However you have a combination of two important factors in the join                          

1. All the columns explicitly referenced in the fragment of trace file have frequency histograms
2. There's an sql_plan_directive in force which is going to adjust any other calculations the optimizer will do.

    For the impact of a frequency histograms on joins you could look at a series I wrote time ago. Here's a link to part 2, it refers back to part 1 and has pingbacks from parts 3, 4, and 5.:     https://jonathanlewis.wordpress.com/2018/10/05/join-cardinality-2/                                  

    For an understanding (though there's a lot of version dependency to worry about) of SQL Plan Directives you could start with Stefan Koehler's article, which includes several links to material by Mauro Pagano and Franck Pachot.     https://blogs.sap.com/2015/06/01/oracle-db-optimizer-part-xii-revealing-sql-plan-directive-details-for-existingloaded-cursor-from-cbo-and-sql-dynamic-sampling-services-trace/                                  

    Regards        

    Jonathan Lewis                              

    On Mon, 4 Oct 2021 at 20:14, Laurentiu Oprea <     laurentiu.oprea06_at_gmail.com> wrote:                  

     Hello,
     
      
     
     
      I have a big cardinality misestimate for a HJ operation:
     
     
      Join cardinality for HJ/SMJ (no post filters):  28073805.777726, outer: 42.720832, inner: 7801742.651163, sel: 2.1058e-04
      
Join Card - Rounded: 70185 Computed: 70184.514444
      
     
     
      
     
     
      Can someone help me understand how optimizer came with that sel value? (attached 10053 extract)
     
     
      
     
     
      Thank you. 
     
    
   
  
 

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 05 2021 - 10:12:01 CEST