Clear the WRH $ _ LATCH_CHILDREN table of the sysaux tablespace

Clear the WRH $ _ LATCH_CHILDREN table of the sysaux tablespace

On Saturday, the system was suddenly woken up by text messages. 63 messages appeared, all of which were monitored. The tablespace size exceeds 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_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
WRH $ _ LATCH_CHILDREN WRH $ _ latch1_567344007_15885 table partition 3971
WRH $ _ LATCH_CHILDREN_PK WRH $ _ latch1_567344007_15885 index partition 2822
WRH $ _ LATCH_CHILDREN WRH $ _ latch1_567344007_0 table partition 2213
WRH $ _ LATCH_CHILDREN WRH $ _ latch1_567344007_15909 table partition 1984
WRH $ _ LATCH_CHILDREN_PK WRH $ _ latch1_567344007_0 index partition 1537
WRH $ _ LATCH_CHILDREN_PK WRH $ _ latch1_567344007_15909 index partition 1412
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.

Select dbms_stats.get_stats_history_retention from 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.