Hello,
I've recently been looking at a Data warehouse running on a single instance 11.2.0.3 database. There a few large partitioned tables and associated indexes that take many hours to analyze. The tables are ranged partitioned on a date column with each partition representing a single day. Data is only inserted into the most recent daily partition with occasional updates and additional inserts on partitions that are 2-3 days old already.
The Oracle automatic analyze job struggles to analyze the tables in the maintenance window so I want to do something to reduce the analyze time. Given that the data is static after after a couple of days I want to do something to improve the time taken to analyze the tables and their partitions so have been looking at locking statistics and incremental stats gathering. I'm quite new to DBMS_STATS so would be grateful if anyone could comment on a couple of questions I have about locking and incremental stats.
If I lock the statistics on the older partitions (DBMS_STATS.LOCK_PARTITION_STATS) will the automatic Oracle maintenance job just gather stats on the new and most recent partitions that have not yet had their stats locked? Will the global table level stats be derived from the partition stats?
When using incremental stats (EXEC DBMS_STATS.SET_TABLE_PREFS('xx','xxxxx','INCREMENTAL','TRUE');) is there any need to lock the stats of the older partitions or does Oracle recognise there is no need to recollect the stats for the older partitions?
Also when enabling incremental does this force an initial full collection of stats on the entire partitioned object before continuing in an incremental fashion going forward?
Thanks in advance,
ma365