How to check Gather stats on a table in Oracle

Many times, we might want to check if gather stats ran on the table and when it ran last time. We can find this query dba_tables or user_tables view. let’s see a few queries related to this

How to check gather stats on a table/how to check last gather stats on table in Oracle

The following query can be run to find the detail of stats on a table

col num_rows format 999999990 heading 'ROWS'
col avg_row_len format 9990 heading 'AVG|ROW|SIZE'
col blocks format 9999990 heading 'USED|BLOCKS'
col empty_blocks format 999990 heading 'EMPTY|BLOCKS'
col avg_space format 9990 heading 'AVG|FREE|SPACE'
select owner, table_name, num_rows, avg_row_len,
blocks, empty_blocks, avg_space,last_analyzed
from dba_tables
where owner='&table_owner' and table_name ='&table_name'
/

Here num_rows is the number of rows in the table
avg_row_len is the average row length in bytes
blocks is the number of blocks allocated to the table below the high watermark
empty_blocks is the number of empty blocks which never have any rows
last_analyzed is the date when the last gather stats ran on the table

If you want the last_analyzed details to the hour and minute, then we can use the below query to set the NLS

alter session set nls_date_format = 'mm-dd-yyyy hh24:mi:ss';

We can use dba_tab_statistics view to query the table stats

col num_rows format 999999990 heading 'ROWS' 
col avg_row_len format 9990 heading 'AVG|ROW|SIZE' 
col blocks format 9999990 heading 'USED|BLOCKS' 
col empty_blocks format 999990 heading 'EMPTY|BLOCKS' 
col avg_space format 9990 heading 'AVG|FREE|SPACE' 
select owner, table_name, num_rows, avg_row_len, blocks, empty_blocks, avg_space,last_analyzed
from dba_tab_statistics
where owner='&table_owner' and table_name ='&table_name';

How to check for gather stats on all the tables in a query

You can use the below query

set line 1000
set verify off
col owner format a15
col object_name format a25
col object_type format a12
col "LAST ANALYZED" format a13
select do.OWNER,do.OBJECT_NAME,OBJECT_TYPE, decode (OBJECT_TYPE,'TABLE' , (Select LAST_ANALYZED from dba_tables where owner=do.owner and TABLE_NAME=do.object_name) , 'INDEX' , (Select LAST_ANALYZED from dba_indexes where owner=do.owner and INDEX_NAME=do.object_name) , 'UNKNOWN') "LAST ANALYZED",STATUS from DBA_OBJECTS do where OBJECT_TYPE in ('TABLE','INDEX') and (OWNER,OBJECT_NAME) in (select OBJECT_OWNER,OBJECT_NAME from V$SQL_PLAN where HASH_VALUE=&1) ;

Query to check the gather stats for the whole schema

col num_rows format 999999990 heading 'ROWS' 
col avg_row_len format 9990 heading 'AVG|ROW|SIZE' 
col blocks format 9999990 heading 'USED|BLOCKS' 
col empty_blocks format 999990 heading 'EMPTY|BLOCKS' 
col avg_space format 9990 heading 'AVG|FREE|SPACE' 
select owner, table_name, num_rows, avg_row_len, blocks, empty_blocks, avg_space,last_analyzed from dba_tables where owner='&schema_name' ;

I have given the dba_tables example here but we can use any of these views
DBA_TABLES
USER_TABLES
ALL_TABLES

we can now view CDB_TABLES starting with 12c with the container database. In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables

I hope you like this article on gathering stats and how to check last gather stats on the table in Oracle. Please do provide the feedback

Related Articles

How to check Index statistics in Oracle: In this post, we find out the index statistics, what is meaning of each of those statistics
How to check column statistics in Oracle
Optimizer statistics preferences in Oracle
Histograms in Oracle