Preferences

I’ve made a few comments in the past about setting “table preferences” for stats collection – most significantly the table_cached_blocks preference that affects the calculation of the clustering_factor of all the indexes on the table, the incremental preference for dictating the strategy used for dealing with partitioned tables, and the method_opt preference for dictating precise requirments for histograms.

If you want to check the current preferences set for a table you can query the XXXX_tab_stat_prefs views. For some reason in the dim and distant past (perhaps in a beta release before the views had been created but perhaps because the views show only the preferences that have been set) I wrote a little script to report all the possible table preferences showing both the table value and the current global value.

rem
rem     Script: get_table_prefs.sql
rem     Dated:  ???
rem     Author: Jonathan Lewis
rem
rem     Last tested
rem             19.11.0.0
rem
rem     Notes
rem     Report the table preferences for a given
rem     owner and table.
rem
rem     Needs to find a list of all legal preferences.
rem             Global prefs are in:    optstat_hist_control$ (sname, spare4)
rem             Table prefs are in:     optstat_user_prefs$ (valchar / valnum)
rem
rem     The public view is dba_tab_stat_prefs / user_tab_stat_prefs.
rem     But if a table has no prefs set there are no rows in the view
rem
rem     This script currently has to be run by sys or a user with 
rem     the select privileges on sys.optstat_hist_control$ (and
rem     execute on dbms_stats).
rem

define m_owner = '&enter_schema'
define m_table = '&enter_tablename'


<<anon_block>>
declare
        pref_count      number(2,0) := 0;
begin
        dbms_output.new_line;
        dbms_output.put_line(
                        rpad('Preference',32) || ' ' ||
                        rpad('Table value',32) || ' ' ||
                        '[Global value]'
        );
        dbms_output.put_line(
                        rpad('=',32,'=') || ' ' ||
                        rpad('=',32,'=') || ' ' ||
                        '================================'
        );

        <<optstat_loop>>
        for c1 in (
                select  sname, spare4 
                from    sys.optstat_hist_control$
                where   spare4 is not null
        ) loop
                anon_block.pref_count := anon_block.pref_count + 1;
                
                dbms_output.put_line(
                        rpad(c1.sname,32) || ' ' ||
                        rpad(dbms_stats.get_prefs(c1.sname,'&m_owner','&m_table'),32) || ' ' 
                        || '[' || c1.spare4 || ']'
                );      
        end loop optstat_loop;

        dbms_output.new_line;
        dbms_output.put_line('Preferences reported: ' || anon_block.pref_count);
end;
/

While I’ve hardly ever used the script – and so haven’t considered reviewing the strategy it uses – the benefit of having it around means that when I have run it I’ve occasionally discovered new preferences that I hadn’t previously noticed (and ought to investigate).

Here’s a sample of the output – from a table with no special settings for preferences:

Preference                       Table value                      [Global value]
================================ ================================ ================================
TRACE                            0                                [0]
DEBUG                            0                                [0]
SYS_FLAGS                        1                                [1]
SPD_RETENTION_WEEKS              53                               [53]
CASCADE                          DBMS_STATS.AUTO_CASCADE          [DBMS_STATS.AUTO_CASCADE]
ESTIMATE_PERCENT                 DBMS_STATS.AUTO_SAMPLE_SIZE      [DBMS_STATS.AUTO_SAMPLE_SIZE]
DEGREE                           NULL                             [NULL]
METHOD_OPT                       FOR ALL COLUMNS SIZE AUTO        [FOR ALL COLUMNS SIZE AUTO]
NO_INVALIDATE                    DBMS_STATS.AUTO_INVALIDATE       [DBMS_STATS.AUTO_INVALIDATE]
GRANULARITY                      AUTO                             [AUTO]
PUBLISH                          TRUE                             [TRUE]
STALE_PERCENT                    10                               [10]
APPROXIMATE_NDV                  TRUE                             [TRUE]
APPROXIMATE_NDV_ALGORITHM        REPEAT OR HYPERLOGLOG            [REPEAT OR HYPERLOGLOG]
ANDV_ALGO_INTERNAL_OBSERVE       FALSE                            [FALSE]
INCREMENTAL                      FALSE                            [FALSE]
INCREMENTAL_INTERNAL_CONTROL     TRUE                             [TRUE]
AUTOSTATS_TARGET                 AUTO                             [AUTO]
CONCURRENT                       OFF                              [OFF]
JOB_OVERHEAD_PERC                1                                [1]
JOB_OVERHEAD                     -1                               [-1]
GLOBAL_TEMP_TABLE_STATS          SESSION                          [SESSION]
ENABLE_TOP_FREQ_HISTOGRAMS       3                                [3]
ENABLE_HYBRID_HISTOGRAMS         3                                [3]
TABLE_CACHED_BLOCKS              1                                [1]
INCREMENTAL_LEVEL                PARTITION                        [PARTITION]
INCREMENTAL_STALENESS            ALLOW_MIXED_FORMAT               [ALLOW_MIXED_FORMAT]
OPTIONS                          GATHER                           [GATHER]
GATHER_AUTO                      AFTER_LOAD                       [AFTER_LOAD]
STAT_CATEGORY                    OBJECT_STATS, REALTIME_STATS     [OBJECT_STATS, REALTIME_STATS]
SCAN_RATE                        0                                [0]
GATHER_SCAN_RATE                 HADOOP_ONLY                      [HADOOP_ONLY]
PREFERENCE_OVERRIDES_PARAMETER   FALSE                            [FALSE]
AUTO_STAT_EXTENSIONS             OFF                              [OFF]
WAIT_TIME_TO_UPDATE_STATS        15                               [15]
ROOT_TRIGGER_PDB                 FALSE                            [FALSE]
COORDINATOR_TRIGGER_SHARD        FALSE                            [FALSE]
MAINTAIN_STATISTICS_STATUS       FALSE                            [FALSE]
AUTO_TASK_STATUS                 OFF                              [OFF]
AUTO_TASK_MAX_RUN_TIME           3600                             [3600]
AUTO_TASK_INTERVAL               900                              [900]

Preferences reported: 41

As the notes that I’ve left in-line say: this version of the script has to be run by SYS or a DBA because of the privileges required.

You might notice, by the way , that this is one of those rare cases where I’ve remembered to use a label to name the PL/SQL block and the main loop, and then used the block’s label to qualify the name of a variable I’ve used inside the block and the loop’s label with the end loop line.