My Oracle Support Banner

AUTO_STATS_ADVISOR_TASK Data Pertaining To One Table Leading To High Space Consumption For The SYSAUX Tablespace (Doc ID 2996766.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Information in this document applies to any platform.

Symptoms

SM/ADVISOR is consuming large amounts of space in the SYSAUX tablespace.

Example from AWRInfo report:

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/ADVISOR           SYS                        5,317.4 MB
| SM/AWR               SYS                          420.1 MB
| SM/OPTSTAT           SYS                          351.2 MB

The EXECUTION_DAYS_TO_EXPIRE parameter is set to 10 for AUTO_STATS_ADVISOR_TASK and the data appears to be purged correctly based on the retention period as there are no records older than 10 days.

 

As can be seen, most of the records are for CMS_INFOOBJECTS7 table.

Changes

 No Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.