1. 问题现象
在Oracle 12c 以后的版本中,SYSAUX 表空间增长较快。查询发现SM/ADVISOR 和 WRI$_ADV_OBJECTS 占用大量空间。
SQL> SET LINES 120
SQL> COL OCCUPANT_NAME FORMAT A30
SQL> SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC;
OCCUPANT_NAME SPACE_USAGE_KBYTES
------------------------------ ------------------
SM/ADVISOR 5901376
SM/OPTSTAT 574080
...
SQL> COL SEGMENT_NAME FORMAT A30
SQL> COL OWNER FORMAT A10
SQL> COL TABLESPACE_NAME FORMAT A10
SQL> COL SEGMENT_TYPE FORMAT A15
SQL> 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;
SEGMENT_NAME OWNER TABLESPACE SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
WRI$_ADV_OBJECTS SYS SYSAUX 3600 TABLE
WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 1400 INDEX
...
2. 解决方法
Oracle 12.2 引入了Optimizer Statistics Advisor,每天在维护窗口运行时会占用大量的空间。
SQL> COL TASK_NAME FORMAT A35
SQL> SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC;
TASK_NAME CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK 27082431
SYS_AUTO_SPM_EVOLVE_TASK 19
SYS_AUTO_SQL_TUNING_TASK 39
...
可以定期清理也可以直接禁用该特性:
12C清理方式
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;/
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
19C清理方式
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;/
EXEC DBMS_STATS.INIT_PACKAGE();
SQL> alter session set container=<PDB_NAME>;
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER TABLE WRI$_ADV_OBJECTS MOVE');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD'); /* This index is available from 19c only */
禁用advisor
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;/
参考文档:SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)
版权声明:本文为博主原创文章,未经博主允许不得转载。