Wrong cardinality estimation after gathering statistics ORACLE
1
vote
1
answer
160
views
We have a large table range partitioned by month. Incremental statistics turned on. After scheduled statistics gathering cardinality estimation become weird, like
select count(*) from my_table where date >= trunc(sysdate) - 30 and date 'ownname',
tabname=> 'tabname' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> 4,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO'
);
-- Manual
DBMS_STATS.GATHER_TABLE_STATS
(
ownname => '"ownname"',
tabname => '"tabname"',
partname => '"partname"',
method_opt => 'FOR COLUMNS DATE SIZE 254',
estimate_percent => 1
);
Other partitioned tables are ok.
The differences between this table and others are (as we know):
1. There were wrong inserts in this table. Most dates are between 2014 and 2023, but there are some rows with 1970 and 2024 (we can't change it). Also there is empty partition with 2045.
We tried recreating this but didn't get same behaviour.
2. We messed with histograms, removed some automatically created and manually created some useful function-based. But in USER_TAB_COL_STATISTICS and USER_TAB_HISTOGRAMS histograms for DATE column were present.
What can cause such behaviour? How can we fix it?
Asked by Andy DB Analyst
(110 rep)
Oct 24, 2023, 08:38 AM
Last activity: Oct 25, 2023, 01:50 AM
Last activity: Oct 25, 2023, 01:50 AM