Oracle Statistics Preferences Viewer
Displays the current DBMS_STATS preferences configured in the database, including settings related to statistics gathering behavior, concurrency, granularity, incremental stats, and optimizer-related options. Useful for reviewing and auditing optimizer statistics configuration.
oraclesqlindexing-statisticsv1.0.0
0 stars0 downloads19 views0 comments
By OracleDba • Created
Code
(60 lines)123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/statistics_prefs.sql
-- Author : Tim Hall
-- Description : Displays current statistics preferences.
-- Requirements : Access to the DBMS_STATS package.
-- Call Syntax : @statistics_prefs
-- Last Modified: 08-NOV-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 450
COLUMN approximate_ndv_algorithm FORMAT A25
COLUMN auto_stat_extensions FORMAT A20
COLUMN auto_task_status FORMAT A16
COLUMN auto_task_max_run_time FORMAT A22
COLUMN auto_task_interval FORMAT A18
COLUMN cascade FORMAT A23
COLUMN concurrent FORMAT A10
COLUMN degree FORMAT A6
COLUMN estimate_percent FORMAT A27
COLUMN global_temp_table_stats FORMAT A23
COLUMN granularity FORMAT A11
COLUMN incremental FORMAT A11
COLUMN incremental_staleness FORMAT A21
COLUMN incremental_level FORMAT A17
COLUMN method_opt FORMAT A25
COLUMN no_invalidate FORMAT A26
COLUMN options FORMAT A7
COLUMN preference_overrides_parameter FORMAT A30
COLUMN publish FORMAT A7
COLUMN options FORMAT A7
COLUMN stale_percent FORMAT A13
COLUMN stat_category FORMAT A28
COLUMN table_cached_blocks FORMAT A19
COLUMN wait_time_to_update_stats FORMAT A19
SELECT DBMS_STATS.GET_PREFS('APPROXIMATE_NDV_ALGORITHM') AS approximate_ndv_algorithm,
DBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS') AS auto_stat_extensions,
DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') AS auto_task_status,
DBMS_STATS.GET_PREFS('AUTO_TASK_MAX_RUN_TIME') AS auto_task_max_run_time,
DBMS_STATS.GET_PREFS('AUTO_TASK_INTERVAL') AS auto_task_interval,
DBMS_STATS.GET_PREFS('CASCADE') AS cascade,
DBMS_STATS.GET_PREFS('CONCURRENT') AS concurrent,
DBMS_STATS.GET_PREFS('DEGREE') AS degree,
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') AS estimate_percent,
DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS') AS global_temp_table_stats,
DBMS_STATS.GET_PREFS('GRANULARITY') AS granularity,
DBMS_STATS.GET_PREFS('INCREMENTAL') AS incremental,
DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS') AS incremental_staleness,
DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL') AS incremental_level,
DBMS_STATS.GET_PREFS('METHOD_OPT') AS method_opt,
DBMS_STATS.GET_PREFS('NO_INVALIDATE') AS no_invalidate,
DBMS_STATS.GET_PREFS('OPTIONS') AS options,
DBMS_STATS.GET_PREFS('PREFERENCE_OVERRIDES_PARAMETER') AS preference_overrides_parameter,
DBMS_STATS.GET_PREFS('PUBLISH') AS publish,
DBMS_STATS.GET_PREFS('STALE_PERCENT') AS stale_percent,
DBMS_STATS.GET_PREFS('STAT_CATEGORY') AS stat_category,
DBMS_STATS.GET_PREFS('TABLE_CACHED_BLOCKS') AS table_cached_blocks,
DBMS_STATS.GET_PREFS('WAIT_TIME_TO_UPDATE_STATS') AS wait_time_to_update_stats
FROM dual;