Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance impact of MONITORING and GATHER_STALE

Re: Performance impact of MONITORING and GATHER_STALE

From: Stephen Barr <>
Date: Wed, 16 Mar 2005 11:25:44 +0000 (GMT)
Message-ID: <>

We've actually gone down the route of nor gathering statistics at all on the majority of our tables once they have been "baselined".

This is a DSS environment and our structures mainly fall in to three categories -

  1. Partitioned, time series
  2. Non-partitioned, time series
  3. Non-partitioned, non-time series

For the partitioned tables we do analyze the most recent partition on a nightly basis post ETL. The global statistics on the table are updated through a package we've put together to update specific stats such as num_rows, num_blks, null_cnt, max/min values, etc.

For the other tables we have a few different requirements, but the process is similar - we manually update the stats using this package and the data from the ETL process.

All of our stats are also under change control, so we backup the statistics each night, just in case, and keep a months worth of statistics off-line in a change management program.

Histograms are collected and refreshed only when required. The profile of our skewed data doesn't usually change, so we create the histogram once and then leave it alone until it is identified as a problem through pro-active monitoring.

Our analyze run now completes in ~10 minutes on our 4TB DSS. I suppose the main question may be WHY take this approach? Well, we have a small subset of our data being queried by the majority of our users...this is recent data (last 6 months). However, we have 8 years of data in the warehouse. Most of the data was migrated from an older source system and is therefore has a very different profile from the data being loaded from the new source systems.

Essentially, we need to skew the statistics towards the data we will actually be querying...something we couldn't do with traditional gather_stale options. We completed this during a "baselining" period.

Of course there are disadvantages to this approach - probably a little extra maintenace, much more proactive monitoring is required and the statistics will undoubtedly slowly get out of synch and require manual intervention...but the benefits in our situation I believe out-weigh the disadvantages.

I would be interested to hear from anyone else who has tried this approach - or anyone who thoroughly disagrees!

Many thanks,


Send instant messages to your online friends

Received on Wed Mar 16 2005 - 06:29:34 CST

Original text of this message