We are using goldengate as a fallback solution for databases migrated through datapump. Frequently I can see delete statements slow and performing multiple full table scans due to non indexed columns contained in the where clause of delete statements.
Then I often go through the following loop of steps to create temporary indexes (for only as long as the fallback replication is required):
- Stop replicat
- Analyze the statement as well as the columns in the where clause.
- Analyze the selectivity of those columns:
select table_name, column_name, num_distinct from dba_tab_col_statistics where table_name = 'XYZ' order by 3 desc;
- Create the index.
- Restart replicat.
But this happens many times... I understand I could know first hand what tables would face this problem by runnning Goldengate Health Check Profile Script, but then again, I'd see it and would need to start creating indexes one at a time to support replication.
Is there a better approach to it?
Thanks.