Statistics Gathering

script to check if automated stats gather is in place or not.

SQL> select Owner, job_name, program_name, schedule_name, schedule_type,failure_count, last_start_date , comments  from dba_scheduler_jobs;

Script to check the window for the automated stats gather.

SQL> select window_name, repeat_interval,duration, window_priority, next_start_date, last_start_date, Comments from dba_scheduler_windows;

Script to check the stale statistics & it's tables

SQL> select * from sys.dba_tab_modifications where table_owner not in (‘SYS’,’SYSTEM’) order by timestamp;

Check the last analyzed data for the table/index

SQL> select table_name, stale_stats, last_analyzed from dba_tab_statistics where owner= '&OWNER' and stale_stats='YES' order by last_analyzed desc;

SQL> select table_name, partition_name, cast(last_analyzed as timestamp) last_analyzed from dba_tab_statistics order by 3, 1, 2;

Script to gather the stats.

a) For Table:

EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SMART' , tabname => 'AGENT',cascade => true, estimate_percent => 10, granularity => 'ALL', degree => 1);

b) For Schema:

EXEC dbms_stats.gather_schema_stats(ownname=>'XES_MGR',estimate_percent=>100,CASCADE=>TRUE);

c) For Database:

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);