Incremental & Concurrent Statistics Collection

Incremental Statistics Collection for Partitioned Objects

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 GRANULARITY when 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.

To initiate incremental statistics for a partitioned table, the INCREMENTAL preference must be set to TRUE. If you have altered the default preferences, make sure the GRANULARITY and ESTIMATE_PERCENT preferences for the specific table are reverted the defaults.

 -- Implement INCREMENTAL Statistics Gathering  
 -- Schema Level  
 SQL> exec dbms_stats.set_schema_prefs('<owner>','incremental', 'true');  
 -- Table Level  
 SQL> exec dbms_stats.set_table_prefs('<owner>','<partition_table_name>','incremental','true');  
 SQL> exec dbms_stats.set_table_prefs('<owner>','<partition_table_name>','publish','true');  

 -- Check  
 SQL> select dbms_stats.get_prefs('incremental','<owner>','<partition_table_name>') from dual;  
 SQL> select dbms_stats.get_prefs('publish','<owner>','<partition_table_name>') from dual;  

Concurrent statistics gathering

Oracle Database 11g Release 2 (11.2.0.2) introduced a new statistics gathering mode, 'Concurrent statistics gathering', this mode enables you to gather statistics on multiple tables in a schema (or database), and multiple (sub)partitioning within a table concurrently.

Concurrent statistics gathering is controlled by a global preference, CONCURRENT in the DBMS_STATS package. The preference can be set to TRUE or FALSE. The default is FALSE. When CONCURRENT is set to TRUE Oracle employs Oracle Job scheduler and Advanced queuing components to help manage the multiple statistics gathering jobs.

To turn on the concurrent statistics gathering mode
-- Run the following command:

 SQL> exec DBMS_STATS.SET_GLOBAL_PREFS ('CONCURRENT','TRUE');  

-- Set job_queue_processes parameter.
-- Grant additional privileges. The user must have the following Job Scheduler and AQ privileges

CREATE JOB

MANAGE SCHEDULER

MANAGE ANY QUEUE

Details about how this feature works can be found on the Optimizer Blog.