If you're finding that expanding projects, views, or packages in Workbench is slow after upgrading from Harvest 7.1 to SCM 12.x, taking some steps to tune your database might help improve performance.
If you face performance issues after migration from r7.1 to r12, the following steps have successfully solved performance issues in a few cases, so this might be worth a try. You should be careful with the first 2 steps as they might have a negative impact if you have already done advanced statistics or might negatively impact other applications that might be running on the same Oracle. Your DBA can review this and let you know.
- exec dbms_stats.delete_system_stats();
- exec dbms_stats.gather_system_stats();
- exec dbms_stats.delete_schema_stats('<harvestschema>');
- EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=> '<harvestschema>',
estimate_percent=>100 , method_opt => 'FOR ALL COLUMNS SIZE 1', cascade
=> true, no_invalidate => FALSE);
Running the above commands has brought performance up to an acceptable level for most operations in SCM. However they alone might not be sufficient in bringing the performance to top notch. Additional DB tuning might be required for some special cases.
*** Important Note *** Before running these commands, please have your DBA review them. The first two steps are very tricky. If your DBA is already using an advanced method for gathering system stats, then you should not run the first two steps at all. If the DBA has not done anything with system stats, then it is required to run them.