Oracle 23c New Features for DBA'sEver, interested in looking for statistics staleness for each table including how much percent they stale are. The following query helps you to get you the details.
This query filter out the staleness for those tables which having more than or equal to 10%, you can adjust accordingly.
However, you will need to understand how this details are populated by reading this post, http://db.geeksinsight.com/2013/01/03/stats-change-statistics-stale-percentage-in-11g/
To change the database level statistics preferences for staleness , you need to do following.
SQL> Select dbms_stats.GET_PREFS('STALE_PERCENT') from dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT')
---------------------------------------------
10
SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT',9);
Back to the point, here is the query,
set heading on
set pages 1000
set lines 100
SELECT DT.OWNER,
DT.TABLE_NAME,
dtm.inserts,
dtm.deletes,
dtm.updates,
dtm.truncated,
dt.last_analyzed,
dt.num_rows,
ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
FROM DBA_TABLES DT, DBA_TAB_MODIFICATIONS DTM
WHERE DT.OWNER = DTM.TABLE_OWNER
AND DT.TABLE_NAME = DTM.TABLE_NAME
AND NUM_ROWS > 0
--AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
AND DT.TABLE_NAME='TSTS076'
ORDER BY 9 desc;
Click to Enlarge the screenshot
Hope it helps
Geek DBA
Follow Me!!!