Re: purge of AWR historical tables

I am curious about the retention time that allowed this to happen (dbms_stats.get_stats_history_retention).

I haven't had to do this myself (or for a client), but with the possible change that I'd mark the indexes unusable (purely for convenience) rather than dropping them I think it's the only realistic option. The indexes are non-unique and global, and there are only check constraint on the tables, no foreign keys so there should be no referential integrity problems - there's only the question of whether the dbms_stats call that tries to populate the tables allows for unusable indexes (because when you drop partitions Oracle will try to update some stats tables, which means saving some stats to history ;) I suspect that most of the time of the purge relates to the maintenance of the global indexes since the code will drop the oldest partitions in order mainting the global index for each partition as it does so. (which means that a call to purge after dropping or marking unusable the indexes may be all you need to do, and then rebuild the global indexes, rather than writing your own code to drop partitions)

Thinking of possible threats:
What's going to happen in the future - do you have a lot of other OPTSTAT tables that go back that far in history or was it only the histogram (and column) stats that had the problem. Will a future purge still be very slow because it has a large history for TAB and IND stats if you set the retention to 31 days.
Do you have any partitioned tables with synopses that might do a huge amount of work in a future purge if you change the retention. There is a point in the purge process where a control table has to be updated with some details of the earliest expected date - so you may want to finish the process with a final purge in case something funny happens - better happen when you're watching rather than some arbitrary time in the future.

Regards
Jonathan Lewis

On Fri, 16 Apr 2021 at 09:37, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

>
> Hello
>
> At one project (Oracle 12.1) two of the AWR historical tables are
> occupying 1,8 TB.
>
> · WRIS_OPSTAT_HISTHEAD_HISTORY : 81GB
>
> · WRI$_OPSTAT_HISTGRM_HISTORY : 1,012TB with almost 16 billion
> of rows
>
> The last savtime from WRI$_OPSTAT_HISTGRM_HISTORY is from November 2018
>
> Based upon the first suggestion of Oracle support the team has started by
> running the purge stats procedure which they have canceled after 3 hours.
>
> Now the Oracle support is suggesting the following
>
> · Drop indexes for those two tables
>
> · Drop partitions older than 31 days
>
> · Recreate indexes
>
> Have you already been in such situations? If so, what strategy have you
> adopted to reduce the size of those two tables as quickly as possible?
>
> Thanks
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

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