Statistic is crucial for Oracle Database to choose the correct Cost Based Optimization. Statistic should always be up to date.
For the entire Schema
exec dbms_stats.gather_schema_stats(‘SCOTT’,estimate_percent => 100, cascade=>TRUE, degree => 4);
exec dbms_stats.gather_index_stats(‘SCOTT’,’EMP_IDX’,estimate_percent => 100,degree => 4);
– cascade=>TRUE would also do index statistic
– degree tells how many CPUs will be used
200GB on degree 6 lasted approximately 1 hour
Other options for gathering statistics
It is possible to calculate statistics on one database instance and to copy it to the other (without copying the data itself).