Oracle 19c pdb AUTO_STATS_ADVISOR_TASK占用表空间sysaux

问题来源：

监控系统告警表空间sysaux使用率达到95%

分析：

查看表空间使用：

SQL> @df


TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
SYSAUX                              32760      31075       1685    95% NO  |################### |

复制

查看表空间sysaux体积最大的段：

select * from (
select segment_name,owner,tablespace_name,bytes/1024/1024 "Size(MB)",
segment_type
from dba_segments
where tablespace_name='SYSAUX'
order by bytes desc) where rownum<=10;

复制

查看视图V$SYSAUX_OCCUPANTS 显示 SYSAUX 表空间占用 SM/ADVISOR占用最大。

SQL> select OCCUPANT_NAME,SPACE_USAGE_KBYTES from  v$sysaux_occupants order by SPACE_USAGE_KBYTES desc;


OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
SM/ADVISOR                                                                 31106304
SM/OPTSTAT                                                                 235456
SDO                                                                               176192
SM/OTHER                                                                     62400

复制

通过以下查询，可以获得任务数据的保留时间，缺省的保留时间是没有限制的：

col TASK_NAME for a30
col PARAMETER_NAME for a30
col PARAMETER_VALUE for a30
col DESCRIPTION for a30
col EXECUTION_TYPE for a30
SELECT parameter_name,
parameter_value
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';

复制

解决办法：

可以通过修改保留时间，限制保留为 31 天：

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
  task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 31
);
END;
/

复制