Manage performance of GATHERSTATS job for PARTITION TABLE in Oracle

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' );

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.