Re: how to influence the threshold for optimizer statistics to become stale

  • From: "David Taft" <oradbt054@xxxxxxxxx>
  • To: lutz.hartmann@xxxxxxxxx
  • Date: Wed, 23 May 2007 16:48:09 -0400


Just got around to reading this message. As Christian said, the 10% appears
to be hard-coded, but I have thought about this issue myself in the past.  I
thought to calculate the percent for the table I wanted to have a different
threshhold.  Example:

select b.mods/a.num_rows
(select num_rows from dba_tables
where owner='&&v_owner' and table_name='&&v_table') a,
from dba_tab_modifications
where table_owner='&&v_owner'
and table_name='&&v_table') b;

This would be selected into a variable like v_stale within a PL/SQL job.
If v_stale is greater than say .05 (or whatever you desire), then gather
stats.  The values in dba_tab_modifications are automatically reset to zero
by oracle each time you gather new stats.  Not a lot of detail here, but I
hope you get the idea.


David Taft

On 5/21/07, Lutz Hartmann <lutz.hartmann@xxxxxxxxx> wrote:

 Does anyone of you know about a way
how to influence the threshold for optimizer statistics becoming stale

Other related posts: