RE: ORACLE automatic update statistics better??
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Aug 2010 08:53:57 -0700 (PDT)
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.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Thu, 8/19/10, Powell, Mark <mark.powell2@xxxxxx> wrote:
From: Powell, Mark <mark.powell2@xxxxxx>
Subject: RE: ORACLE automatic update statistics better??
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
--
Sandy
Transzap, Inc.
Other related posts:
