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