RE: purge of AWR historical tables

What were you calling the purge stats proc with?

There is a magic flag DBMS_STATS.PURGE_ALL which should truncate/drop the partitions but very briefly some time ago I noticed one oddity in what it was doing wrt indexes on 12.1 https://orastory.wordpress.com/2015/02/16/dbms_stats-purge_stats/

I would ditch all of it. I have almost never needed the history data and to solve an immediate problem, I wouldn’t be overly concerned about retaining it.

Regards,
Dominic

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Mohamed Houri<mailto:mohamed.houri_at_gmail.com> Sent: 16 April 2021 09:37
To: ORACLE-L<mailto: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.

• 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://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.hourim.wordpress.com%2F&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225260810%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=jtpnXXBur6e2upm0hOx%2F4Xyjcv7XoAjQj7eM1GfCgiU%3D&reserved=0>

Let's Connect - <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225270800%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=5AyYcb8cptWBX3qEJW48FAfW8CGWFc8OBKI7MIfZbac%3D&reserved=0> Linkedin Profile<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225280798%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=MxV4CFE22NuxdNsLD0tlhiC%2B%2FoJ7YMPiT1Pig6o8Bp0%3D&reserved=0>

My Twitter<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FMohamedHouri&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225280798%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=mXVC8%2BdwVJKSmoPJ%2FP2g19JAp%2FMhmD9%2FNMKE7cqIg2c%3D&reserved=0> - MohamedHouri<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FMohamedHouri&data=04%7C01%7C%7C8f9274111d7c4ec6619e08d900b2cdea%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637541590225280798%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=mXVC8%2BdwVJKSmoPJ%2FP2g19JAp%2FMhmD9%2FNMKE7cqIg2c%3D&reserved=0>

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 19 2021 - 21:27:17 CEST