Re: Storing old stats

  • From: Orlando L <oralrnr@xxxxxxxxx>
  • To: centrex@xxxxxxxxxxxxx
  • Date: Tue, 7 May 2013 15:56:54 -0500

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@xxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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
>
>
>


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


Other related posts: