SMOPTSTAT导致sysaux异常增长

这问题是由最近一个数据库SYSAUX使用空间过多引出来的. SM/OPTSTAT是用于存储老的统计信息，10G之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。10G就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会暴了的。默认的情况下,系统会为SM/OPTSTAT保留31天的记录，可以通过dbms_stats.get_stats_history_retention来确定。这里的统计信息跟AWR是有区别的, AWR默认保留7天. SM/OPTSTAT保留的时间可以通过dbms_stats.alter_stats_history_retention来控制。如果SM/OPTSTAT确实占用了比较多的空间，要删除某个时间前的记录可以用：dbms_stats.purge_stats. 这个SP其实只从存储历史统计信息的表里删除记录，这样的话，就会出现一种情况，删除了大量的数据，但这些表占用的空间并没有释放,也就是HWM不会降下来的。这时要手工处理，顺便说一下用于存储SM/OPTSTAT相关信息的表是由脚$ORACLE_HOME/rdbms/admin/catost.sql来创建的.下面是测试：

SQL> show parameter sga_targetNAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_target big integer 0SQL> select dbms_stats.get_stats_history_availability from dual;GET_STATS_HISTORY_AVAILABILITY---------------------------------------------------------------------------10-APR-10 11.15.35.339756000 PM -04:00SQL> select dbms_stats.get_stats_history_retention from dual;GET_STATS_HISTORY_RETENTION---------------------------31SQL> select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAM MOVE_PROCE MOVE_PROCEDURE_DESC SPACE_USAGE_KBYTES-------------- ---------------------------------------------------- ---------- ---------- ---------------------------------------- ------------------SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS *** MOVE PROCEDURE NOT APPLICABLE *** 68416SQL> select sum(SPACE_USAGE_KBYTES) from v$sysaux_occupants;SUM(SPACE_USAGE_KBYTES)-----------------------175168SQL> select owner,segment_name,sum(bytes)/1024/1024 from dba_extents where segment_name in (2 'WRI$_OPTSTAT_TAB_HISTORY','I_WRI$_OPTSTAT_TAB_OBJ#_ST','I_WRI$_OPTSTAT_TAB_ST','WRI$_OPTSTAT_IND_HISTORY','I_WRI$_OPTSTAT_IND_OBJ#_ST','I_WRI$_OPTSTAT_IND_ST','WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST','WRI$_OPTSTAT_HISTGRM_HISTORY'3 4 ,'I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_H_ST','WRI$_OPTSTAT_AUX_HISTORY','I_WRI$_OPTSTAT_AUX_ST','WRI$_OPTSTAT_OPR'5 ,'I_WRI$_OPTSTAT_OPR_STIME','OPTSTAT_HIST_CONTROL$')6 group by owner,segment_name7 order by 3;OWNER SEGMENT_NAME SUM(BYTES)/1024/1024------------------------------ --------------------------------------------------------------------------------- --------------------SYS OPTSTAT_HIST_CONTROL$ .015625SYS WRI$_OPTSTAT_AUX_HISTORY .0625SYS I_WRI$_OPTSTAT_AUX_ST .0625SYS WRI$_OPTSTAT_OPR .0625SYS I_WRI$_OPTSTAT_OPR_STIME .0625SYS I_WRI$_OPTSTAT_IND_ST .1875SYS I_WRI$_OPTSTAT_TAB_ST .1875SYS I_WRI$_OPTSTAT_IND_OBJ#_ST .25SYS I_WRI$_OPTSTAT_TAB_OBJ#_ST .3125SYS WRI$_OPTSTAT_TAB_HISTORY .3125SYS WRI$_OPTSTAT_IND_HISTORY .3125SYS I_WRI$_OPTSTAT_HH_ST 2SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 4SYS I_WRI$_OPTSTAT_H_ST 13SYS WRI$_OPTSTAT_HISTGRM_HISTORY 17SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 2617 rows selected.

从这里可以看出，总共使用了175M sysaux,其中SM/OPTSTAT占用68M.

SQL> exec dbms_stats.purge_stats(sysdate-1);PL/SQL procedure successfully completed.SQL> select dbms_stats.get_stats_history_availability from dual;11-MAY-10 10.22.53.000000000 PM -04:00

把之前的历史数据清空了.

SQL> select owner,segment_name,sum(bytes)/1024/1024 from dba_extents where segment_name in (2 'WRI$_OPTSTAT_TAB_HISTORY','I_WRI$_OPTSTAT_TAB_OBJ#_ST','I_WRI$_OPTSTAT_TAB_ST','WRI$_OPTSTAT_IND_HISTORY','I_WRI$_OPTSTAT_IND_OBJ#_ST','I_WRI$_OPTSTAT_IND_ST','WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST','WRI$_OPTSTAT_HISTGRM_HISTORY'3 4 ,'I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_H_ST','WRI$_OPTSTAT_AUX_HISTORY','I_WRI$_OPTSTAT_AUX_ST','WRI$_OPTSTAT_OPR'5 ,'I_WRI$_OPTSTAT_OPR_STIME','OPTSTAT_HIST_CONTROL$')group by owner,segment_name6 7 order by 3;OWNER SEGMENT_NAME SUM(BYTES)/1024/1024------------------------------ --------------------------------------------------------------------------------- --------------------SYS OPTSTAT_HIST_CONTROL$ .015625SYS WRI$_OPTSTAT_AUX_HISTORY .0625SYS I_WRI$_OPTSTAT_AUX_ST .0625SYS WRI$_OPTSTAT_OPR .0625SYS I_WRI$_OPTSTAT_OPR_STIME .0625SYS I_WRI$_OPTSTAT_IND_ST .1875SYS I_WRI$_OPTSTAT_TAB_ST .1875SYS I_WRI$_OPTSTAT_IND_OBJ#_ST .25SYS I_WRI$_OPTSTAT_TAB_OBJ#_ST .3125SYS WRI$_OPTSTAT_TAB_HISTORY .3125SYS WRI$_OPTSTAT_IND_HISTORY .3125SYS I_WRI$_OPTSTAT_HH_ST 2SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 4SYS I_WRI$_OPTSTAT_H_ST 13SYS WRI$_OPTSTAT_HISTGRM_HISTORY 17SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 2617 rows selected.SQL> select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAM MOVE_PROCE MOVE_PROCEDURE_DESC SPACE_USAGE_KBYTES-------------- ---------------------------------------------------- ---------- ---------- ---------------------------------------- ------------------SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS *** MOVE PROCEDURE NOT APPLICABLE *** 68416SQL> select sum(SPACE_USAGE_KBYTES) from v$sysaux_occupants;SUM(SPACE_USAGE_KBYTES)-----------------------175168这里可以看出purge完后，这些空间并没有释放.SQL> show userUSER is "SYS"SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space*ERROR at line 1:ORA-10631: SHRINK clause should not be specified for this object

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;Table altered.SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space*ERROR at line 1:ORA-10631: SHRINK clause should not be specified for this object

报这个错误的原因是由于这张表有函数索引:SQL> select owner,index_name from dba_indexes where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';SYS I_WRI$_OPTSTAT_H_STSYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_STSQL> select index_owner,index_name from dba_ind_expressions where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_STSYS I_WRI$_OPTSTAT_H_ST

只能用move来降HWM了，做完这个操作，要对相应的index重建.

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;Table altered.SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;Index altered.SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild online;Index altered.SQL> select * from v$sysaux_occupants where occupant_name='SM/OPTSTAT';OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAM MOVE_PROCE MOVE_PROCEDURE_DESC SPACE_USAGE_KBYTES-------------- ---------------------------------------------------- ---------- ---------- ---------------------------------------- ------------------SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS *** MOVE PROCEDURE NOT APPLICABLE *** 12736SQL> select sum(SPACE_USAGE_KBYTES) from v$sysaux_occupants;SUM(SPACE_USAGE_KBYTES)-----------------------119488SQL> select owner,segment_name,sum(bytes)/1024/1024 from dba_extents where segment_name in (2 'WRI$_OPTSTAT_TAB_HISTORY','I_WRI$_OPTSTAT_TAB_OBJ#_ST','I_WRI$_OPTSTAT_TAB_ST','WRI$_OPTSTAT_IND_HISTORY','I_WRI$_OPTSTAT_IND_OBJ#_ST','I_WRI$_OPTSTAT_IND_ST','WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST','WRI$_OPTSTAT_HISTGRM_HISTORY'3 4 ,'I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_H_ST','WRI$_OPTSTAT_AUX_HISTORY','I_WRI$_OPTSTAT_AUX_ST','WRI$_OPTSTAT_OPR'5 ,'I_WRI$_OPTSTAT_OPR_STIME','OPTSTAT_HIST_CONTROL$')6 group by owner,segment_name7 order by 3;

OWNER SEGMENT_NAME SUM(BYTES)/1024/1024------------------------------ --------------------------------------------------------------------------------- --------------------SYS OPTSTAT_HIST_CONTROL$ .015625SYS WRI$_OPTSTAT_AUX_HISTORY .0625SYS I_WRI$_OPTSTAT_OPR_STIME .0625SYS I_WRI$_OPTSTAT_AUX_ST .0625SYS WRI$_OPTSTAT_OPR .0625SYS I_WRI$_OPTSTAT_IND_ST .1875SYS I_WRI$_OPTSTAT_TAB_ST .1875SYS I_WRI$_OPTSTAT_IND_OBJ#_ST .25SYS I_WRI$_OPTSTAT_TAB_OBJ#_ST .3125SYS WRI$_OPTSTAT_IND_HISTORY .3125SYS WRI$_OPTSTAT_TAB_HISTORY .3125SYS I_WRI$_OPTSTAT_H_ST .4375SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST .5625SYS WRI$_OPTSTAT_HISTGRM_HISTORY .625SYS I_WRI$_OPTSTAT_HH_ST 2SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 417 rows selected.

从总体上看：sysaux释放了56M, 这正是SM/OPTSTAT释放出来的。测试时碰到一问题，当SGA是自动管理时，purge时会被MMON进程阻塞的。