dbms_stats quickie: show global and table preferences

Introduction

To gather statistics for a schema or a table there is the dbms_stats package. Either we call it manually or the automatic statistic gathering (scheduled) job is used.

We can provide many settings for the statistic gathering job as a parameter during the gather call. For parameters that we do not explicitly set, preferences are used. Either on a global or on individual table level.

Since there are many preferences this article has some SQL statements that help to check how the current dbms_stats preferences are.

Table preferences overrule the global preferences. And preferences set by calling gather_..._stats overrule the table preferences. However it is possible to override this last rule by setting the preference_overrides_parameter to true. In that case the table preferences overrule the gather_..._stats parameters.
see https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/options-for-optimizer-statistics-gathering.html#GUID-E616363F-0A7B-4D4D-9384-63934DB69F7D

show global preferences

Script to show the global preferences. Note that the parameter autostats_target can not be set on table level, because it directly influences the area of work for the auto stats gathering job.

set linesize 250
set pagesize 100
column preference_name format a30
column preference_value format a50

-- global preferences
with preflist (preference_name,global_only)
as (select 'APPROXIMATE_NDV_ALGORITHM',0 from dual union all
    select 'AUTO_STAT_EXTENSIONS'     ,0 from dual union all
    select 'AUTO_TASK_STATUS'         ,0 from dual union all
    select 'AUTO_TASK_MAX_RUN_TIME'   ,0 from dual union all
    select 'AUTO_TASK_INTERVAL'       ,0 from dual union all
    select 'AUTOSTATS_TARGET'         ,1 from dual union all
    select 'CASCADE'                  ,0 from dual union all
    select 'CONCURRENT'               ,0 from dual union all
    select 'DEGREE'                   ,0 from dual union all
    select 'ESTIMATE_PERCENT'         ,0 from dual union all
    select 'GLOBAL_TEMP_TABLE_STATS'  ,0 from dual union all
    select 'GRANULARITY'              ,0 from dual union all
    select 'INCREMENTAL'              ,0 from dual union all
    select 'INCREMENTAL_STALENESS'    ,0 from dual union all
    select 'INCREMENTAL_LEVEL'        ,0 from dual union all
    select 'METHOD_OPT'               ,0 from dual union all
    select 'NO_INVALIDATE'            ,0 from dual union all
    select 'OPTIONS'                  ,0 from dual union all
    select 'PREFERENCE_OVERRIDES_PARAMETER',0 from dual union all
    select 'PUBLISH'                  ,0 from dual union all
    select 'STALE_PERCENT'            ,0 from dual union all
    select 'STAT_CATEGORY'            ,0 from dual union all
    select 'TABLE_CACHED_BLOCKS'      ,0 from dual union all
    select 'WAIT_TIME_TO_UPDATE_STATS',0 from dual )
select preference_name, 
       sys.dbms_stats.get_prefs(preference_name) as preference_value,
       global_only
from preflist;

And the result might look like this

PREFERENCE_NAME	PREFERENCE_VALUE
APPROXIMATE_NDV_ALGORITHM	HYPERLOGLOG
AUTO_STAT_EXTENSIONS	OFF
AUTO_TASK_STATUS	OFF
AUTO_TASK_MAX_RUN_TIME	3600
AUTO_TASK_INTERVAL	900
AUTOSTATS_TARGET	ORACLE
CASCADE	DBMS_STATS.AUTO_CASCADE
CONCURRENT	OFF
DEGREE	NULL
ESTIMATE_PERCENT	DBMS_STATS.AUTO_SAMPLE_SIZE
GLOBAL_TEMP_TABLE_STATS	SESSION
GRANULARITY	AUTO
INCREMENTAL	FALSE
INCREMENTAL_STALENESS	NULL
INCREMENTAL_LEVEL	PARTITION
METHOD_OPT	FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE	DBMS_STATS.AUTO_INVALIDATE
OPTIONS	GATHER
PREFERENCE_OVERRIDES_PARAMETER	FALSE
PUBLISH	TRUE
STALE_PERCENT	10
STAT_CATEGORY	OBJECT_STATS, REALTIME_STATS
TABLE_CACHED_BLOCKS	1
WAIT_TIME_TO_UPDATE_STATS	15

global preferences for dbms_stats

Show table preferences

And here is how to show the preferences for a single (or a few) table(s) and compare them to the global preferences. Add your table names to the list of tables in the tabs subquery.

-- table preferences
with tabs (owner, table_name)
  as (select user , 'MYTABLE1' from dual union all
      select 'STAGE' , 'MYTABLE2' from dual union all
      select 'STAGE' , 'MYTABLE' from dual
      )
, preflist (preference_name, global_only)
as (select 'APPROXIMATE_NDV_ALGORITHM',0 from dual union all
    select 'AUTO_STAT_EXTENSIONS'     ,0 from dual union all
    select 'AUTO_TASK_STATUS'         ,0 from dual union all
    select 'AUTO_TASK_MAX_RUN_TIME'   ,0 from dual union all
    select 'AUTO_TASK_INTERVAL'       ,0 from dual union all
    select 'AUTOSTATS_TARGET'         ,1 from dual union all
    select 'CASCADE'                  ,0 from dual union all
    select 'CONCURRENT'               ,0 from dual union all
    select 'DEGREE'                   ,0 from dual union all
    select 'ESTIMATE_PERCENT'         ,0 from dual union all
    select 'GLOBAL_TEMP_TABLE_STATS'  ,0 from dual union all
    select 'GRANULARITY'              ,0 from dual union all
    select 'INCREMENTAL'              ,0 from dual union all
    select 'INCREMENTAL_STALENESS'    ,0 from dual union all
    select 'INCREMENTAL_LEVEL'        ,0 from dual union all
    select 'METHOD_OPT'               ,0 from dual union all
    select 'NO_INVALIDATE'            ,0 from dual union all
    select 'OPTIONS'                  ,0 from dual union all
    select 'PREFERENCE_OVERRIDES_PARAMETER',0 from dual union all
    select 'PUBLISH'                  ,0 from dual union all
    select 'STALE_PERCENT'            ,0 from dual union all
    select 'STAT_CATEGORY'            ,0 from dual union all
    select 'TABLE_CACHED_BLOCKS'      ,0 from dual union all
    select 'WAIT_TIME_TO_UPDATE_STATS',0 from dual
    )
,tabprefs as (select t.owner, t.table_name, preference_name, 
       sys.dbms_stats.get_prefs(preference_name,null,null) as preference_global,
       case when global_only=0 
            then sys.dbms_stats.get_prefs(preference_name,t.owner,t.table_name) 
       end as preference_table,
       global_only
       from preflist 
       cross join tabs t
)
select p.*, 
       case when global_only=1 then null
            when preference_global=preference_table then 'FALSE' 
             else 'TRUE' 
             end as individual_table_pref
from tabprefs p
;

The select gives a comparison between the preferences on global and on table level.

OWNER	TABLE_NAME	PREFERENCE_NAME	PREFERENCE_GLOBAL	PREFERENCE_TABLE	GLOBAL_ONLY	INDIVIDUAL_TABLE_PREF
SVEN	MYTABLE1	APPROXIMATE_NDV_ALGORITHM	HYPERLOGLOG	HYPERLOGLOG	0	FALSE
SVEN	MYTABLE1	AUTO_STAT_EXTENSIONS	OFF	OFF	0	FALSE
SVEN	MYTABLE1	AUTO_TASK_STATUS	OFF	OFF	0	FALSE
SVEN	MYTABLE1	AUTO_TASK_MAX_RUN_TIME	3600	3600	0	FALSE
SVEN	MYTABLE1	AUTO_TASK_INTERVAL	900	900	0	FALSE
SVEN	MYTABLE1	AUTOSTATS_TARGET	ORACLE		1
SVEN	MYTABLE1	CASCADE	DBMS_STATS.AUTO_CASCADE	DBMS_STATS.AUTO_CASCADE	0	FALSE
SVEN	MYTABLE1	CONCURRENT	OFF	OFF	0	FALSE
SVEN	MYTABLE1	DEGREE	NULL	NULL	0	FALSE
SVEN	MYTABLE1	ESTIMATE_PERCENT	DBMS_STATS.AUTO_SAMPLE_SIZE	DBMS_STATS.AUTO_SAMPLE_SIZE	0	FALSE
SVEN	MYTABLE1	GLOBAL_TEMP_TABLE_STATS	SESSION	SESSION	0	FALSE
SVEN	MYTABLE1	GRANULARITY	AUTO	AUTO	0	FALSE
SVEN	MYTABLE1	INCREMENTAL	FALSE	TRUE	0	TRUE
SVEN	MYTABLE1	INCREMENTAL_STALENESS	NULL	NULL	0	FALSE
SVEN	MYTABLE1	INCREMENTAL_LEVEL	PARTITION	PARTITION	0	FALSE
SVEN	MYTABLE1	METHOD_OPT	FOR ALL COLUMNS SIZE AUTO	FOR ALL COLUMNS SIZE AUTO	0	FALSE
SVEN	MYTABLE1	NO_INVALIDATE	DBMS_STATS.AUTO_INVALIDATE	DBMS_STATS.AUTO_INVALIDATE	0	FALSE
SVEN	MYTABLE1	OPTIONS	GATHER	GATHER	0	FALSE
SVEN	MYTABLE1	PREFERENCE_OVERRIDES_PARAMETER	FALSE	FALSE	0	FALSE
SVEN	MYTABLE1	PUBLISH	TRUE	TRUE	0	FALSE
SVEN	MYTABLE1	STALE_PERCENT	10	10	0	FALSE
SVEN	MYTABLE1	STAT_CATEGORY	OBJECT_STATS, REALTIME_STATS	OBJECT_STATS, REALTIME_STATS	0	FALSE
SVEN	MYTABLE1	TABLE_CACHED_BLOCKS	1	1	0	FALSE
SVEN	MYTABLE1	WAIT_TIME_TO_UPDATE_STATS	15	15	0	FALSE

table preferences for dbms_stats

The last column INDIVIDUAL_TABLE_PREF indicates whether the global setting was changed on table level or not. But take it with a grain of salt. Some preferences might have additional restrictions, like the INCREMENTAL_LEVEL can be set to PARTITION only if the table is actually partitioned. Otherwise the default for the table will be TABLE, even if the global setting differs.

Show all changed preferences on table level for a schema

And finally here is a statement that goes through all the tables in one (or several schemas) and finds settings where the global and the table preference differs. For each preference value combination it lists the number of table and the table names.

In the tabs subquery list all the schemas you want to be checked.

-- All tables with a non default setting
with tabs
  as (select owner, table_name, partitioned 
      from dba_tables
      where owner in (user,'STAGE')
      )
,preflist (preference_name, global_only)
as (select 'APPROXIMATE_NDV_ALGORITHM',0 from dual union all
    select 'AUTO_STAT_EXTENSIONS'     ,0 from dual union all
    select 'AUTO_TASK_STATUS'         ,0 from dual union all
    select 'AUTO_TASK_MAX_RUN_TIME'   ,0 from dual union all
    select 'AUTO_TASK_INTERVAL'       ,0 from dual union all
    select 'AUTOSTATS_TARGET'         ,1 from dual union all
    select 'CASCADE'                  ,0 from dual union all
    select 'CONCURRENT'               ,0 from dual union all
    select 'DEGREE'                   ,0 from dual union all
    select 'ESTIMATE_PERCENT'         ,0 from dual union all
    select 'GLOBAL_TEMP_TABLE_STATS'  ,0 from dual union all
    select 'GRANULARITY'              ,0 from dual union all
    select 'INCREMENTAL'              ,0 from dual union all
    select 'INCREMENTAL_STALENESS'    ,0 from dual union all
    select 'INCREMENTAL_LEVEL'        ,0 from dual union all
    select 'METHOD_OPT'               ,0 from dual union all
    select 'NO_INVALIDATE'            ,0 from dual union all
    select 'OPTIONS'                  ,0 from dual union all
    select 'PREFERENCE_OVERRIDES_PARAMETER',0 from dual union all
    select 'PUBLISH'                  ,0 from dual union all
    select 'STALE_PERCENT'            ,0 from dual union all
    select 'STAT_CATEGORY'            ,0 from dual union all
    select 'TABLE_CACHED_BLOCKS'      ,0 from dual union all
    select 'WAIT_TIME_TO_UPDATE_STATS',0 from dual
    )
,tabprefs as (select t.owner, t.table_name, t.partitioned, 
       preference_name, global_only,
       sys.dbms_stats.get_prefs(preference_name,null,null) as preference_global,
       case when global_only=0 then sys.dbms_stats.get_prefs(preference_name,t.owner,t.table_name) end as preference_table
       from preflist 
       cross join tabs t
)
select preference_name, preference_global, preference_table, listagg(owner||'.'||table_name,',' on overflow truncate without count) within group (order by owner, table_name) tables, count(*) table#
from tabprefs p
where global_only=0
and preference_global!=preference_table 
-- Special case: incremental_level can only be TABLE for non partitioned tables, even if default is different.
and not (preference_name='INCREMENTAL_LEVEL' and partitioned='NO' and preference_table='TABLE' and preference_global='PARTITION')
group by preference_name, preference_global, preference_table
order by preference_name, preference_global, preference_table;

Note that there is some special consideration for the previously mentioned INCREMENTAL_LEVEL preference.

PREFERENCE_NAME	PREFERENCE_GLOBAL	PREFERENCE_TABLE	TABLES	TABLE#
INCREMENTAL	FALSE	TRUE	SVEN.ANTRAG_BESTAND,SVEN.ANTRAG_DETAIL,SVEN.ANTRAG_PRODUKT,…	203

tables with individual preferences

So in this case there were 203 tables (not all names shown) where the INCREMENTAL preference on table level was set to TRUE compared to the global setting of FALSE. This indicates that the global preference was changed, after most of the tables had been created.

Conclusion

It is hard to remember all the different settings that do influence stats gathering. Especially in cases where we wonder, why the settings did not seem to work as expected, it helps to check all the preferences on each level.