How to check Stale statistics in Oracle Database

Statistics can become stale when the data in the table changes substantially. Up-to-date statistics are important to generate good execution plans

How does Oracle decide if the stats have become stale

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10% of NUM_ROWS from dba_tables:

The parameter setting required to Track the table changes

• Prior to Oracle 10g, the automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on the table.
• Depending on the MONITORING flag, the GATHER_STATS_JOB job collected “GATHER EMPTY” and “GATHER STALE” on the flagged objects.
• In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored. Table-monitoring feature is now controlled by the STATISTICS_LEVEL parameter.
• When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.
• When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.
• By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled. It is strongly recommended to set STATISTICS_LEVEL to TYPICAL in 10g and above
• By setting this parameter, Oracle tracks the approximate number of INSERT, UPDATE, and DELETE operations for the oracle table since the last time statistics were gathered. This information on “changes made” is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). The data dictionary information is made visible through the views: DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, and USER_TAB_MODIFICATIONS.
• Oracle uses these views to identify tables that have stale statistics.
• Whenever there is a 10% change in data in a table, Oracle considers its statistics to be stale.

How to check Stale statistics in Oracle

The below PLSQL procedure or sql query finds out all the tables in the SCOTT schema which is stale stats

SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT', objlist=>ObjList, options=>'LIST STALE');
FOR k in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(k).ownname || '.' || ObjList(k).ObjName || ' ' || ObjList(k).ObjType || ' ' || ObjList(k).partname);
END LOOP;
END;
/

or

select table_name,owner 
from  dba_tab_statistics 
where stale_stats = 'YES' or stale_stats is null  and owner='SCOTT';

The below sql can also be used to find out inserts, updates, deletes

select u.TIMESTAMP,
t.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(d.num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
,'Null Value in USER_TAB_MODIFICATIONS')
) percent
from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
and d.owner = '&Owner'
and (u.inserts > 3000 or u.updates > 3000 or u.deletes > 3000)
order by t.last_analyzed
/

If you want to run this on the whole database

SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(objlist=>ObjList, options=>'LIST STALE');
FOR k in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(k).ownname || '.' || ObjList(k).ObjName || ' ' || ObjList(k).ObjType || ' ' || ObjList(k).partname);
END LOOP;
END;
/

or
select table_name,owner 
from  dba_tab_statistics 
where stale_stats = 'YES' or stale_stats is null;

If you want to see the tables where statistics are empty, we can use them below

SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(objlist=>ObjList, options=>'LIST EMPTY');
FOR k in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(k).ownname || '.' || ObjList(k).ObjName || ' ' || ObjList(k).ObjType || ' ' || ObjList(k).partname);
END LOOP;
END;
/

or
select table_name,owner 
from  dba_tab_statistics 
where stale_stats is null;

Now once you find the tables list, you can generate statistics on these tables.

exec dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME');

We can also run the below command to generate stats on all the stale objects in the schema

exec dbms_stats.gather_schema_stats(ownname => '<schema name>', cascade => TRUE, options => 'GATHER AUTO');

Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference. This can be set globally using DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS.

How to check stale statistics manually

Check the stats of the table

select owner, table_name, num_rows,blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
from dba_tables
where table_name = '<table name>' ;

Compare the statistical data with real data in the table

select count(1) from TABLE_NAME

Check the stats for partition:

select owner, table_name, partition_name, subpartition_name, num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len, stale_stats
from dba_tab_statistics where
table_name = '<table name>' and partition_name = '<partition name>';

Compare the statistical data with real data in the table, such as

select count(1) from TABLE_NAME partition (PARTITION_NAME);

Check the stats of the columns:

select owner,table_name,column_name,num_distinct,low_value,high_value,num_nulls,num_buckets,last_analyzed from dba_tab_cols
where table_name = '<table name>';
select * from dba_tab_col_statistics where table_name = '<table name>';
select * from dba_tab_columns where table_name = '<table name>';

Compare the statistical data with real data in the table, such as

select distinct(COLUMN_NAME) FROM TABLE_NAME ;

Hope you like this content on How to check Stale statistics in Oracle

Related Articles

ora-38029: object statistics are locked
ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)
Gathering Statistics in Release 11i and R12
Incremental Statistics Gathering in 11g
How to set Table Monitoring in Oracle and Relationship with STATISTICS_LEVEL