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

Lutz,

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
from
(select num_rows from dba_tables
where owner='&&v_owner' and table_name='&&v_table') a,
(select sum(INSERTS+UPDATES+DELETES) mods
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.

Cheers,

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
with TABLE MONITORING?

Other related posts: