oracle10g的sysaux空间暴增与空间回收

约定不等于承诺〃 2022-06-15 09:23 442阅读 0赞

author：skate  
time：2012/02/22

**oracle10g的sysaux空间暴增与空间回收**

在Oracle10中表空间SYSAUX引入，oracle把统计信息存储在这里，这也是为了更好的优化system表空间，我们可以用视图V$SYSAUX\_OCCUPANTS 查看，oracle有哪些数据存贮在SYSAUX中。

SELECT occupant\_name, space\_usage\_kbytes FROM V$SYSAUX\_OCCUPANTS;

oracle的SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER的统计信息都存储在SYSAUX中，这里重点介绍SM/OPTSTAT。

SM/OPTSTAT：用于存储老版本的优化统计信息，在oracle10g中，在我们手动或自动更新统计信息使oracle选择了错误的执行计划。oracle10g是可以恢复旧版本的统计信息，这个统计信息默认保存31天

查询当前SM/OPTSTAT的统计信息的保存时间  
SQL> select dbms\_stats.get\_stats\_history\_retention from dual;  
   
GET\_STATS\_HISTORY\_RETENTION  
\---------------------------  
                         31  
   
修改SM/OPTSTAT的统计信息的保存时间为10天  
SQL> exec dbms\_stats.alter\_stats\_history\_retention(10);  
   
PL/SQL procedure successfully completed  
   
SQL> select dbms\_stats.get\_stats\_history\_retention from dual;  
   
GET\_STATS\_HISTORY\_RETENTION  
\---------------------------  
                         10  
   
SQL>

删除16天前的统计数据  
SQL> exec dbms\_stats.purge\_stats(sysdate-16);

PL/SQL procedure successfully completed  
   
SQL>

查看当前有效的统计数据是到什么时间的  
SQL> select DBMS\_STATS.GET\_STATS\_HISTORY\_AVAILABILITY from dual;  
   
GET\_STATS\_HISTORY\_AVAILABILITY  
\--------------------------------------------------------------------------------  
12-2月 -12 07.15.49.000000000 下午 +08:00

再删除7天前的统计数据  
SQL> exec dbms\_stats.purge\_stats(sysdate-7);  
   
PL/SQL procedure successfully completed  
   
这个时候发现有效的统计信息时间已经变了  
SQL> select DBMS\_STATS.GET\_STATS\_HISTORY\_AVAILABILITY from dual;  
   
GET\_STATS\_HISTORY\_AVAILABILITY  
\--------------------------------------------------------------------------------  
14-2月 -12 07.15.57.000000000 下午 +08:00  
   
SQL>

这个时候虽然删除了数据，但空间还没有回收，如何回收空间呢？

没有释放空间是因为“purge\_stats”用delete的方式删除数据，虽然数据没了，但是HWM还没降下来，查看OPTSTAT使用哪些表，然后降低其高水位即可。  
SQL> SELECT s.object\_name from dba\_objects s where s.object\_name like '%OPTSTAT%' and s.object\_type='TABLE'  
  2  ;  
   
OBJECT\_NAME  
\--------------------------------------------------------------------------------  
WRI$\_OPTSTAT\_TAB\_HISTORY  
WRI$\_OPTSTAT\_IND\_HISTORY  
WRI$\_OPTSTAT\_HISTHEAD\_HISTORY  
WRI$\_OPTSTAT\_HISTGRM\_HISTORY  
WRI$\_OPTSTAT\_AUX\_HISTORY  
WRI$\_OPTSTAT\_OPR  
OPTSTAT\_HIST\_CONTROL$  
   
7 rows selected  
   
SQL>

再结合如下sql判断哪个表大，然后就move哪个表  
SQL> select a.table\_name,a.num\_rows from dba\_tables a where  a.tablespace\_name='SYSAUX' and a.table\_name like '%OPTSTAT%'  
  2  ;  
   
TABLE\_NAME                       NUM\_ROWS  
\------------------------------ ----------  
WRI$\_OPTSTAT\_OPR                      151  
WRI$\_OPTSTAT\_AUX\_HISTORY                0  
WRI$\_OPTSTAT\_HISTGRM\_HISTORY       139933  
WRI$\_OPTSTAT\_HISTHEAD\_HISTORY       14406  
WRI$\_OPTSTAT\_IND\_HISTORY             1196  
WRI$\_OPTSTAT\_TAB\_HISTORY             1323  
   
6 rows selected  
   
SQL>

再用如下语句查出相关表的索引，因为move表，索引会失效，需要重建索引  
SQL> select i.index\_name,i.table\_name,i.status,i.table\_owner  
  2   from dba\_indexes i,dba\_objects s where i.table\_name=s.object\_name and  s.object\_name like '%OPTSTAT%' and s.object\_type='TABLE'  
  3  ;  
   
INDEX\_NAME                     TABLE\_NAME                     STATUS   TABLE\_OWNER  
\------------------------------ ------------------------------ -------- ------------------------------  
I\_WRI$\_OPTSTAT\_TAB\_OBJ\#\_ST     WRI$\_OPTSTAT\_TAB\_HISTORY       VALID    SYS  
I\_WRI$\_OPTSTAT\_TAB\_ST          WRI$\_OPTSTAT\_TAB\_HISTORY       VALID    SYS  
I\_WRI$\_OPTSTAT\_IND\_OBJ\#\_ST     WRI$\_OPTSTAT\_IND\_HISTORY       VALID    SYS  
I\_WRI$\_OPTSTAT\_IND\_ST          WRI$\_OPTSTAT\_IND\_HISTORY       VALID    SYS  
I\_WRI$\_OPTSTAT\_HH\_OBJ\_ICOL\_ST  WRI$\_OPTSTAT\_HISTHEAD\_HISTORY  VALID    SYS  
I\_WRI$\_OPTSTAT\_HH\_ST           WRI$\_OPTSTAT\_HISTHEAD\_HISTORY  VALID    SYS  
I\_WRI$\_OPTSTAT\_H\_OBJ\#\_ICOL\#\_ST WRI$\_OPTSTAT\_HISTGRM\_HISTORY   VALID    SYS  
I\_WRI$\_OPTSTAT\_H\_ST            WRI$\_OPTSTAT\_HISTGRM\_HISTORY   VALID    SYS  
I\_WRI$\_OPTSTAT\_AUX\_ST          WRI$\_OPTSTAT\_AUX\_HISTORY       VALID    SYS  
I\_WRI$\_OPTSTAT\_OPR\_STIME       WRI$\_OPTSTAT\_OPR               VALID    SYS  
   
10 rows selected  
   
SQL>

降低HWM  
sql> alter table WRI$\_OPTSTAT\_TAB\_HISTORY move;  
sql> alter table WRI$\_OPTSTAT\_OPR move;  
sql> alter table WRI$\_OPTSTAT\_IND\_HISTORY move;  
sql> alter table WRI$\_OPTSTAT\_HISTHEAD\_HISTORY move;  
sql> alter table WRI$\_OPTSTAT\_HISTGRM\_HISTORY move;  
sql> alter table WRI$\_OPTSTAT\_AUX\_HISTORY move;  
sql> alter table OPTSTAT\_HIST\_CONTROL$ move;

重建索引  
alter index I\_WRI$\_OPTSTAT\_TAB\_OBJ\#\_ST  rebuild online;  
alter index I\_WRI$\_OPTSTAT\_TAB\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_IND\_OBJ\#\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_IND\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_HH\_OBJ\_ICOL\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_HH\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_H\_OBJ\#\_ICOL\#\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_H\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_AUX\_ST rebuild online;  
alter index I\_WRI$\_OPTSTAT\_OPR\_STIME rebuild online;

如果索引编译不成功，就要create indexe

用如下语句生成DDL语句  
SQL> set long 4000  
SQL> select dbms\_metadata.get\_ddl('INDEX','I\_WRI$\_OPTSTAT\_IND\_OBJ\#\_ST','SYS') from dual;  
SQL> select dbms\_metadata.get\_ddl('INDEX','I\_WRI$\_OPTSTAT\_TAB\_ST','SYS') from dual;

**如何恢复统计信息**

用如下语句查到统计信息的时间点  
select TABLE\_NAME, STATS\_UPDATE\_TIME from dba\_tab\_stats\_history；

可以按需要根据时间点恢复统计信息  
execute DBMS\_STATS.RESTORE\_TABLE\_STATS ('owner','table',date)  
execute DBMS\_STATS.RESTORE\_DATABASE\_STATS(date)  
execute DBMS\_STATS.RESTORE\_DICTIONARY\_STATS(date)  
execute DBMS\_STATS.RESTORE\_FIXED\_OBJECTS\_STATS(date)  
execute DBMS\_STATS.RESTORE\_SCHEMA\_STATS('owner',date)  
execute DBMS\_STATS.RESTORE\_SYSTEM\_STATS(date)

例如：  
SQL> execute dbms\_stats.restore\_table\_stats ('SKATE','BK\_ADMIN',sysdate -1);  
   
PL/SQL procedure successfully completed  
   
SQL>

**参考文档：\[ID 329984.1\], \[ID 452011.1\],\[ID 454678.1\]**

\---------end--------