Re: Storing old stats

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Wed, 8 May 2013 07:55:57 -0400
Message-ID: <CAGzKQQfWjTUn3M0HG1PTOmgU86L0q-c+dfqkOhma-5s0Nrb9kw_at_mail.gmail.com>

Just created this blog entry with views for cbo stats versions: http://carlos-sierra.net/2013/05/08/browsing-schema-objects-cbo-statistics-versions/

On Tue, May 7, 2013 at 4:56 PM, Orlando L <oralrnr_at_gmail.com> wrote:

> Thanks again. I did some googling on that and found this link that tells
> the names of the tables where the history statistics are stored, for anyone
> interested:
>
> http://blog.psftdba.com/2009/06/oracle-10g-statistics-history-retention.html
> (table names are WRI$_OPTSTAT%HISTORY )
>
>
> On Tue, May 7, 2013 at 3:00 PM, <breitliw_at_centrexcc.com> wrote:
>
> > The statistics history tables exist in 10g ( R2 ). Don't have a 10.1
> > database handy to check.
> >
> >
> > On Tue, 7 May 2013 14:53:11 -0500, Orlando L <oralrnr_at_gmail.com> wrote:
> >
> > Thank you Wolfgang. I thought the old stats being available for 31 days
> > was a 11g feature not in 10g.
> >
> >
> > On Tue, May 7, 2013 at 2:00 PM, <breitliw_at_centrexcc.com> wrote:
> >
> >> If you use dbms_stats to gather statistics the prior statistics are
> >> automatically save in statistics history tables and kept for n days (
> 31 by
> >> default, is configurable ). I am not a big fan of this - if statistics
> for
> >> a table / index do not get gathered for more than 31 days ( e.g.
> because of
> >> too little activity ) the entire statistics history is purged and if the
> >> next gather results in an undesirable plan you can't revert back. I much
> >> prefer creating a stattab table and exporting statistics "manually"
> either
> >> at regular intervals, i.e. prior to a dbms_stats.gather, or prior
> to/after
> >> certain events.
> >> --
> >> Wolfgang Breitling
> >> Centrex Consulting Corporation
> >>
> >>
> >>
> >
> >
> >
> > --
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> >
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cheers -- Carlos Sierra
http://carlos-sierra.net/


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

Received on Wed May 08 2013 - 13:55:57 CEST