Re: Stats on Huge Partitioned Table and ORA-01555

  • From: "Mladen Gogala" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "mgogala@xxxxxxxxx" for DMARC)
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 26 May 2015 21:15:50 -0400

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

--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2015