Incremental statistics for Partioned table.-11g/10g

Incremental statistics for Partioned table.-11g

  This feature improves the performance of statistics collection in Datawarehouse databases.In Exadata environment some of the big tables sizes are from 50g. It is really a nightmare to collect the statistics for those big tables( partitioned).

Until 10g ( <10.2.0.3) if we want to collect stats for partitioned table,oracle scans full table and collects stats for all the partitioned( including the partions which were not modified).

With this new feature,Oracle will gather statistics on the new partition and update the global table statistics by scanning only those partitions that have been modified and not the entire table.

When the following criteria are met, the database updates global statistics incrementally by scanning only the partitions that have changed:

•The INCREMENTAL value for the partitioned table is true.
•The PUBLISH value for the partitioned table is true. ( default)
•The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

How to collect a stats for a partitioned table

1.exec dbms_stats.set_table_pref('TABLEOWNER','TABLENAME','INCREMENTAL','TRUE');
2.exec dbms_stats.gather_table_stats( Owner=>'TABLEOWNER', Tabname=>'JRC', Partname=>'MARCH_2010', Granularity=>'AUTO');

GRANULARITY to AUTO to gather subpartition, partition, or global statistics depending on the partition
type

Consequences of Incremental stats

If a table uses composite partitioning, then the database only gathers statistics for modified subpartitions. The database does not gather statistics at the subpartition level for unmodified subpartitions.

If a table uses incremental statistics, and if this table has a locally partitioned index, then the database gathers index statistics at the global level and for modified (not unmodified) index partitions. The database does not generate global index statistics from the partition-level index statistics. Rather, the database gathers global index statistics by performing a full index scan.

CAUTION:

* ( In 11gR1/R2 ,if we use estimate_percent a value other than AUTO_SAMPLE_SIZE,incremental stats will ignore the sampling and it does full scan due to a Bug:11710056 so either use auto instead of sampling or disable the incremental stats)

* Incremental stats will be slower if the dictionary stats is missing.so it is advisable to collect dictionary stats

* Uncremental stats will consume more space if incremental stats is enabled . The biggest occupants are the following objects:
- Table SYS.WRI$_OPTSTAT_SYNOPSIS$
- Index SYS.I_WRI$_OPTSTAT_SYNOPSIS.
 Incremental Statistics stores a synopsis(10KB size) for each partition in the sysaux table for each table. synopsis is nothing but statistical metadata for that partition and the columns in the partition. Global statistics are collected by aggregating the synopses from each partition, its eliminates the necessary for the full table scan .



Incremental statistics for Partioned table.-10g

For Oracle Database10g Release 2 (10.2.0.5) there is a solution that simulates the same behavior.

The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that oracle doesnn’t update the number of distinct values for non-partitioning columns and number of distinct keys of the index at the global level.

For partitioned column oracle updates the number of distinct values as the sum of number of distinct values at the partition levels. Also it sets the number of distinct values of columns of unique indexes as the number of rows of the table. The above option is best suited for the cases where  non-partitioning column number of distinct values at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.

This new value exists in 10.2.0.5 and for 10.2.04 a patch 6526370 is available  to enable this feature.