Re: hash join cardinality missestimate

Thanks a lot Jonathan and Lothar for the useful information. Appreciate it.

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

>
> Your assumption is correct IF:
>
> It's a single column join with equality
> There are no other predicates on the two tables
> There are no histograms on the two columns
> There are no nulls for either column
> There are no SQL Plan Directives in place for the columns individually or
> the join
>
> You have histograms and at least one SQL Plan Directive.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Tue, 5 Oct 2021 at 08:43, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
>> 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