Manage performance of GATHERSTATS job for PARTITION TABLE in Oracle
Partition table are generally bigger in size, we can manage the gatherstats for them seperately.
Set the setting for partition table stats for incremental, global or partition stats for doing job faster.
Gatherstats job will gather the stats of partition table, we can change the perference according to need for done it fast.
DBMS_STATS.SET_TABLE_PREFS is used to define the property for stats gather for particular table.
GRANULARITY
Granularity define the statistics to collect for the table globally, partition or subpartition and Choose for ALL.
ALL: include all partition, subpartition & global statistics.
AUTO: Default determine the granularity based on partitioning type.
GLOBAL: Gather only global stats
GLOBAL AND PARTITION: Gather global & partition stats, No sub-partition stats.
PARTITION: Gather only partition stats.
SUBPARTITION: Gather only sub partition stats
SQL> SELECT DBMS_STATS.get_prefs('GRANULARITY','TEST', 'SALES' ) FROM dual;
DBMS_STATS.GET_PREFS('GRANULARITY','TEST','SALES')
--------------------------------------------------------------------------------
AUTO
--Set for global partition
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'GRANULARITY', pvalue => 'GLOBAL' );
INCREMENTAL
It is used for determine the global statistics maintained for partitioned table without doing a full table scan.
If its TRUE, then Oracle update the global stats by scanning only changed partition instead of full table scan.
IF its FALSE, then Oracle go for full table scan for maintained global stats for the table.
Following setting need to done for implement:
1. INCREMENTAL = TRUE
2. PUBLISH = TRUE
3. ESTIMATE_PERCENT = AUTO_SAMPLE_SIZE
4. GRANULARITY = AUTO
SQL> SELECT DBMS_STATS.get_prefs('INCREMENTAL','TEST', 'SALES' ) FROM dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','TEST','SALES')
--------------------------------------------------------------------------------
FALSE
--Set for global partition
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'INCREMENTAL', pvalue => 'TRUE' );
--You need to check other condition otherwise modified them to implement it.
--PUBLISH
SQL> SELECT DBMS_STATS.get_prefs('PUBLISH','TEST', 'SALES' ) FROM dual;
DBMS_STATS.GET_PREFS('PUBLISH','TEST','SALES')
--------------------------------------------------------------------------------
TRUE
--If not Change
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'PUBLISH', pvalue => 'TRUE' );
--ESTIMATE_PERCENT
SQL> SELECT DBMS_STATS.get_prefs('ESTIMATE_PERCENT','TEST', 'SALES' ) FROM dual;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','TEST','SALES')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
--If not Change
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'ESTIMATE_PERCENT', pvalue => 'DBMS_STATS.AUTO_SAMPLE_SIZE' );
--GRANULARITY
SQL> SELECT DBMS_STATS.get_prefs('GRANULARITY','TEST', 'SALES' ) FROM dual;
DBMS_STATS.GET_PREFS('GRANULARITY','TEST','SALES')
--------------------------------------------------------------------------------
PARTITION
--Change to AUTO
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'GRANULARITY', pvalue => 'AUTO' );
Smart way of Technology 