Re: Gather stale does not recognize partitioned tables

Hi,
 I ran into the same issue.
 there are some known bugs regarding this.

 The workaround for me was to run the analyze several times (i.e. first time  analyzes the first partition, second time second partition etc...)

 I believe, there is a patch, but you need to check with oracle support to  confirm that you are running into the same issue anyway.

Karsten  

gail_at_alberich.Stanford.EDU (Gail Binkley) wrote in message news:<asgm82$p52$1_at_news.Stanford.EDU>...
> I've recently started the automatic gathering of statistics using
> DBMS_STATS.GATHER_SCHEMA_STATS with the 'GATHER STALE' and 'GATHER
> EMPTY' options. I also use the 'LIST STALE' and 'LIST EMPTY' options
> to create a log file. This new system has worked very well for all
> our tables, execpt partitioned tables. LIST STALE does not seem to
> recognize when a table is partitioned. It gathers statistics on all
> partitions if the "table" is deemed stale, not just the partitions
> that are stale.
>
> This seems totally contrary to the purpose of both partitioned tables
> (manage a large table in smaller pieces) and the 'GATHER STALE'
> option (only gather statistics when necessary).
>
> I have used GATHER_TABLE_STATS to gather statistics on individual
> partitions. However, I was hoping to use the built in algorithm
> associated with GATHER STALE to simplify and to minimize the
> statistics that are gathered on our rather large partitioned table
> (nearly 700 million rows and growing).
>
> The database is running Oracle 8.1.7.3 (soon to be upgraded to
> 8.1.7.4) on a Sun 4500 server.
>
> Has anyone wanted to or tried to 'GATHER STALE' statistics on a
> partitioned table? I've looked in various 3rd party books and in the
> Oracle online documentation, but haven't found anything that
> specifically addresses this situation. Any suggestions or information
> would be greatly appreciated.
>
> Gail Binkley
> Stanford University
> gail_at_genome.stanford.edu
Received on Tue Dec 03 2002 - 05:39:31 CST