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 |