Re: Usage of the dbms_stats.gather_database_stats() procedure

You said you want to gather stats while the database is active...

There's a 'no_invalidate' parameter which you may want to set to true.  If you use the default, false, all dependant sql will be invalidated, so you may get lots hard parses after gathering stats on a table referenced by lots of queries. It might be better for you to slowly have dependant sql reparsed with the new stats as it's aged out and brought back into the shared pool. (Unless you're one of the 2% of Oracle sites using the shared pool properly, and your sql doesn't age out...)

Also perhaps *not* gathering stats in parallel would be a good way to minimise impact on your user's response times.

Malcolm.

On 14/02/06, Anand Rao <panandrao_at_gmail.com> wrote:
> Hi,
>
> Just like to add that,
>
> 1) Gather histograms only if you are using literal SQL. i.e, if you are
> using Bind Variables in your queries, then it doesn't make much sense to
> gather histograms because CBO will NOT use it after the first lookup.
>
> 2) Why don't you use degree=>n in your gather_table_stats or
> gather_index_stats? that can help run your stats gathering faster. If you
> have lots of CPUs then you should utilise atleast 70% of it for gathering
> stats during the weekends.
>
> 3) spawn multiple gather_table_stats jobs and see how it runs.
>
> regards
> anand
>
>
> On 14/02/06, Jared Still < jkstill_at_gmail.com> wrote:
> >
> >
> >
> > On 2/13/06, Orr, Steve <sorr_at_rightnow.com> wrote:
> > >
> > >
> > >
> > > Any other stats maintenance recommendations?
> >
> > Consider using block_sample => true.
> >
> > Otherwise your samples may read much more of the database than you
> intended.
> >
> > --
> > Jared Still
> > Certifiable Oracle DBA and Part Time Perl Evangelist
> >
>
>

--
http://www.freelists.org/webpage/oracle-l