RE: Run stats on the basis of data increase

From: Bobak, Mark <>
Date: Wed, 3 Jun 2009 16:14:07 -0400
Message-ID: <>


Beware that the default stats collection job likes to use method_opt=>'for all columns size auto', which lets Oracle decide which columns get histograms. In many circumstances, this is less than optimal. There's something to be said for disabling the default stats job and creating your own that does method_opt=>'for all columns size 1'.


From: [] On Behalf Of Niall Litchfield Sent: Wednesday, June 03, 2009 3:40 PM
Cc:;; Subject: Re: Run stats on the basis of data increase

Indeed it does apply to both releases - and 11g too, and it appears from my investigations to work exactly as the dbms_stats.gather_stale option does, that is on the 10% basis that the OP originally asked for. So not only is there a feature, but it's almost certainly running. If only all issues were this simple.

On Wed, Jun 3, 2009 at 6:41 PM, Stephen T Murphy <<>> wrote: Oracle 10gR2 (and I think 10gR1) does this right out of the box. When you create a 10g database (or upgrade an earlier one) a DBMS_Scheduler job is created and scheduled to run sys.gather_stats-prog on a daily basis. It does stats for both missing and stale stats.

  • *---------------------------------------------------------------------------* Stephen T. Murphy Manager, Database and Technical Support ITS - University Applications Development MSC-100 The University at Albany, S.U.N.Y. Albany, New York 12222 Phone: (518) 437-4523 Fax: (518) 437-4540 MailTo: SMurphy_at_UAMail.Albany.EDU<mailto:%20SMurphy_at_UAMail.Albany.EDU> AIM: SMurphy199

From:<> [<>] On Behalf Of Kenneth Naim Sent: Wednesday, June 03, 2009 12:49 PM
To:<>;<> Subject: RE: Run stats on the basis of data increase

You can schedule a regular (weekly/daily etc.) job to have stats gathered on objects that have changed more than 10% by using dbms_stats.gather_stale.


From:<> [<>] On Behalf Of Saad Khan Sent: Wednesday, June 03, 2009 12:27 PM
To:<> Subject: Run stats on the basis of data increase

Hi Fellows,

I have oracle 10g running for an application on SUSE linux 10.

I know I can schedule on the timing basis, but is there a way in Oracle to generate/gather stats on the basis of data growth? I mean, can I schedule the stats generation on the basis of the amount of data that is increased. Say its run if the data increases to 10% of what it is, right now. I didnt see any option myself but I just thought there may be anything that I'm unaware of.

Thanks for your help.


Niall Litchfield
Oracle DBA

-- Received on Wed Jun 03 2009 - 15:14:07 CDT

Original text of this message