Re: 9i Analyze Behavior (DBMS_STATS)

According to 'Oracle9i Supplied PL/SQL Packages and Types Reference' GATHER AUTO collects stats only when Oracle *thinks* those are not up-to-date. And it 'implicitly' determines when stats are to be collected. I don't know if this behaviour matches to that of GATHER STALE, per se, which is looking for 10% or more DML changes or truncate/reload.

HTH, Regards,
- Kirti

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.
>
> 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 - 22:49:47 CST