Re: Stats on Huge Partitioned Table and ORA-01555

From: Mladen Gogala <>
Date: Tue, 26 May 2015 21:15:50 -0400
Message-ID: <>

On 05/26/2015 02:44 PM, Stefan Koehler wrote:
> - Are you already using incremental statistics?
> - Are you creating/gathering unnecessary histograms (default setting)? Gathering a lot of histograms cause high runtime of DBMS_STATS.
> - Are you already using parallel query for gathering statistics (parameter degree)? I guess not based on your provided SQL snap.
In addition to what Stefan has said, what is the stale percent? For such monster tables, the default 10% threshold should be crossed every once in a blue moon. Also, make sure to collect stats with BLOCK_SAMPLE set to TRUE. Don't leave stats on such a huge table to be collected automatically. Schedule it when there is the least activity on the database and set stale_percent to 100, to prevent the automatic job from ever touching it.
Mladen Gogala
Oracle DBA

Received on Wed May 27 2015 - 03:15:50 CEST

Original text of this message