In this article, we will explore how to check the last analyzed date of tables and indexes in Oracle, or in other words, how to determine when the statistics were last gathered. “check last gather stats in oracle”.
Table of Contents
Check last gather stats in oracle
Here are the complete steps to retrieve the latest statistics gathering information in an Oracle database.
You also learn: How to install, configure, and use Statspack in Oracle
Read about Database tuning
Check the last analyzed for tables
col owner for a20
col table_name for a30
set lin 500 pagesize 600
select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not like '%SYS%'
order by owner,table_name;
OR
select owner,min(last_Analyzed), max(last_analyzed) from dba_tables group by owner order by 1;
Output example of above query
For Partitioned tables
col table_owner form a15
col PARTITION_NAME for a25
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;
Output:
Check the last analyzed for Indexes
col INDEX_NAME for a30
select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by owner, index_name;
OR
SELECT table_name, index_name, to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_INDEXES;
Output:
OWNER INDEX_NAME LAST_ANAL GLO
-------------------- ------------------------------ --------- ---
HR COUNTRY_C_ID_PK 27-FEB-24 YES
HR DEPT_ID_PK 27-FEB-24 YES
HR DEPT_LOCATION_IX 27-FEB-24 YES
HR EMP_DEPARTMENT_IX 27-FEB-24 YES
HR EMP_EMAIL_UK 27-FEB-24 YES
HR EMP_EMP_ID_PK 27-FEB-24 YES
For Partitioned Indexes
col index_owner form a15
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;
Check the last stats gathered for the Database
The following command to check the current stats history
select dbms_stats.get_stats_history_availability from dual;
Output:
GET_STATS_HISTORY_AVAILABILITY
------------------------------------------
19-MAR-24 10.32.49.000000000 PM -04:00
How to disable automatic statistics history purge
exec dbms_stats.alter_stats_history_retention(-1);
The -1 = statistics history never purged by autopurge
How to purge statistics history manually
exec dbms_stats.purge_stats(sysdate-&days);
using &days = n, n-1, n-2, …, n-x
I hope you learn something from this post “check last gather stats in oracle” if yes please write in comment box.