• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

Oracle12cSYSAUX表空间增长过快,主要是热表,统计信息,优化器 ...

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
 
1. 通过SQL语句查看 SYSAUX 占用情况,和表空间里对象占用情况
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
HEAT_MAP                       SYS        SYSAUX       600       TABLE
 

 

A.哪果是表空间里是 heat_map占用比较大 ,可以查看HEAT_MAP 是否开启

   这个段为12c新特性提供的新功能:它存储在行和段级别系统生成的数据使用统计信息——这些信息可以用来自动化压缩和移动数据,以降低存储成本、提高性能和优化Oracle数据库存储,并且默认是启用的状态,但如果数据库host objects过多将会导致该段占用大量SYSAUX表空间,通过MOS搜索得到该问题的解决方案:
      ALTER SYSTEM SET "_drop_stat_segment" =1;    数据库不用重启
      通过调整数据库的隐藏参数来进行段的清除(文档 ID 2024036.1
 

B. SM/OPTSTAT  占用空间排名 参考:文档 ID )  

    这个是由于,存放的历史统计信息太多, 更统计信息频率,和数据量变更,以及保存时间都有关系。
    1. 自动清理策略默认31天
          select dbms_stats.get_stats_history_availability from dual;
    2. 修改保留策略
           exec dbms_stats.alter_stats_history_retention(1);
    3. 手动清理一次
           exec dbms_stats.purge_stats(sysdate-10);
    4.  如果表还是比较大,可以考虑在线移动
 
a. 重创建表
CREATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY_B AS SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY ;
 TRUNCATE TABLE  WRI$_OPTSTAT_HISTGRM_HISTORY ;
 INSERT INTO WRI$_OPTSTAT_HISTGRM_HISTORY  SELECT * FROM  WRI$_OPTSTAT_HISTGRM_HISTORY_B;
 COMMIT;
 drop table WRI$_OPTSTAT_HISTGRM_HISTORY_B purge;

 

        b.通过表移动的方式
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;  文档ID 454678.1

 

    5. 手动重创建索引  
alter index  xxx  rebuild online ; 

 

 

C. SM/ADVISOR 占用空间排名 参考:文档 ID 2440139.1)  

      查询视图 V$SYSAUX_OCCUPANTS 显示 SM/ADVISOR 占用空间排名靠前.
      这个是由于12.2版本的一个新特性,即优化器统计信息顾问每天在维护窗口期间自动运行,因而引发了该问题。

      AUTO_STATS_ADVISOR_TASK 任务运行了很多次导致SYSAUX表空间增长迅速。

   

例如:

 
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

 

 
 

解决办法:

 

1. 这个自动运行的任务(AUTO_STATS_ADVISOR_TASK) 可以被删除进而释放它占用的大量空间。

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

 

一旦任务被删除,所有依赖Auto Stats Advisor Task的相关历史输出信息将从WRI$_ADV_OBJECTS表中一并删除。

删除任务AUTO_STATS_ADVISOR_TASK可能引发如下报错:

ORA-20001: Statistics Advisor: Invalid Task Name For the current user

如果上述报错出现,可以用下面的方法重建AUTO_STATS_ADVISOR_TASK来解决:

SQL> connect / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();

 

 
删除任务之后,重组表和所有索引:

SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

 
2.如果表WRI$_ADV_OBJECTS中有太多和Auto Stats Advisor Task相关的记录,删除任务时可能会需要大量undo空间.

一旦发生此状况,如下方法可以用来直接清理数据而避免产生大量redo 和undo信息。

### 检查表 WRI$_ADV_OBJECTS和 Auto Stats Advisor Task相关的有效条数 ###
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

### 创建新表并保留非 AUTO_STATS_ADVISOR_TASK数据 ###
SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

 

### 截断原表 ###

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

 

### 把备份表中数据插回原表 ###

SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

 

### 重建索引 ###

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

 



P.S: 表 WRI$_ADV_OBJECTS_NEW在上述命令都成功执行后可删除。

从数据字典删除统计信息任务,避免再次造成问题。

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

 

DBA可以在任何时间重建统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK),具体参考如下办法:

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

 

统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK)也可以被直接禁用:
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/

 

 新的增强补丁已经被申请,将会提供更方便的方法来禁用这个任务。
Unpublished Bug 26749785 NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK

 

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
[c++]子程序计算两个数字的相加的结果并返回发布时间:2022-07-13
下一篇:
C++两个矩阵库发布时间:2022-07-13
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap