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




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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: