GatherStats

 GatherStats

Flush the changes recorded

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

Below gets updated due to insert / update/ delete

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_owner = 'OWNER';

Collect STALE stats

SQL> exec dbms_stats.gather_schema_stats(ownname=>'OWNER',cascade=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,options=>'GATHER STALE',method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.AUTO_DEGREE);

Collect EMPTY stats

SQL> exec dbms_stats.gather_schema_stats(ownname=>'OWNER',cascade=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,options=>'GATHER EMPTY',method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.AUTO_DEGREE);

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_owner = 'OWNER';

Check the last_analyzed time

SQL> select last_analyzed,table_name,num_ROWS ,owner from dba_tables where table_name = 'TAB_NAME';

SQL> exec dbms_stats.gather_schema_stats(ownname=>'OWNER',cascade=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,options=>'LIST EMPTY',method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.AUTO_DEGREE);

SQL> exec dbms_stats.gather_schema_stats(ownname=>'OWNER',cascade=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,options=>'LIST STALE',method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.AUTO_DEGREE);

10g

SQL> select

DBMS_STATS.GET_PARAM ('AUTOSTATS_TARGET'),

DBMS_STATS.GET_PARAM ('CASCADE'),

DBMS_STATS.GET_PARAM ('DEGREE'),

DBMS_STATS.GET_PARAM ('ESTIMATE_PERCENT'),

DBMS_STATS.GET_PARAM ('METHOD_OPT'),

DBMS_STATS.GET_PARAM ('NO_INVALIDATE'),

DBMS_STATS.GET_PARAM ('GRANULARITY')

from dual;

11g

SQL> select

DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET'),

DBMS_STATS.GET_PREFS ('CASCADE'),

DBMS_STATS.GET_PREFS ('DEGREE'),

DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT'),

DBMS_STATS.GET_PREFS ('METHOD_OPT'),

DBMS_STATS.GET_PREFS ('NO_INVALIDATE'),

DBMS_STATS.GET_PREFS ('GRANULARITY'),

DBMS_STATS.GET_PREFS ('PUBLISH'),

DBMS_STATS.GET_PREFS ('INCREMENTAL'),

DBMS_STATS.GET_PREFS ('STALE_PERCENT')

from dual;

10g:

select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB';

select * from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB';

SQL> exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);

PL/SQL procedure successfully completed

SQL> exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);

10g:

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')

---

DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')

---

100

SQL> exec dbms_stats.RESET_PARAM_DEFAULTS();

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')

---

DBMS_STATS.AUTO_SAMPLE_SIZE