Statistics

Statistic is crucial for Oracle Database to choose the correct Cost Based Optimization. Statistic should always be up to date.

 

Gathering statistics

For the entire Schema

exec dbms_stats.gather_schema_stats(‘SCOTT’,estimate_percent => 100, cascade=>TRUE, degree => 4);

exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’,cascade=>TRUE);

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

GATHER_DATABASE_STATS

GATHER_SYSTEM_STATS

 

It is possible to calculate statistics on one database instance and to copy it to the other (without copying the data itself).