Re: DBMS_STATS gather stale option

From: Mark B. Wallace <no.spam_at_verizon.net>
Date: Tue, 29 Oct 2002 06:15:58 GMT
Message-ID: <yIpv9.29868$iV1.19681@nwrddc02.gnilink.net>

Gail Binkley wrote:
>
> I use DBMS_STATS to gather statistics for all tables in an Oracle
> 8.1.7.3 database. I would like use the GATHER STALE option of
> GATHER_SCHEMA_STATS to automatically collect statistics. However, the
> tables in this database vary widely in size and there is also a
> partitioned table.
>
> If I execute DBMS_STATS.GATHER_SCHEMA_STATS with the GATHER STALE
> option, will it gather statistics based on how statistics were
> gathered previously on each table (compute, estimate, and partition),
> or will it compute statistics on all tables no matter the type or size
> of the table? There is no GATHER STALE option associated with
> GATHER_TABLE_STATS.
You ask two very different questions. One, for each table or partition in the specified schema, will it be targeted for gathering? This depends solely on whether or not it is considered to be stale. Two, if statistics are to be gathered, how will this be done? This depends on the second parameter to the procedure, ESTIMATE_PERCENT, and it will be the same for every object (table or partition) that is analyzed in this call to DBMS_STATS.

Please see the "Supplied PL/SQL Packages" manual for details.

Mark Wallace Received on Tue Oct 29 2002 - 00:15:58 CST