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!
