Gather table statistics(using dbms_stats)
December 20, 2010 Leave a comment
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. |