Re: 9i Analyze Behavior (DBMS_STATS)

On Thu, 27 Feb 2003 14:06:17 +0000, Buck Turgidson wrote:

> I am trying to understand the dbms_stats behavior. The docs appear to say
> that GATHER AUTO behaves like GATHER STALE in 8i, which of course needs
> monitoring turned on for the table.

Not quite. 8i had GATHER_STALE and also GATHER_EMPTY. GATHER_AUTO does both, refreshing statistics where they are stale, and computing them from scratch if it encounters a table with no statistics.

>
> If I create a table (default nomonitoring) analyze it, and then reanalyze it
> a couple of minutes later, LAST_ANALYZED is not updated, indicating that the
> 2nd analyze didn't do anything. I was expecting it, since the table is in
> nomonitoring. Can someone explain that?

Not sure what there is to explain. In your test, nomonitoring is set. Therefore, there can be no sense of 'stale' statistics (and you'd have to do some DML on the table for the previous statistics to be considered stale even if monitoring was set). And the 'gather_empty' component of the GATHER_AUTO procedure isn't going to get run, either, because you've got statistics on the table already.

Regards
HJR Received on Thu Feb 27 2003 - 13:03:18 CST