Re: Optimizer estimated cardinality very low

Thank you, Jonathan

Without changing the sql and adding the column group the plan stayed the same, predicate information:
Predicate Information (identified by operation id):

   4 -
access("ORDER_DETL"."ORDR_RQST_NUMBER"="ORDER_REQST"."ORDR_RQST_NUMBER" AND

              "ORDER_DETL"."ORDR_RQST_DATE"="ORDER_REQST"."ORDR_RQST_DATE")    6 - storage("ORDER_REQST"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_REQST"."ORDR_RQST_DATE"<=TO_DATE(' 2020-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("ORDER_REQST"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_REQST"."ORDR_RQST_DATE"<=TO_DATE(' 2020-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   8 - storage("ORDER_DETL"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_DETL"."ORDR_RQST_DATE"<=TO_DATE(' 2020-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("ORDER_DETL"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_DETL"."ORDR_RQST_DATE"<=TO_DATE(' 2020-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

And when I changed the sql to use "< 1st Aug" the predicate is: Predicate Information (identified by operation id):

   4 -
access("ORDER_DETL"."ORDR_RQST_NUMBER"="ORDER_REQST"."ORDR_RQST_NUMBER" AND

              "ORDER_DETL"."ORDR_RQST_DATE"="ORDER_REQST"."ORDR_RQST_DATE")    6 - storage("ORDER_REQST"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_REQST"."ORDR_RQST_DATE"<TO_DATE(' 2020-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("ORDER_REQST"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_REQST"."ORDR_RQST_DATE"<TO_DATE(' 2020-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   8 - filter("ORDER_DETL"."ORDR_RQST_DATE">=TO_DATE(' 2020-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "ORDER_DETL"."ORDR_RQST_DATE"<TO_DATE(' 2020-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

I'll do the recommended testing and review the information related to the bugs.
Thanks again!

On Thu, Oct 8, 2020 at 4:44 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> You say the plan for the July query didn't change - does that include the
> predicate information section ?
>
> If you've modified the query to use the ">= 1st Jul" and "< 1st Aug"
> then the date predicate should have disappeared from the predicate
> information since it's an exact match for the partition definition.
>
> The only other thought that crosses my mind at present is that you've got
> a frequency histogram on the date partition stats (in both tables), but a
> hybrid histogram at the global level. There are various bugs with hybrid
> (and top-N) histograms in 12c with patches available (
> https://jonathanlewis.wordpress.com/2018/01/15/histogram-hassle/) so it's
> possible that something about your hybrid histogram has confused the
> optimizer.
>
> A couple of tests I would do:
> Write the query without the explicit date predicates, but use the
> "partition extended" table syntax to specify the partition. Do this for
> both June and July. If the estimates in the plans match the queries with
> dates specified then the anomaly may be something to do with the optimizer
> code; if they do match then the anomaly is more likely to be something to
> do with the available stats (or object definitions).
>
> Regards
> Jonathan Lewis
>
>
>
>
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
> www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#m_2140098577282994835_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

-- 
Daniel Coello Villacis

--
http://www.freelists.org/webpage/oracle-l