签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c SYSAUX 表空间增长过快 解决方法

2023-03-28 08:52 1168 0 原创 Oracle 19c
作者: dave

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
...

可以定期清理也可以直接禁用该特性:http://www.cndba.cn/dave/article/116511http://www.cndba.cn/dave/article/116511

12C清理方式http://www.cndba.cn/dave/article/116511http://www.cndba.cn/dave/article/116511http://www.cndba.cn/dave/article/116511

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清理方式http://www.cndba.cn/dave/article/116511

http://www.cndba.cn/dave/article/116511
http://www.cndba.cn/dave/article/116511

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 */

禁用advisorhttp://www.cndba.cn/dave/article/116511

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)http://www.cndba.cn/dave/article/116511

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2235
    原创
  • 2
    翻译
  • 535
    转载
  • 185
    评论
  • 访问:6448417次
  • 积分:4223
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ