Clear the WRH $ _ LATCH_CHILDREN table and sysauxwrh table in the SYSAUX tablespace.

Clear the WRH $ _ LATCH_CHILDREN table and sysauxwrh table in the SYSAUX tablespace.

On Saturday, the system was suddenly woken up by text messages. 63 messages appeared, all of which were in monitoring. The tablespace size exceeded a warning value.

It is found that the SYSAUX tablespace exceeds 15 GB.

Run the following code to view the usage of the SYSAUX tablespace.

SELECT occupant_name "Item",        space_usage_kbytes / 1048576 "Space Used (GB)",        schema_name "Schema",        move_procedure "Move Procedure"   FROM v$sysaux_occupants ORDER BY 2 desc

Basically

Item	Space Used (GB)	Schema
SM/AWR	15.3005981445313	SYS

View the corresponding table and index size rankings

SELECT *  FROM (SELECT SEGMENT_NAME,               PARTITION_NAME,               SEGMENT_TYPE,               BYTES / 1024 / 1024          FROM DBA_SEGMENTS         WHERE TABLESPACE_NAME = 'SYSAUX'         ORDER BY 4 DESC) WHERE ROWNUM <= 10;

SEGMENT_NAMEPARTITION_NAMESEGMENT_TYPEBYTES/1024/1024
WRH $ _ LATCH_CHILDRENWRH $ _ latch1_567344007_15885table PARTITION3971
WRH $ _ LATCH_CHILDREN_PKWRH $ _ latch1_567344007_15885index PARTITION2822
WRH $ _ LATCH_CHILDRENWRH $ _ latch1_567344007_0table PARTITION2213
WRH $ _ LATCH_CHILDRENWRH $ _ latch1_567344007_15909table PARTITION1984
WRH $ _ LATCH_CHILDREN_PKWRH $ _ latch1_567344007_0index PARTITION1537
WRH $ _ LATCH_CHILDREN_PKWRH $ _ latch1_567344007_15909index PARTITION1412

Basically, the WRH $ table partition is too large. The first number of WRH $ _ latch1_567344007_15909 is DBID, and the second value is snapshot ID.

Check whether the three IDs above 15885 15909 0 are recent IDs?

Check the ID time and find that it is the ID of the last two days.

select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;

Is it because STATISTICS_LEVEL is set to ALL, and they become larger?

But how can we clear 0!

Alter table WRH $ _ LATCH_CHILDREN truncate partition WRH $ _ latch1_567344007_0;

Change the default AWR to 31 days to 10 days.

SelectDbms_stats.get_stats_history_retentionFrom dual;

Exec dbms_stats.alter_stats_history_retention (10 );

Clear 11-day statistics

Exec dbms_stats.purge_stats (systimestamp-11 );

To be honest, these statistics do not occupy much space, just to keep the same row. It is not worthwhile to keep the database for 31 days.