Re: Optimizer estimated cardinality very low

Daniel,

I've lost count of the number of times I've said: "you hardly ever need to read the 10053 (CBO) trace file"; but in this case I think I need to see the 10053 trace file.
I suspect the issue is something to do with the hybrid histogram and the fact that the July data is the top partition of the table but I can't think of anything that would make the cardinality estimate drop to 1.

If you could enable event 10053 ('alter session set events '10053 trace name context forever'; and execute the june and july statements and send me the trace files (zip'ed would be good). Please start a fresh session for each query so that they are in different files, and to make sure that the optimizer does actually optimize them afresh (which is when the 10053 trace is generated) add small comment to each statement.

Regards
Jonathan Lewis

On Tue, 13 Oct 2020 at 22:30, Daniel Coello <coello.daniel_at_gmail.com> wrote:

> Thank you for the feedback,
>
> The points brought up about the data are accurate and it is what would be
> expected with exception of the high_value for the july dates (july/21).
> I checked on the date values to ensure there were no inconsistencies with
> the statistics information; I found them to be accurate regarding low/high
> values for july partitions:
>
> *DBA_PART_COL_STATISTICS*
> *TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE
> HIGH_VALUE HISTOGRAM*
> ORDER_REQST ORDR_RQST_DATE 3 78780707010101
> 78780715010101 FREQUENCY
> ORDER_DETL ORDR_RQST_DATE 3 78780707010101
> 78780715010101 FREQUENCY
>
> *DBA_PART_HISTOGRAMS*
> *TABLE_NAME PARTITION_NAME COLUMN_NAME ENDPOINT_ACTUAL_VALUE
> ENDPOINT_ACTUAL_VALUE_RAW*
> ORDER_REQST SYS_P971481 ORDR_RQST_DATE 07-JUL-20
> 78780707010101
> ORDER_REQST SYS_P971481 ORDR_RQST_DATE 14-JUL-20
> 7878070E010101
> ORDER_REQST SYS_P971481 ORDR_RQST_DATE 21-JUL-20
> 78780715010101
> ORDER_DETL SYS_P971761 ORDR_RQST_DATE 07-JUL-20
> 78780707010101
> ORDER_DETL SYS_P971761 ORDR_RQST_DATE 14-JUL-20
> 7878070E010101
> ORDER_DETL SYS_P971761 ORDR_RQST_DATE 21-JUL-20
> 78780715010101
>
> *DBA_TAB_COL_STATISTICS*
> *TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE
> HIGH_VALUE HISTOGRAM*
> ORDER_REQST ORDR_RQST_DATE 2696 64650101010101
> 78780715010101 HYBRID
> ORDER_DETL ORDR_RQST_DATE 2696 64650101010101
> 78780715010101 HYBRID
>
> *DBA_TAB_HISTOGRAMS** (highest value)*
> *OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
> ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE ENDPOINT_ACTUAL_VALUE_RAW*
> DW_OWNER ORDER_REQST ORDR_RQST_DATE 84005 2459052
> 21-JUL-20 78780715010101
> DW_OWNER ORDER_DETL ORDR_RQST_DATE 40208 2459052
> 21-JUL-20 78780715010101
>
> Both June and July partitions have frequency histograms on ordr_rqst_date,
> which would mean column groups are ignored in both cases and still join
> estimates for june partitions are good but for july are not.
>
> I am puzzled as individual statistics values and single table statistics
> are accurate on its own but when it comes down to calculating the estimate
> for the semi join cardinality just for July the value is lesser than 1.
>
> Based on the feedback I think if this leaning towards an statistics issue
> I would like to understand what is the calculation and what factors
> (histograms, col groups, partition vs global stats) play a role on the
> estimation of the cardinality and more importantly how I can efficiently
> take steps to help the optimizer have the best information to an accurate
> result.
>
>
> Thanks again,
>
>
>
>
>
> On Fri, Oct 9, 2020 at 7:03 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Daniel,
>>
>> I point I'd completely overlooked when I made the comment about matching
>> column groups: column group stats are not used if any of the underlying
>> columns has a histogram and the column group itself does not. In your July
>> stats ordr_rqst_date has a histogram and the column groups don't, so their
>> stats won't be used.
>>
>> A few other points about the stats:
>> The table has '01-01-0001' recorded as the low value for the
>> ordr_rqst_date - that extreme value MIGHT have an impact on some queries.
>> The high value is 15th July 2020 for table and July partition.
>>
>> The ordr_rqst_number is stored as a varchar2 (or char), not as a number,
>> although the values stored do look like numbers. This can have a serious
>> impact on the optimizer's arithmetic, but since you're only using equality
>> on this column in this query it's probably irrelevant to this case.
>>
>> It looks a little odd that the high value ordr_rqst_number for the table
>> ( 9936510407132880 ) is much bigger than the value for July (
>> 2020000000740002 ) - again that may be irrelevant in this case, and it may
>> be something that you expect to see anyway (i.e. no correlation between
>> data and number).
>>
>> Finally, you've said that there's one date per week in each partition -
>> are the loads/dates supposed to be on the same day of each week or can they
>> move around. I ask this because the July data has a low of 7th July and a
>> high of 15th July, and 3 distinct values. This doesn't look consistent with
>> 3 weeks on the same day of the week, so I was wondering if this was normal,
>> or whether it was an indication that something a little unusual had
>> happened during the data loads.
>>
>> ---------------
>>
>> It would be useful to have the june stats to compare with the July stats
>> in case any significant difference shows up.
>>
>> ----------------------
>>
>> Sorry about the error in the previous post which basically said:
>> if they match then is probably X but if they match then it's probably Y -
>> one of those should have had a negative.
>>
>> My thinking was as follows:
>>
>> if specifying the partitions (with no date predicate) gives you the same
>> estimates as supplying date predicates then the optimizer has *probably *recognized
>> perfectly correctly that your dates are all about one specific partition
>> and will be doing the same arithmetic in both cases -- and that would mean
>> there's something about the statistics that causing a problem.
>>
>> if the two variations give you different estimates that would *suggest*
>> that the optimizer has *NOT* (emphasised just to make sure one of my
>> statements had the appropriate negative) used a code path that is 100%
>> about just one known partition, and therefore there may be an error in the
>> optimizer code.
>>
>> As it is you got the same estimates in both cases - which means it's
>> probably something about the stats.
>>
>> Having said that there is a quirky little difference between the two sets
>> of predicate information you posted when you compared "<= 31st July" with
>> "< 1st Aug". They both have the same ACCESS and FILTER predicates (apart
>> from the change in date value, of course) at exactly the same operation -
>> but the query using 1st Aug didn't offload the filter on the date on the
>> ordr_rqst_detail table (viz: the plan didn't report a STORAGE predicate.)
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>
> --
> Daniel Coello Villacis
>
>

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