Wednesday, 21 September 2011

SQL Query to indentify the stale tables

During the data conversion activity, we always had a performance penalty and had to run the gather statistics for a particular schema or for all schemas. This was taking a long time. We identified that instead of running gather stats on schema, we can actually analyze the affected tables. The SQL given below will list out the stale tables present in the instance.

SELECT dt.owner || '.' || dt.table_name
  FROM all_tables dt,

       all_tab_modifications atm
 WHERE dt.owner = atm.table_owner
   AND dt.table_name = atm.table_name
   AND dt.num_rows > 1000
   AND (ROUND(((atm.updates + atm.deletes + atm.inserts)/dt.num_rows)* 100) > 1
       OR inserts > 1000
       OR deletes > 1000
       OR updates > 1000
       )
  AND table_owner NOT IN('SYS','SYSTEM');

No comments:

Post a Comment