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

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [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人参与评论

请发表评论

全部评论

专题导读

More+

10-27 六六分期app的软件客服如何联系？(六六分期

11-06 可心卡盟:win10系统火狐flash插件崩溃怎么

11-06 亲亲特价:怎么删除回收站图标

11-06 济南大学虚拟社区:鲁大师节能降温的具体办

11-06 xlueops.exe:无线网络安装向导

11-06 女斗合众国:win7系统cf与主机连接不稳定怎

11-06 0xc000022-[cf烟雾头]cf怎么调烟雾头

11-06 qizideyouhuo:应用程序无法正常启动0xc0000

11-06 ipz-185:win7系统vcf文件怎么打开

11-06 傻哥蹦迪:win10系统s4怎么打开usb调试

11-06 八神浩树gtaste:回收站清空了怎么恢复

11-06 妖尾之黑色守护:win10系统电脑没有1440x900

11-06 校园至尊魔王小说:win7系统浏览网页时字体

11-06 女斗合众国:win10系统访问共享文件夹提示请

11-06 tokyo hot n0654:恢复win7系统默认字体一招

11-06 雨酷仙境:设置win7系统转移临时文件夹腾出

11-06 阿穆纳伊之杖:win7系统开始菜单在右边还原

11-06 tunespotting:win10系统火狐flash插件总是

11-06 甘尔葛分析师：计谋网站seo关键词暴涨有什

11-06 蔡贵霖: 计谋网站seo关键词暴涨有什么秘密

11-06 博益网首页:ao3网页版进入不了解决方法

11-06 漏斗子专栏: 网站数据分析小白易懂精华篇

11-06 见证双虹怎么做:win7系统开启telnet命令的

11-06 颾狐蝶蜋:系统资源不足无法完成请求的服务

11-06 国光中学校歌:提交网站到alexa查询详细步骤

11-06 西安有情天:静态网页和动态网页的区别

11-06 红木雅尚斋:外部链接构造对网站的好处

11-06 前官礼遇：防止域名劫持–增强域安全性的10

11-06 密传二转答案: 中文分词算法有哪些

11-06 金泉家园邮编:百度快照劫持的表现及应对方

[c++]子程序计算两个数字的相加的结果并返回发布时间：2022-07-13

C++两个矩阵库发布时间：2022-07-13

剪的笔顺,诠释剪的笔画,认识剪的部首

1 六六分期app的软件客服如何联系？(六六分期

六六分期app的软件客服如何联系？不知道吗？加qq群【895510560】即可！标题：六六分期

阅读：19271|2023-10-27

2 可心卡盟:win10系统火狐flash插件崩溃怎么

今天小编告诉大家如何处理win10系统火狐flash插件总是崩溃的问题，可能很多用户都不知

阅读：10014|2022-11-06

3 亲亲特价:怎么删除回收站图标

今天小编告诉大家如何对win10系统删除桌面回收站图标进行设置，可能很多用户都不知道

阅读：8341|2022-11-06

4 济南大学虚拟社区:鲁大师节能降温的具体办

今天小编告诉大家如何对win10系统电脑设置节能降温的设置方法，想必大家都遇到过需要

阅读：8709|2022-11-06

5 xlueops.exe:无线网络安装向导

我们在使用xp系统的过程中,经常需要对xp系统无线网络安装向导设置进行设置，可能很多

阅读：8655|2022-11-06

6 女斗合众国:win7系统cf与主机连接不稳定怎

今天小编告诉大家如何处理win7系统玩cf老是与主机连接不稳定的问题，可能很多用户都不

阅读：9684|2022-11-06

7 0xc000022-[cf烟雾头]cf怎么调烟雾头

电脑对日常生活的重要性小编就不多说了，可是一旦碰到win7系统设置cf烟雾头的问题，很

阅读：8643|2022-11-06

8 qizideyouhuo:应用程序无法正常启动0xc0000

我们在日常使用电脑的时候，有的小伙伴们可能在打开应用的时候会遇见提示应用程序无法

阅读：8011|2022-11-06

9 ipz-185:win7系统vcf文件怎么打开

今天小编告诉大家如何对win7系统打开vcf文件进行设置，可能很多用户都不知道怎么对win

阅读：8680|2022-11-06

10 傻哥蹦迪:win10系统s4怎么打开usb调试

今天小编告诉大家如何对win10系统s4开启USB调试模式进行设置，可能很多用户都不知道怎

阅读：7547|2022-11-06

客服电话

电子邮件

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

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

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

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

请发表评论

全部评论

上一篇：

下一篇：

PacktPublishing/Python-Machine-Learning-

CVE-2022-36999

sussillo/hfopt-matlab: A parallel, cpu-b

鲁东大学一米网:Win7系统USB驱动器RAM的操

emersion/go-ostatus: An OStatus library

剪的笔顺,诠释剪的笔画,认识剪的部首

六六分期app的软件客服如何联系？(六六分期

florent37/ViewAnimator: A fluent Android

florent37/Shrine-MaterialDesign2: implem

CVE-2020-36276

SimpleSoftwareIO/simple-sms: Send and re

关于我们

产品与服务

解决方案

139-2527-9053

客服电话

电子邮件

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

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

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

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

请发表评论

全部评论

上一篇：

下一篇：

PacktPublishing/Python-Machine-Learning-

CVE-2022-36999

sussillo/hfopt-matlab: A parallel, cpu-b

鲁东大学一米网:Win7系统USB驱动器RAM的操

emersion/go-ostatus: An OStatus library

剪的笔顺,诠释剪的笔画,认识剪的部首

六六分期app的软件客服如何联系？(六六分期

florent37/ViewAnimator: A fluent Android

florent37/Shrine-MaterialDesign2: implem

CVE-2020-36276

SimpleSoftwareIO/simple-sms: Send and re

关于我们

产品与服务

解决方案

139-2527-9053

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

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

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