Oracle Gather Table Stats

After a large load, update or delete of data in an Oracle database schema the query optimizer statistics can lead to sub-optimal performance. To gather statistics and improve performance run the following command:


begin
dbms_stats.gather_schema_stats(
     ownname          => 'SCHEMA',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt       => 'for all columns size repeat',
     degree           => 8
   );
  end;


How many times have I seen this make a huge performance difference after a massive data load!
Make sure this is set as a regular job to keep the statistics up to date