Search

Top 60 Oracle Blogs

Recent comments

Printing all table preferences affecting dbms_stats.gather_table_stats

Oracle 11g introduced the abilty to control the behaviour of the dbms_stats package by setting preferences on the database, schema, and table level. These affect the way dbms_stats goes about doing its work. This feature has been extensively documented, I found the post by Maria Colgan exceptionally good at explaining the mechanism.

I often have to check table preferences in case statistics look a little suspicious but there isn’t an Oracle supplied way that I’m aware of to print all table preferences so I came up with a little script to do so. It currently supports Oracle 19c but it’s super easy to update the code for other releases. The table – passed as a parameter – needs to be in the user’s schema:

$ cat table_prefs.sql 
set serveroutput on verify off

prompt
prompt getting table prefs for &1
prompt ----------------------------------------

declare 
        v_version varchar2(100);
        v_compat  varchar2(100);

        type prefs_t is table of varchar2(100);

        v_prefs_19c prefs_t := prefs_t(
                'APPROXIMATE_NDV_ALGORITHM',
                'AUTO_STAT_EXTENSIONS',
                'AUTO_TASK_STATUS',
                'AUTO_TASK_MAX_RUN_TIME',
                'AUTO_TASK_INTERVAL',
                'CASCADE',
                'CONCURRENT',
                'DEGREE',
                'ESTIMATE_PERCENT',
                'GLOBAL_TEMP_TABLE_STATS',
                'GRANULARITY',
                'INCREMENTAL',
                'INCREMENTAL_STALENESS',
                'INCREMENTAL_LEVEL',
                'METHOD_OPT',
                'NO_INVALIDATE',
                'OPTIONS',
                'PREFERENCE_OVERRIDES_PARAMETER',
                'PUBLISH',
                'STALE_PERCENT',
                'STAT_CATEGORY',
                'TABLE_CACHED_BLOCKS');

        procedure print_prefs(pi_prefs prefs_t) as
                v_value varchar2(100);
        begin   
                for i in pi_prefs.first .. pi_prefs.last loop
                        v_value := sys.dbms_stats.get_prefs(
                                pname => pi_prefs(i),
                                ownname => user,
                                tabname => sys.dbms_assert.sql_object_name('&1'));

                sys.dbms_output.put_line(rpad(pi_prefs(i), 50) || ': ' || v_value);
                end loop;
        end;

begin   
        sys.dbms_utility.db_version(v_version, v_compat);

        if v_version = '19.0.0.0.0' then
                print_prefs(v_prefs_19c);
        else
                raise_application_error(-20001, 'Oracle ' || v_version || ' not yet supported');
        end if;

end;
/ 

Extending the code is very straight-forward. Simply create a new variable like v_prefs_12102 and copy/paste all the possible values from the 12c documentation for dbms_stats.get_prefs(). Then add a branch for your release and off you go.

Happy troubleshooting!