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.



About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email:
This entry was posted in Performance and Tuning. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s