DBMS_STATS.SET_TABLE_PREFS parameters used for GATHERSTATS Job

DBMS_STATS.SET_TABLE_PREFS parameters used for GATHERSTATS Job

With help of procedure, You can set the specific parameters for calculating stats for specific table during statistics calculation during gatherstats job running.

Syntax:

DBMS_STATS.SET_TABLE_PREFS (
ownname IN VARCHAR2, tabname IN VARCHAR2,
pname IN VARCHAR2, pvalue IN VARCHAR2);

Change Parameters
We will show you the default value set for a table SALES in TEST schema and way to change this with SET_TABLE_PREFS procedures.

1. CASCADE
It is used for gatherstats of TABLE along with INDEX stats if value is TRUE otherwise it only done for TABLE only.

SQL> SELECT DBMS_STATS.get_prefs('CASCADE','TEST', 'SALES' ) FROM dual;

DBMS_STATS.GET_PREFS('CASCADE','TEST','SALES')
-----------------------------------------------
DBMS_STATS.AUTO_CASCADE

-- Enable CASCADE option
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'CASCADE', pvalue => 'TRUE' );

--Disable CASCADE option
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'CASCADE', pvalue => 'FALSE' );

--Set Default
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'CASCADE', pvalue => 'DBMS_STATS.AUTO_CASCADE' );

2. DEGREE
It is used for parallelism during stats gathering.

SQL> SELECT DBMS_STATS.get_prefs('DEGREE','TEST', 'SALES' ) FROM dual;

DBMS_STATS.GET_PREFS('DEGREE','TEST','SALES')
--------------------------------------------------------------------------------
NULL

-- Set degree to any value in this i am setting 4
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'DEGREE', pvalue => '4' );

3. GRANULARITY
Determines granularity of statistics to collect for the table.
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' );

4. PUBLISH
Determines whether or not newly gathered statistics will be published once the gather job has completed.

SQL> SELECT DBMS_STATS.get_prefs('PUBLISH','TEST', 'SALES' ) FROM dual;

DBMS_STATS.GET_PREFS('PUBLISH','TEST','SALES')
-----------------------------------------------
TRUE

--Stats not used by oracle or skip any table from gatherstats job
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'PUBLISH', pvalue => 'FALSE' );

5. STALE_PERCENT
Default value is 10%, it determine how many rows are updated in table from previous stats gather.

SQL> SELECT DBMS_STATS.get_prefs('STALE_PERCENT','TEST', 'SALES' ) FROM dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','TEST','SALES')
--------------------------------------------------------------------------------
10

--You can change default value of STALE gatherstats which will include table in gatherstats job.
EXEC DBMS_STATS.SET_TABLE_PREFS(ownname => 'TEST',Tabname => 'SALES', pname => 'STALE_PERCENT', pvalue => '20' );

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.