My friend Bernhard explains to me his issue using incremental gather_table_stats on a large partitioned table in a DWH. This table has only changes on a few partitions per day, normally the last two. I was looking for a solution that only those partitions should be re-gathered if they were changed more than 15% (STALE_PERCENT).
based on some Blogs
- Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table?
- Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics Part I, Part II & Part III
- Partitioned Table Incremental Statistics Gathering Scans Entire Table Instead of Relevant Partitions (Doc ID 1541543.1)
and as per documentation:
If the INCREMENTAL value for a partition table is set to TRUE, and GRANULARITY set to AUTO, and PUBLISH set to TRUE, and AUTO_SAMPLE_SIZE set to AUTO, Oracle will gather statistics and updates the global table statistics by scanning only those partitions and not the entire table
We created a test case
- a table INCSTATS
- one column VAL number
- containing the numbers 1 to 450
- range partitioned by VAL so we have 6 partitions
Everything seems to be fine, but when I tried to update 20 or more rows of only one partition without moving them to another partition, Oracle started to gather the stats for all partitions. And for now on, this behavior will not changed. If I changed one row, several rows or do no DML in any case Oracle re-gather all stats on all partitions.
After reading a lot of blogs I get an idea reading the last line of a remark to a blog from Chris about histograms.
Checking the column HISTOGRAM in USER_TAB_COL_STATISTICS before and after executing the upgrade described above.
Before the Upgrade the Column has the value „HYBRID“, after the Upgrade the Column has a Value of „NONE“. So I assume Oracle tries to collect histograms of all partitions but will not update the global statistics successfully. Or anything else went wrong collecting histograms …
So, after setting ‚METHOD_OPT‘ to ‚FOR ALL COLUMNS SIZE 1‘ no histograms will be collected and everything works fine.
You must set ‚METHOD_OPT‘ and all other parameters (listing see below) in order to gather the incremental stats. If e.g. ‚METHOD_OPT‘ or other are not set correctly (= as listed below), everything seems to show that Oracle want to use incremental stats, but during re-gathering all partitions will be gathered every time.
In the code you will find some queries to check if incremental stats will be yes or not.
Every time you execute DBMS_STATS.gather_table_stats you should also execute DBMS_STATS.flush_database_monitoring_info;
We tested on 220.127.116.11 (SPARC, x86) and 18.104.22.168 (x86). Whenever we run into an issue it occurred on all three platforms. However, the final solution will work on all three platforms. So, if there is any bug we run into, it is not fixed in 12.2 while present in 12.1
Short description of the sql code:
- create the table partitioned by range as select …
- setting some parameter for gathering stats on table level using DBMS_STATS.SET_TABLE_PREFS
- ‚INCREMENTAL‘ = ‚TRUE‘
- ‚METHOD_OPT‘ = ‚FOR ALL COLUMNS SIZE 1‘
- ‚GRANULARITY‘ = ‚APPROX_GLOBAL AND PARTITION‘
- ‚INCREMENTAL_LEVEL‘ = ‚PARTITION
- ‚PUBLISH‘ = ‚TRUE‘
- ‚INCREMENTAL_STALENESS‘ = ‚USE_STALE_PERCENT, USE_LOCKED_STATS‘
- ‚STALE_PERCENT‘ = ’15‘
- gather_table_stats for every partition
- check there are no histograms
- check if incremental stats is on
- gather_table_stats again w/o any DML => only global stats will be changed
- wait some seconds and re-gather table stats => only global stats will be changed
- insert 20 rows into the last partition & re-gather stats => last partition and global stats are changed
- update less than STALE_PERCENT rows in partition P_200 & re-gather stats => no stats will be changed
- update more than STALE_PERCENT in partition P_200 & re-gather stats
thank you to Bernhard Rosenberger (MT AG) and Dr. Peter Alteheld (MT AG) for their support .