【故障】SYSAUX表空间使用率过高的问题处理

SYSAUX表空间使用率过高的问题处理

 

• Oracle数据库的表空间主要分为两类，一类是数据库系统表空间，例如SYSTEM, SYSAUX, TEMP, UNDO，另一类是用户自定义表空间，主要适用于各种业务的数据处理。SYSTEM表空间主要用来存储数据库的数据字典和元数据信息，其表空间的容量大小随着数据库对象的规模而变化；而SYSAUX表空间诞生于10G，作为SYSTEM表空间的辅助空间，被设计用来存储数据库对象的统计信息以及数据库快照等历史性能数据，其空间容量随着时间的推移不断地膨胀,我们需要根据oracle的相应的管理方式来设置管理策略，设定历史数据的保留时间等。
• 而UNDO 和TEMP表空间的容量则与数据库的活跃程度相关，数据库越是活跃，事务处理规模越大则相应空间容量的占用会增加。我们需要根据业务所产生的数据库事务的规模设置TEMP和UNDO表空间的容量。

• 我们使用以下的SQL语句查询数据库系统表空间的使用率信息：

SELECT * FROM ( 
    SELECT D.TABLESPACE_NAME, 
            SPACE || 'M' "SUM_SPACE(M)", 
            BLOCKS "SUM_BLOCKS", 
            SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 
            ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 
               "USED_RATE(%)", 
            FREE_SPACE || 'M' "FREE_SPACE(M)" 
       FROM (  SELECT TABLESPACE_NAME, 
                      ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                      SUM (BLOCKS) BLOCKS 
                 FROM DBA_DATA_FILES 
             GROUP BY TABLESPACE_NAME) D, 
            (  SELECT TABLESPACE_NAME, 
                      ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
                 FROM DBA_FREE_SPACE 
             GROUP BY TABLESPACE_NAME) F 
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
     UNION ALL                                                           
     SELECT D.TABLESPACE_NAME, 
            SPACE || 'M' "SUM_SPACE(M)", 
            BLOCKS SUM_BLOCKS, 
            USED_SPACE || 'M' "USED_SPACE(M)", 
            ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 
            NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
       FROM (  SELECT TABLESPACE_NAME, 
                      ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                      SUM (BLOCKS) BLOCKS 
                 FROM DBA_TEMP_FILES 
             GROUP BY TABLESPACE_NAME) D, 
            (  SELECT TABLESPACE_NAME, 
                      ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                      ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
                 FROM V$TEMP_SPACE_HEADER 
             GROUP BY TABLESPACE_NAME) F 
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
     ORDER BY 1)  
     WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP'); 

TABLESPACE_NAME  SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%)  FREE_SPACE(M)
---------------  -----------   ---------  ------------   -----------   ------------ 
SYSAUX             840M           107520      792.31M         94.32%         47.69M
SYSTEM             810M           103680      809.56M         99.95%         .44M                       
TEMP               47M             6016        47M              100%           0M
UNDOTBS1           405M           51840       404M             99.75%        1M

• 我们使用以下语句查询SYSAUX表空间内各个分类项目占存储空间的比重：

SELECT occupant_name "Item", 
           space_usage_kbytes / 1048576 "Space Used (GB)", 
           schema_name "Schema", 
           move_procedure "Move Procedure" 
  FROM v$sysaux_occupants 
 ORDER BY 1

• 修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除：

（1）查询数据库统计信息的保留时间

 

SQL> select dbms_stats.get_stats_history_retention from dual; 

GET_STATS_HISTORY_RETENTION 
--------------------------- 
                         31 

        （2）更改数据库统计信息的保留时间为7天

SQL> exec dbms_stats.alter_stats_history_retention(7);       
    PL/SQL procedure successfully completed. 

（3）再次查询数据库统计信息的保留时间

SQL> select dbms_stats.get_stats_history_retention from dual; 

GET_STATS_HISTORY_RETENTION 
--------------------------- 
       7 

• 修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改

 （1）查询数据库快照的MIN(SNAP_ID)和MAX(SNAP_ID)

 

SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         701          716

 （2）如果修改数据库快照的保留时间出现错误ORA-13541与ORA-06512，处理方法如下所示

ERROR

begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 39
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 87
ORA-06512: 在 line 2

• 查询ORA-13541的错误信息如下:

ORA-13541: system moving window baseline size (string) greater than retention (string)
Cause: The system moving window baseline size must be less than the retention setting.
            The specified window size or retention violate this.
Action:Check the moving window baseline size or retention.

• 在看看上面的个数字基线大小(691200)和保留时间(604800)

• 基线大小691200

SQL> select 691200/60/60/24 from dual;

691200/60/60/24
---------------
              8
Note:8天的时间

• 保留时间604800

SQL> select 604800/60/24/60 from dual;

604800/60/24/60
---------------
              7

• 检查当前的移动窗口基线大小

SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME            BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------ ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW     MOVING_WINDOW                  8

• 与上面的8天对应的数字(8*60*60*24=691200)相吻合.

• 调用如下过程修改移动窗口baseline size为7天

SQL> exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL 过程已成功完成。

• 然后继续上面的修改操作

SQL> begin
           dbms_workload_repository.modify_snapshot_settings (
             interval => 30,
             retention => 7*24*60,
             topnsql => 100
           );
     end;
     /
PL/SQL 过程已成功完成。

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL         RETENTION             TOPNSQL
---------- --------------------- --------------------- ----------
4096851118 +00000 00:30:00.0     +00007 00:00:00.0            100

SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME           BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ----------------------- ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW    MOVING_WINDOW                  7

•  在Enterprise Manager中修改快照的保留时间