Re: Optimizer estimated cardinality very low
Date: Thu, 29 Oct 2020 11:35:27 +0000
Message-ID: <CAGtsp8kGHs0dwTVBY6-WFH47EC8zW6M0_=m=MCezm4iV5raGSQ_at_mail.gmail.com>
Nenad,
I saw your email with the formulae in a couple of days ago, I was just covering two points:
- (some of) the variations that could be checked to find out where, specifically, Daniel's setup are giving him good luck in June and bad luck in July.
- raising the complaint that a switch of algorithm shouldn't produce a catastrophic change in estimate for a minor change in values (with the follow-up that this looks like a bug - though there are other cases where the optimizer switches algorithm catastrophically in ways that seem unreasonable).
Regards
Jonathan Lewis
On Wed, 28 Oct 2020 at 13:16, Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:
> Jonathan,
>
>
>
> The usage of column group statistics obviously depends on many factors,
> such as the presence of histograms, matching columns on col group (or
> index) stats in both tables, sanity checks, etc..
>
>
>
> The problem is that the optimizer switches to completely different formula
> for calculating selectivity if it decides to use col group stats:
>
>
>
> With column groups: sel = 1/greater(NDV(t1),NDV(t2)
>
> Without column groups sel = 1
>
>
>
> The difference can be in orders of magnitude, like in Daniel’s case.
>
>
>
> The problem isn’t only with semi-join. The join selectivity calculation is
> also broken for multi-joins with a filter.
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Jonathan Lewis
> *Sent:* Mittwoch, 28. Oktober 2020 14:03
> *To:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *Subject:* Re: Optimizer estimated cardinality very low
>
>
>
>
>
> I've got to say that I think there's a logic error in the way this whole
> thing works.
>
>
>
> A very small change in statistics shouldn't result in a massive change in
> estimated outcome. (This is different from the nlj/hj switch which can
> look as if it's an analogy, but the nlj/hj switch happens when the
> statistics say that both should cost the same).
>
>
>
> For this existence to go (effectively) from 100% to 0% on the basis of a
> tiny difference really does suggest that someone hasn't thought through the
> implications (or, possibly, two independent pieces of code have been
> hijacked as the A/B options without any consideration of consistency).
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
> On Wed, 28 Oct 2020 at 12:40, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>
>
>
>
> I've modelled this problem on a pair of non-partitioned tables. (Since the
> join is exactly across an exact matching pair of partitions I think this is
> probably valid).
>
> I've got 100,000 rows in claim_header and 110,000 rows in claim_line.
>
> The actual number of distinct trnsct_cntl_nbr in both tables is exactly
> 100,000, with 4 dates
>
> The basic stats collection for tables reports 4 dates and 99,848 distinct
> values for trnsctl_ctl_nbr.
>
> Any set of tests below starts with no indexes, no column groups and no
> histograms
>
>
>
> a) Baseline test - this gets a "suitable" cardinality estimate (100K)
>
> a') create a histogram on either (or both) date columns - still gets the
> "proper" cardinality
>
>
>
>
>
> b) Adding indexes on (trnsctl_cntl_nbr, clm_hdr_pd_dt) at both ends I get
> cardinality 1.
>
> b') Adding hdr_sts_cd to either of the indexes gets "proper" cardinality
>
> b'') With 2-column indexes adding a histogram on date to either table gets
> cardinality 1
>
>
>
>
>
> c) Add a column group to just one table, ndv = 98,832, in both cases
> proper cardinality
>
> c') Add a column group to BOTH tables at once get cardinality 1
>
> c'') Add histogram on date to ONE table (when both have column groups) get
> cardinality 1
>
> c''') Add histograms on date to BOTH tables (when both have column groups)
> get proper cardinality
>
> c'''') As for c''', but increase the NDV (in stages) on the claim_header
> column groups - get cardinality 1 at 99,001
>
> c''''') As for c''', but increase the NDV (in stages) on the claim_line
> column groups - get cardinality 1 at 108,901
>
>
>
> From c''' and c''''' it appears that there's a break point for the sanity
> check at 99% of the rows in the table.
>
> Trying to untangle this to get something useful is going to be a pain -
> and it's still not a complete set of tests.
>
>
>
>
>
> Question - For the two tables, for the two columns in the join/existence,
> what indexes, column groups and histograms do you have.
>
>
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version. Please note that all e-mail communications to and from the
> Vontobel Group are subject to electronic storage and review by Vontobel
> Group. Unless stated to the contrary and without prejudice to any
> contractual agreements between you and Vontobel Group which shall prevail
> in any case, e-mail-communication is for informational purposes only and is
> not intended as an offer or solicitation for the purchase or sale of any
> financial instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 29 2020 - 12:35:27 CET