Re: gather stale and Histograms

From: Geomancer <pharfromhome_at_hotmail.com>
Date: 7 May 2003 14:51:24 -0700
Message-ID: <cf90fb89.0305071351.5ae6ec4f@posting.google.com>

Great question!

Because all statistics will become stale quickly in an OLTP database, we must remember the rule for gather stale is > 10% row change (based on num_rows at statistics collection time). Hence, almost every table except read-only tables will be re-analyzed with the gather stale option.

Hence, the gather stale option is best for system that is largely read-only. For example, if only 5% of the database tables get significant updates, then only 5% of the tables will be re-analyzed with the "gather stale" option.

I use the skewonly option for the first time, and follow-up with the repeat option. As I add new tables I skewonly for their first analysis.

Here is my weekly re-analyse:

begin

   dbms_stats.gather_schema_stats(

      ownname => 'SCOTT', 
      estimate_percent => dbms_stats.auto_sample_size, 
      method_opt=>'for all columns size repeat', 
      cascade=>true,
      degree=7

   );
end;
/

Hope this helps . . . Received on Wed May 07 2003 - 16:51:24 CDT