Wednesday, February 14, 2018

How to view default settings for dbms_stats



Thanks to the author of the blog "Thinking out loud" for writing this piece of code. It makes it easy to view the default settings that applies when gathering schema statistics:

SELECT 
  username,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incr,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') grty,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') "stale%",
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') "estimate%",
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt,
  DBMS_STATS.get_prefs(pname=>'DEGREE') degree,
  DBMS_STATS.get_prefs(pname=>'CONCURRENT') CONC
FROM dba_users
WHERE username IN ('BILL','BOB','SCOTT','JANE')
ORDER BY username;

USERNAME INCR GRTY STALE% ESTIMATE% CASCADE METHOD_OPT DEGREE CONC
BILL TRUE AUTO 10 DBMS_STATS.AUTO_SAMPLE_SIZE DBMS_STATS.AUTO_CASCADE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.DEFAULT_DEGREE OFF
BOB TRUE AUTO 10 DBMS_STATS.AUTO_SAMPLE_SIZE DBMS_STATS.AUTO_CASCADE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.DEFAULT_DEGREE OFF
SCOTT TRUE AUTO 10 DBMS_STATS.AUTO_SAMPLE_SIZE DBMS_STATS.AUTO_CASCADE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.DEFAULT_DEGREE OFF
JANE TRUE AUTO 10 DBMS_STATS.AUTO_SAMPLE_SIZE DBMS_STATS.AUTO_CASCADE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.DEFAULT_DEGREE OFF

No comments:

Post a Comment