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_NAMEPREFERENCE_VALUE
APPROXIMATE_NDV_ALGORITHMHYPERLOGLOG
AUTO_STAT_EXTENSIONSOFF
AUTO_TASK_STATUSOFF
AUTO_TASK_MAX_RUN_TIME3600
AUTO_TASK_INTERVAL900
AUTOSTATS_TARGETORACLE
CASCADEDBMS_STATS.AUTO_CASCADE
CONCURRENTOFF
DEGREENULL
ESTIMATE_PERCENTDBMS_STATS.AUTO_SAMPLE_SIZE
GLOBAL_TEMP_TABLE_STATSSESSION
GRANULARITYAUTO
INCREMENTALFALSE
INCREMENTAL_STALENESSNULL
INCREMENTAL_LEVELPARTITION
METHOD_OPTFOR ALL COLUMNS SIZE AUTO
NO_INVALIDATEDBMS_STATS.AUTO_INVALIDATE
OPTIONSGATHER
PREFERENCE_OVERRIDES_PARAMETERFALSE
PUBLISHTRUE
STALE_PERCENT10
STAT_CATEGORYOBJECT_STATS, REALTIME_STATS
TABLE_CACHED_BLOCKS1
WAIT_TIME_TO_UPDATE_STATS15
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.

OWNERTABLE_NAMEPREFERENCE_NAMEPREFERENCE_GLOBALPREFERENCE_TABLEGLOBAL_ONLYINDIVIDUAL_TABLE_PREF
SVENMYTABLE1APPROXIMATE_NDV_ALGORITHMHYPERLOGLOGHYPERLOGLOG0FALSE
SVENMYTABLE1AUTO_STAT_EXTENSIONSOFFOFF0FALSE
SVENMYTABLE1AUTO_TASK_STATUSOFFOFF0FALSE
SVENMYTABLE1AUTO_TASK_MAX_RUN_TIME360036000FALSE
SVENMYTABLE1AUTO_TASK_INTERVAL9009000FALSE
SVENMYTABLE1AUTOSTATS_TARGETORACLE1
SVENMYTABLE1CASCADEDBMS_STATS.AUTO_CASCADEDBMS_STATS.AUTO_CASCADE0FALSE
SVENMYTABLE1CONCURRENTOFFOFF0FALSE
SVENMYTABLE1DEGREENULLNULL0FALSE
SVENMYTABLE1ESTIMATE_PERCENTDBMS_STATS.AUTO_SAMPLE_SIZEDBMS_STATS.AUTO_SAMPLE_SIZE0FALSE
SVENMYTABLE1GLOBAL_TEMP_TABLE_STATSSESSIONSESSION0FALSE
SVENMYTABLE1GRANULARITYAUTOAUTO0FALSE
SVENMYTABLE1INCREMENTALFALSETRUE0TRUE
SVENMYTABLE1INCREMENTAL_STALENESSNULLNULL0FALSE
SVENMYTABLE1INCREMENTAL_LEVELPARTITIONPARTITION0FALSE
SVENMYTABLE1METHOD_OPTFOR ALL COLUMNS SIZE AUTOFOR ALL COLUMNS SIZE AUTO0FALSE
SVENMYTABLE1NO_INVALIDATEDBMS_STATS.AUTO_INVALIDATEDBMS_STATS.AUTO_INVALIDATE0FALSE
SVENMYTABLE1OPTIONSGATHERGATHER0FALSE
SVENMYTABLE1PREFERENCE_OVERRIDES_PARAMETERFALSEFALSE0FALSE
SVENMYTABLE1PUBLISHTRUETRUE0FALSE
SVENMYTABLE1STALE_PERCENT10100FALSE
SVENMYTABLE1STAT_CATEGORYOBJECT_STATS, REALTIME_STATSOBJECT_STATS, REALTIME_STATS0FALSE
SVENMYTABLE1TABLE_CACHED_BLOCKS110FALSE
SVENMYTABLE1WAIT_TIME_TO_UPDATE_STATS15150FALSE
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_NAMEPREFERENCE_GLOBALPREFERENCE_TABLETABLESTABLE#
INCREMENTALFALSETRUESVEN.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.

2 thoughts on “dbms_stats quickie: show global and table preferences

  1. For which oracle version this works? On 12.2 it doesn’t
    ERROR:
    ORA-20001: Invalid input values for pname
    ORA-06512: at “SYS.DBMS_STATS”, line 48146
    ORA-06512: at line 1

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 )

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.