9i Analyze Behavior (DBMS

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: Thu, 27 Feb 2003 14:06:17 GMT
Message-ID: <07cb110f40bbc5383a956e6977ae4cde@news.teranews.com>

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.

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?

08:54:39 SQL>
08:54:39 SQL> -- default nomonitoring
08:54:39 SQL> create table t as
08:54:39   2  select * from all_objects;

Table created.

08:54:53 SQL>
08:54:53 SQL> exec dbms_stats.gather_schema_stats (              -
08:54:53 >      ownname          => 'BUCK',                      -
08:54:53 >      estimate_percent => dbms_stats.auto_sample_size, -
08:54:53 >      options          => 'GATHER AUTO',               -
08:54:53 >      method_opt       => 'for all columns size AUTO');

PL/SQL procedure successfully completed.

08:55:08 SQL>
08:55:08 SQL>
08:55:08 SQL> select to_char(last_analyzed, 'hh:mi:ss') from user_tables

where table_name = 'T';

TO_CHAR(

08:55:06

1 row selected.

08:55:08 SQL>
08:55:08 SQL> set pause on   -- wait a couple of minutes ----------
08:55:08 SQL>

08:57:54 SQL>
08:57:54 SQL> exec dbms_stats.gather_schema_stats (              -
08:57:54 >      ownname          => 'BUCK',                      -
08:57:54 >      estimate_percent => dbms_stats.auto_sample_size, -
08:57:54 >      options          => 'GATHER AUTO',               -
08:57:54 >      method_opt       => 'for all columns size AUTO');

PL/SQL procedure successfully completed.

08:57:55 SQL>
08:57:55 SQL>
08:57:55 SQL> select to_char(last_analyzed, 'hh:mi:ss') from user_tables

where table_name = 'T';

TO_CHAR(

08:55:06

1 row selected.

08:57:59 SQL>
08:57:59 SQL>
08:57:59 SQL>

Received on Thu Feb 27 2003 - 08:06:17 CST

9i Analyze Behavior (DBMS_STATS)