Stats Gathering Information

We can set and get statistics options from below view.

Default DB All Options:

SELECT DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') AS autostats_target,

       DBMS_STATS.GET_PREFS('CASCADE') AS cascade,

       DBMS_STATS.GET_PREFS('DEGREE') AS degree,

       DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') AS estimate_percent,

       DBMS_STATS.GET_PREFS('METHOD_OPT') AS method_opt,

       DBMS_STATS.GET_PREFS('NO_INVALIDATE') AS no_invalidate,

       DBMS_STATS.GET_PREFS('GRANULARITY') AS granularity,

       DBMS_STATS.GET_PREFS('PUBLISH') AS publish,

       DBMS_STATS.GET_PREFS('INCREMENTAL') AS incremental,

       DBMS_STATS.GET_PREFS('STALE_PERCENT') AS stale_percent

FROM   dual;

 

TABLE Level:

 

exec DBMS_STATS.SET_TABLE_PREFS (<USER_NAME>,'<TABLE_NAME>','INCREMENTAL','FALSE');

 

SELECT

  owner, table_name,

  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,

  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,

  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,

  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,

  DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,

  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt

FROM dba_tables

WHERE table_name='<TABLE_NAME>

ORDER BY owner, table_name;

 

We can use below View, but it will not give mmuch information.

 

select * from DBA_TAB_STAT_PREFS;

 

SCHEMA Level:

 

exec DBMS_STATS.SET_SCHEMA_PREFS (<USER_NAME>,'ESTIMATE_PERCENT','8');

 

SELECT

  username,

  DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incremental,

  DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') granularity,

  DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') stale_percent,

  DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') estimate_percent,

  DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,

  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt

FROM dba_users

WHERE username=<USERNAME>

ORDER BY username;

 

DATABASE Level:

 

exec DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

 

SELECT

  DBMS_STATS.get_prefs(pname=>'INCREMENTAL') incremental,

  DBMS_STATS.get_prefs(pname=>'GRANULARITY') granularity,

  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT') publish,

  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT') estimate_percent,

  DBMS_STATS.get_prefs(pname=>'CASCADE') cascade,

  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt

FROM dual;