i have configured automatic statistics gathering job in my database(18.104.22.168) to calculate object statistics with stale % a>= 10. I am having a doubt how Oracle calculates stale percentage of an object. I did search in OTN discussion and found the below querry
DT.TABLE_NAME, 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 OWNER IN ('OWNER_NAME’') ORDER BY 3 desc;
but the query is displaying me output with stale % more than 100 and also statistics is not gathered for few of the tables listed by this query.