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_at_sysdba.ch> wrote:
>
> Does anyone of you know about a way
> how to influence the threshold for optimizer statistics becoming stale
> with TABLE MONITORING?
>

--
http://www.freelists.org/webpage/oracle-l