Gather table statistics(using dbms_stats)

World Scenario
If we have had a big table and then we decided to delete a big amount of rows from this table.Oracle “knew” that it was a big table on which a query was slow and now it is a small table on which a query should be faster than the old one,BUT oracle still needs more time to execute a query…
The question is why???
BECAUSE oracle still has the old statistics on this table ,which means that oracle still thinks that it is a big table…And the solution is to gather new statistics manually:

begin
    dbms_stats.unlock_table_stats(ownname =>'username',
                                  tabname=>'table_name);
    dbms_stats.gather_table_stats(ownname =>'username',
                                  tabname=>'table_name',
                                  degree => 4,
                                  cascade => true);
end;
Note:
dbms_stats.unlock_table_stats
Table statistics can be locked..This is a good opportunity to be sure that estimated statistics will not be changed.So to estimate new statistics you should unlock it.
dbms_stats.gather_table_stats
Here, not easy understandable option is just cascade.
Cascade->Indicates oracle to gather statistics on the indexes also for this table.
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: