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:

    dbms_stats.unlock_table_stats(ownname =>'username',
    dbms_stats.gather_table_stats(ownname =>'username',
                                  degree => 4,
                                  cascade => true);
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.
Here, not easy understandable option is just cascade.
Cascade->Indicates oracle to gather statistics on the indexes also for this table.

About Mariami Kupatadze
Oracle Certified Master Linkedin:

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 )

Facebook photo

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

Connecting to %s

%d bloggers like this: