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).

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: