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' );
Smart way of Technology 