************************************************************************************************* * Description: Checking percent of rows applied with insert/update/delete and last analyzed time * Date: 05:10 PM EST, 11/15/2017 ************************************************************************************************* <1> Oracle setup default threshold for percentage of rows in a table got applied with DML, and starting re-gather statistics: | |__ SQL> SELECT * FROM ( SELECT m.table_owner , m.table_name , t.last_analyzed , m.inserts , m.updates , m.deletes , t.num_rows , (( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END)*100 "Change Factor" FROM dba_tab_modifications m , dba_tables t WHERE t.owner = m.table_owner AND t.table_name = m.table_name AND m.inserts + m.updates + m.deletes > 1 AND m.table_owner not in('SYS','APEX_030200','SYSMAN','MDSYS','OLAPSYS','CTXSYS','XDB','ORDDATA','ORDSYS','EXFSYS','DBSNMP','SYSTEM','WMSYS','SYSTEM','OUTLN','ORDDATA') AND trunc(t.LAST_ANALYZED) < (sysdate -7) ORDER BY "Change Factor" DESC ); TABLE_OWNER TABLE_NAME LAST_ANAL INSERTS UPDATES DELETES NUM_ROWS Change Factor ------------------------------ ------------------------------ --------- ---------- ---------- ---------- ---------- ------------- RDSCORE CLIENT_TCORE 26-SEP-17 17 60 0 1222 6.30114566 RDSCORE LEGAL_ORG_PAYMENT 01-SEP-17 1045 1046 24 33639 6.28734505 RDSCORE ASPPECRODUCTMENUITEMS 17-MAR-16 17 40 0 922 6.18221258 RDSCORE CUSTOMERRESOURCES 06-OCT-17 446 1316 0 28741 6.1306148 ERICKCORE CONTEXTS 17-MAR-16 9 1 0 164 6.09756098 RDSCORE PPACASETTINGS 05-APR-17 7 22 0 476 6.09243697 RDSCORE CRMPOST_TOG_CCOUNTLINE 27-FEB-17 41 41 0 1349 6.07857672 ERICKCORE ROLES 14-JUN-17 72 0 25 1638 5.92185592
Your Comments