RE: Run stats on the basis of data increase
Date: Wed, 3 Jun 2009 16:14:07 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F17DCE089_at_AAPQMAILBX02V.proque.st>
Niall,
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'.
-Mark
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield
Sent: Wednesday, June 03, 2009 3:40 PM
To: SMurphy_at_uamail.albany.edu
Cc: kennaim_at_gmail.com; saad4u_at_gmail.com; oracle-l_at_freelists.org
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.
regards
On Wed, Jun 3, 2009 at 6:41 PM, Stephen T Murphy <SMurphy_at_uamail.albany.edu<mailto:SMurphy_at_uamail.albany.edu>> 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: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Kenneth Naim Sent: Wednesday, June 03, 2009 12:49 PM
To: saad4u_at_gmail.com<mailto:saad4u_at_gmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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.
Ken
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Saad Khan
Sent: Wednesday, June 03, 2009 12:27 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
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
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 03 2009 - 15:14:07 CDT