In 11g Oracle introduce incremental gathering statistics for partitioned tables. For huge tables gathering statistics takes a lot of time, but if incremental statistic is enabled on table, of course gathering will be faster.
Below is a query when we can check which tables don't have enabled incremental statistics:
SELECT UNIQUE TABLE_OWNER,TABLE_NAME,
SUM(NUM_ROWS)
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER not in ('SYS','SYSTEM')
AND table_name NOT IN
(SELECT table_name
FROM DBA_TAB_STAT_PREFS
WHERE PREFERENCE_NAME='INCREMENTAL'
AND PREFERENCE_VALUE ='TRUE'
)
GROUP BY TABLE_OWNER,TABLE_NAME
ORDER BY 3;
and here is script to enable incremental statistcs for table:
EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'INCREMENTAL', 'TRUE');
EXEC DBMS_STATS.set_table_prefs('SCHEMA1', 'TABLE1', 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'GRANULARITY', 'AUTO');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1', 'TABLE1');
