Re: Storing old stats

From: <breitliw_at_centrexcc.com>
Date: Tue, 07 May 2013 13:00:57 -0600
Message-ID: <20130507130057.t5dzmhzaxw0o48ss_at_webmail.telushosting.com>

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_at_gmail.com> wrote: List,
> 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.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

  --
Wolfgang Breitling
Centrex Consulting Corporation

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

Received on Tue May 07 2013 - 21:00:57 CEST