RE: ORACLE automatic update statistics better??

From: Kellyn Pedersen <>
Date: Thu, 19 Aug 2010 08:53:57 -0700 (PDT)
Message-ID: <>

I have a number of tables like this in a few of my larger (multi-TB) warehouse/marts.  I have actually left the automatic OCM owned job to collect staleness of objects to the DBA_TAB_MODIFICATIONS table and then base cron jobs that create a stats script dynamically to collect stats based off the objects collected in the modifications table.  I use an outrageously small sample size,  (sometimes as small as .00001) and then lock stats on tables that are likely to have instability in explain plans due to dynamic sampling, where Oracle making the decision on the best plan isn't actually the best choice, (I'm not going to come out and blame the design or the logic in the code here... :))  

I'll be honest-  histograms are rarely my friend in this environment, where at previous companies I loved them in most of my databases.  I have huge, simple tables with monstrous amount of partitions and I find I run smoother with simpler stats, that means "indexed columns" or "all columns size 1". 

This has been exceptionally successful, even in an environment scenario that if I fully explained it, (darn confidentiality!) most would say is darn near impossible!

Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.

"Go away before I replace you with a very small and efficient shell script..."

  • On Thu, 8/19/10, Powell, Mark <> wrote:

From: Powell, Mark <> Subject: RE: ORACLE automatic update statistics better?? To: "oracle-l" <> Date: Thursday, August 19, 2010, 7:40 AM

Considering manually generating statistics for these tables using a sample size then locking the statistics so the Oracle provided job does not regenerate statistics for these tables.  You can write a script that unlocks the statistics, re-calculates the statistics, and relocks the statistics using parameters you have determined work better for these specific tables.

From: [] On Behalf Of Sandra Becker Sent: Wednesday, August 18, 2010 4:58 PM To: oracle-l
Subject: Re: ORACLE automatic update statistics better??

We ran into problems with the auto stats job on a few of our large tables that can grow by 10 percent of more in a day or two.  It takes several hours to get the stats on the largest table and caused significant performance problems for our customers.

Transzap, Inc.


Received on Thu Aug 19 2010 - 10:53:57 CDT

Original text of this message