Re: Stats on Huge Partitioned Table and ORA-01555
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Tue, 26 May 2015 21:15:50 -0400
Message-ID: <55651AC6.90902_at_yahoo.com>
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.
Date: Tue, 26 May 2015 21:15:50 -0400
Message-ID: <55651AC6.90902_at_yahoo.com>
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 http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 27 2015 - 03:15:50 CEST