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@xxxxxxxxx>
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
