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');
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