The idea of dropping the indexes, the dropping old partitions, and then
rebuilding the indexes seems like good advice especially if you want to get the
job done in the quickest time frame. Before I did the preceding, I would like
to know how long it takes to drop a partition and how many partitions, there
are to be dropped as the total required time might be beyond what I would want
to have to work.
The only alternate though would be to run the purge on just the oldest data,
perhaps only the oldest week or month, at a time and just keep advancing the
purge date till all the old data is gone. This would take a while but as long
as each purge had no noticeable impact that is usually my preference for
purges. That is, I do not normally care how long the purge takes as long as
the process does not have a negative performance impact (including on UNDO
utilization).
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Mohamed Houri <mohamed.houri@xxxxxxxxx>
Sent: Friday, April 16, 2021 4:36 AM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: purge of AWR historical tables
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<https://clicktime.symantec.com/3Dzod58HNGm9RaDwqLWCMst7Vc?u=http%3A%2F%2Fwww.hourim.wordpress.com%2F>
Let's Connect -
<https://clicktime.symantec.com/3C2BrtCbMn1SpRJ6gj4jkYm7Vc?u=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F>
Linkedin
Profile<https://clicktime.symantec.com/3C2BrtCbMn1SpRJ6gj4jkYm7Vc?u=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F>
My
Twitter<https://clicktime.symantec.com/3T8EM4Bvo2iWtudyQE8dVX17Vc?u=https%3A%2F%2Ftwitter.com%2FMohamedHouri>
-
MohamedHouri<https://clicktime.symantec.com/3T8EM4Bvo2iWtudyQE8dVX17Vc?u=https%3A%2F%2Ftwitter.com%2FMohamedHouri>
