在11g中，数据库可以增量维护global统计信息，通过仅扫描那些有一定比例数据发生变化的分区，其他分区仍然使用老的统计信息。
about INCREMENTAL option:
INCREMENTAL
Determines whether the global statistics of a partitioned table will be maintained without doing a full table scan.
INCREMENTAL - This value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
the INCREMENTAL value for the partitioned table is set to TRUE;
the PUBLISH value for the partitioned table is set to TRUE;
the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITYwhen gathering statistics on the table.
If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.
impacts:
the additional space usage in SYSAUX, as Synopses are maintained automatically by the database. They store additional information in the SYSAUX tablespace about the data stored in every table partition. For tables with large numbers of columns and high NDVs, the amount of data can become large so space usage in SYSAUX should be monitored. Statistics gathering procedures must maintain the synopsis information so this can add a performance overhead for some operations.
Synopses allow the database to maintain accurate table statistics in a scalable manner: as tables grow in size and the number of partitions increases, the performance benefit of this feature will become more apparent.
we need to new that dbms_stats.set_*_prefs procedure can set the value. dbms_stats.set_*_prefs procedure can work on table level, schema level, database level or global level to override the value of each parameter.
SET_TABLE_PREFS
SET_SCHEMA_PREFS
SET_DATABASE_PREFS
SET_GLOBAL_PREFS
Example to check INCREMENTAL option:
CREATE TABLE "TEST"."TEST_TABLE" PARTITION BY RANGE ("ORDER_DATE") (PARTITION "TEST_DATA_FEB_2007" VALUES LESS THAN (TO_DATE('2007-03-01', 'YYYY-MM-DD')), PARTITION "TEST_DATA_MAR_2007" VALUES LESS THAN (TO_DATE('2007-04-01','YYYY-MM-DD')), PARTITION "TEST_DATA_APR_2007" VALUES LESS THAN (TO_DATE('2007-05-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAY_2007" VALUES LESS THAN (TO_DATE('2007-06-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUN_2007" VALUES LESS THAN (TO_DATE('2007-07-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUL_2007" VALUES LESS THAN (TO_DATE('2007-08-01','YYYY-MM-DD')), PARTITION "TEST_DATA_AUG_2007" VALUES LESS THAN (TO_DATE('2007-09-01','YYYY-MM-DD')), PARTITION "TEST_DATA_SEP_2007" VALUES LESS THAN (TO_DATE('2007-10-01','YYYY-MM-DD')), PARTITION "TEST_DATA_OCT_2007" VALUES LESS THAN (TO_DATE('2007-11-01','YYYY-MM-DD')), PARTITION "TEST_DATA_NOV_2007" VALUES LESS THAN (TO_DATE('2007-12-01','YYYY-MM-DD')), PARTITION "TEST_DATA_DEC_2007" VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JAN_2008" VALUES LESS THAN (TO_DATE('2008-02-01','YYYY-MM-DD')), PARTITION "TEST_DATA_FEB_2008" VALUES LESS THAN (TO_DATE('2008-03-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAR_2008" VALUES LESS THAN (TO_DATE('2008-04-01','YYYY-MM-DD' )), PARTITION "TEST_DATA_APR_2008" VALUES LESS THAN (TO_DATE('2008-05-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAY_2008" VALUES LESS THAN (TO_DATE('2008-06-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUN_2008" VALUES LESS THAN (TO_DATE('2008-07-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUL_2008" VALUES LESS THAN (TO_DATE('2008-08-01','YYYY-MM-DD')), PARTITION "TEST_DATA_AUG_2008" VALUES LESS THAN (TO_DATE('2008-09-01','YYYY-MM-DD')), PARTITION "TEST_DATA_SEP_2008" VALUES LESS THAN (TO_DATE('2008-10-01','YYYY-MM-DD')), PARTITION "TEST_DATA_OCT_2008" VALUES LESS THAN (TO_DATE('2008-11-01','YYYY-MM-DD')), PARTITION "TEST_DATA_NOV_2008" VALUES LESS THAN (TO_DATE('2008-12-01','YYYY-MM-DD')), PARTITION "TEST_DATA_DEC_2008" VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JAN_2009" VALUES LESS THAN (TO_DATE('2009-02-01','YYYY-MM-DD')), PARTITION "TEST_DATA_FEB_2009" VALUES LESS THAN (TO_DATE('2009-03-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAR_2009" VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD' )), PARTITION "TEST_DATA_APR_2009" VALUES LESS THAN (TO_DATE('2009-05-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAY_2009" VALUES LESS THAN (TO_DATE('2009-06-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUN_2009" VALUES LESS THAN (TO_DATE('2009-07-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUL_2009" VALUES LESS THAN (TO_DATE('2009-08-01','YYYY-MM-DD')), PARTITION "TEST_DATA_AUG_2009" VALUES LESS THAN (TO_DATE('2009-09-01','YYYY-MM-DD')), PARTITION "TEST_DATA_SEP_2009" VALUES LESS THAN (TO_DATE('2009-10-01','YYYY-MM-DD')), PARTITION "TEST_DATA_OCT_2009" VALUES LESS THAN (TO_DATE('2009-11-01','YYYY-MM-DD')), PARTITION "TEST_DATA_NOV_2009" VALUES LESS THAN (TO_DATE('2009-12-01','YYYY-MM-DD')), PARTITION "TEST_DATA_DEC_2009" VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JAN_2010" VALUES LESS THAN (TO_DATE('2010-02-01','YYYY-MM-DD')), PARTITION "TEST_DATA_FEB_2010" VALUES LESS THAN (TO_DATE('2010-03-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAR_2010" VALUES LESS THAN (TO_DATE('2010-04-01','YYYY-MM-DD' )), PARTITION "TEST_DATA_APR_2010" VALUES LESS THAN (TO_DATE('2010-05-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAY_2010" VALUES LESS THAN (TO_DATE('2010-06-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUN_2010" VALUES LESS THAN (TO_DATE('2010-07-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUL_2010" VALUES LESS THAN (TO_DATE('2010-08-01','YYYY-MM-DD')), PARTITION "TEST_DATA_AUG_2010" VALUES LESS THAN (TO_DATE('2010-09-01','YYYY-MM-DD')), PARTITION "TEST_DATA_SEP_2010" VALUES LESS THAN (TO_DATE('2010-10-01','YYYY-MM-DD')), PARTITION "TEST_DATA_OCT_2010" VALUES LESS THAN (TO_DATE('2010-11-01','YYYY-MM-DD')), PARTITION "TEST_DATA_NOV_2010" VALUES LESS THAN (TO_DATE('2010-12-01','YYYY-MM-DD')), PARTITION "TEST_DATA_DEC_2010" VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JAN_2011" VALUES LESS THAN (TO_DATE('2011-02-01','YYYY-MM-DD')), PARTITION "TEST_DATA_FEB_2011" VALUES LESS THAN (TO_DATE('2011-03-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAR_2011" VALUES LESS THAN (TO_DATE('2011-04-01','YYYY-MM-DD' )), PARTITION "TEST_DATA_APR_2011" VALUES LESS THAN (TO_DATE('2011-05-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAY_2011" VALUES LESS THAN (TO_DATE('2011-06-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUN_2011" VALUES LESS THAN (TO_DATE('2011-07-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUL_2011" VALUES LESS THAN (TO_DATE('2011-08-01','YYYY-MM-DD')), PARTITION "TEST_DATA_AUG_2011" VALUES LESS THAN (TO_DATE('2011-09-01','YYYY-MM-DD')), PARTITION "TEST_DATA_SEP_2011" VALUES LESS THAN (TO_DATE('2011-10-01','YYYY-MM-DD')), PARTITION "TEST_DATA_OCT_2011" VALUES LESS THAN (TO_DATE('2011-11-01','YYYY-MM-DD')), PARTITION "TEST_DATA_NOV_2011" VALUES LESS THAN (TO_DATE('2011-12-01','YYYY-MM-DD')), PARTITION "TEST_DATA_DEC_2011" VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JAN_2012" VALUES LESS THAN (TO_DATE('2012-02-01','YYYY-MM-DD')), PARTITION "TEST_DATA_FEB_2012" VALUES LESS THAN (TO_DATE('2012-03-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAR_2012" VALUES LESS THAN (TO_DATE('2012-04-01','YYYY-MM-DD')), PARTITION "TEST_DATA_APR_2012" VALUES LESS THAN (TO_DATE('2012-05-01','YYYY-MM-DD')), PARTITION "TEST_DATA_MAY_2012" VALUES LESS THAN (TO_DATE('2012-06-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUN_2012" VALUES LESS THAN (TO_DATE('2012-07-01','YYYY-MM-DD')), PARTITION "TEST_DATA_JUL_2012" VALUES LESS THAN (TO_DATE('2012-08-01','YYYY-MM-DD')), PARTITION "TEST_DATA_AUG_2012" VALUES LESS THAN (TO_DATE('2012-09-01','YYYY-MM-DD')), PARTITION "TEST_DATA_SEP_2012" VALUES LESS THAN (TO_DATE('2012-10-01','YYYY-MM-DD')), PARTITION "TEST_DATA_OCT_2012" VALUES LESS THAN (TO_DATE('2012-11-01','YYYY-MM-DD')), PARTITION "TEST_DATA_NOV_2012" VALUES LESS THAN (TO_DATE('2012-12-01','YYYY-MM-DD')), PARTITION "TEST_DATA_DEC_2012" VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) ) AS SELECT ORDER_ID, to_date(to_char(ORDER_DATE, 'DD-MON-YYYY, HH24:MI:SS'), 'DD-MON-YYYY, HH24:MI:SS') ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID FROM TEST.TEST_DATA;-- Enable incremental statistics on the TEST_TABLE tableBEGIN dbms_stats.set_table_prefs('TEST','TEST_TABLE','INCREMENTAL','TRUE');END;/--Check that the preference was set correctlySELECT dbms_stats.get_prefs('INCREMENTAL','TEST','TEST_TABLE')FROM dual;-- Gather statistics on the TEST_TABLE tableBEGIN dbms_stats.gather_table_stats('TEST','TEST_TABLE');END;/-- Check the last_analyzed date for the TEST_TABLE table and its partitionsSELECT table_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyze`FROM user_tablesWHERE table_name='TEST_TABLE';SELECT partition_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyzeFROM user_tab_partitionsWHERE table_name='TEST_TABLE'ORDER BY partition_position;-- Check the actual column statsSELECT column_name, num_distinct, num_nullsFROM user_tab_col_statisticsWHERE table_name='TEST_TABLE';-- Check we actually did do an incremental stats gatherSELECT o.name, decode(bitand(h.spare2, 8), 8, 'yes', 'no') incrementalFROM sys.hist_head$ h, sys.obj$ oWHERE h.obj# = o.obj#AND o.name = 'TEST_TABLE'AND o.subname is null;--Now lets make a change to the data in the TEST_TABLE tableUPDATE TEST_TABLESET order_status=80WHERE order_id=2459;commit;-- We also need to simulate a load into the TEST_TABLE tableINSERT /*+ APPEND */ INTO TEST_TABLESELECT * FROM ext_TEST_DATA;commit;-- Regather statistics on the TEST_TABLE tableBEGIN dbms_stats.gather_table_stats('TEST','TEST_TABLE');END;/-- lets check the last_analyzed date for the TEST_TABLE table and its paritionsSELECT table_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyzeFROM user_tablesWHERE table_name='TEST_TABLE';SELECT partition_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyzeFROM user_tab_partitionsWHERE table_name='TEST_TABLE'ORDER BY partition_position;
Oracle reference link:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_STATS.html#GUID-6429FC41-52D7-4541-9242-2AC015266D6F
https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-1
https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-2
https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-3
