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