Re: Storing old stats

  • From: breitliw@xxxxxxxxxxxxx
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, Orlando L <oralrnr@xxxxxxxxx>
  • Date: Tue, 07 May 2013 13:00:57 -0600

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. 

On Tue, 7 May 2013 13:45:45 -0500, Orlando L <oralrnr@xxxxxxxxx> wrote:
> One of our colleagues is suggesting that we create a backup table for
> exporting and storing statistics, in all databases, many of them 10g. The
> purpose of this backup table would be to store existing statistics when we
> have to tune queries; just save off the existing statistics of the tables
> involved in case if we have to revert back after new stats are collected. 
> This sounds like a good idea to me, but I want to know how people here do
> it. 
> I think in 11g there is a way to revert back the statistics within 24 hours. 
> Orlando. 
> --
> //

Wolfgang Breitling
Centrex Consulting Corporation


Other related posts: