RE: purge of AWR historical tables

  • From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • To: "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 19 Apr 2021 19:27:17 +0000

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@xxxxxxxxx>
Sent: 16 April 2021 09:37
To: ORACLE-L<mailto: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://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>

Other related posts: