Gather stats example 2

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.