To check tables and indexes last analyzed date

To check tables and indexes last analyzed date

To check tables and indexes last analyzed date and Database stats

set pages 200

col index_owner form a10

col table_owner form a10

col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats

from dba_tables

where owner not in (‘SYS’,’SYSTEM’)

order by owner,table_name

/

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats

from dba_tab_partitions

where table_owner not in (‘SYS’,’SYSTEM’)

order by table_owner,table_name, partition_name

/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats

from dba_indexes

where owner not in (‘SYS’,’SYSTEM’)

order by owner, index_name

/

PROMPT Partitioned Indexes

select index_owner, index_name, partition_name, last_analyzed, global_stats

from dba_ind_partitions

where index_owner not in (‘SYS’,’SYSTEM’)

order by index_owner, index_name, partition_name

/

spool off

To check last collected stats for database

a) Show the current stats history configuration:

select dbms_stats.get_stats_history_availability from dual;

b) Show the current history level:

select dbms_stats.get_stats_history_availability from dual;

c) Disable automatic purge ( -1 = statistics history never purged by autopurge):

exec dbms_stats.alter_stats_history_retention(-1);

d) Purge by hand running successively:

exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

e) Show the new history level

select dbms_stats.get_stats_history_availability from dual;

This should show that the GET_STATS_HISTORY_AVAILABILITY is indeed equal to sysdate – (n-x).

After they are purged, set the desired retention.

It is recommended to set &days to purge little by little.