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
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:
