Re: Optimizer estimated cardinality very low

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>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

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