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,'=') || ' ' ||
'================================'
);
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;
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 then used the label to qualify the name of a variable I’ve used inside the block.
[…] Stats preferences – Aug 2021: a simple query to report all the system and table preferences, default or explicitly set […]
Pingback by Statistics catalogue | Oracle Scratchpad — January 29, 2022 @ 6:44 pm GMT Jan 29,2022 |