Re: purge of AWR historical tables
Date: Mon, 19 Apr 2021 18:21:20 +0000
Message-ID: <BN6PR01MB25472DA93348BFDDC1C0A21FCE499_at_BN6PR01MB2547.prod.exchangelabs.com>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mohamed Houri <mohamed.houri_at_gmail.com> Sent: Friday, April 16, 2021 4:36 AM
To: ORACLE-L <oracle-l_at_freelists.org> Subject: purge of AWR historical tables
Hello
At one project (Oracle 12.1) two of the AWR historical tables are occupying 1,8 TB.
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
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>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 19 2021 - 20:21:20 CEST