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
>
> a) All the columns explicitly referenced in the fragment of trace file
> have frequency histograms
> b) 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