【DB】SYSAUX表空间使用率高问题处理

一般来讲除开业务数据存放的表空间，DBA要着重关注SYSTEM,SYSAUX,UNDO,TEMP表空间，SYSTEM表空间的大小一般是衡定的，UNDO和TEMP表空间的大小由数据库的业务情况决定，而SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大！SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等，个人认为，如果你的SYSAUX表空间大小超过2G，那么该考虑让他减肥了！ 

一：使用下列语句查询表空间使用率 

/* Formattedon 2015/4/9星期四 10:56:01 (QP5v5.185.11230.41888) */

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( SELECTTABLESPACE_NAME,

                         ROUND (SUM (BYTES) / (1024 *1024),2)SPACE,

                         SUM (BLOCKS) BLOCKS

                    FROM DBA_DATA_FILES

                GROUPBY TABLESPACE_NAME) D,

               ( SELECTTABLESPACE_NAME,

                         ROUND (SUM (BYTES) / (1024 *1024),2) FREE_SPACE

                    FROM DBA_FREE_SPACE

                GROUPBY TABLESPACE_NAME) F

         WHERE D.TABLESPACE_NAME= F.TABLESPACE_NAME(+)

        UNIONALL

        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( SELECTTABLESPACE_NAME,

                         ROUND (SUM (BYTES) / (1024 *1024),2)SPACE,

                         SUM (BLOCKS) BLOCKS

                    FROM DBA_TEMP_FILES

                GROUPBY TABLESPACE_NAME) D,

               ( SELECTTABLESPACE_NAME,

                         ROUND (SUM (BYTES_USED) / (1024 *1024),2) USED_SPACE,

                         ROUND (SUM (BYTES_FREE) / (1024 *1024),2) FREE_SPACE

                    FROM V$TEMP_SPACE_HEADER

                GROUPBY TABLESPACE_NAME) F

         WHERE D.TABLESPACE_NAME= F.TABLESPACE_NAME(+)

        ORDER BY 1)
 

二：查询SYSAUX表空间内各个分类项目占存储空间的比重，很明显可以看出来AWR快照占用了2G左右的空间，统计信息为149M左右，同时数据库关闭了审计audit_trail，所以审计表aud$不占空间

/* Formattedon 2015/4/9星期四 10:58:25 (QP5v5.185.11230.41888) */

 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天，过期的统计信息会自动被删除

select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

31

 

exec dbms_stats.alter_stats_history_retention(7);   

PL/SQL procedure successfullycompleted.

 

select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

7

 

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

/*查看moving_window_size的大小：
SELECT baseline_name,start_snap_time,end_snap_time,moving_window_sizeFROMdba_hist_baseline_details;

返回结果中，如果字段moving_window_size的值大于要修改为的保留天数，则必须执行
以下命令修改，这里应修改为7：
/* Formatted on 2015/4/9 星期四 11:08:14 (QP5 v5.185.11230.41888) */

BEGIN

   DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(WINDOW_SIZE=>7);

END;

*/

 

/* Formattedon 2015/4/9星期四 11:04:16 (QP5v5.185.11230.41888) */

BEGIN

   DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval   =>60,

                                                     retention  =>10080,

                                                     topnsql     => 100);

END;

 

五：删除AWR快照，再次查看SYSAUX表空间使用率,最后表空间使用率降低为38.42%

 

--查询最最小和最大快照ID

select min(snap_id),max(snap_id)fromdba_hist_snapshot;

 

--删除AWR快照

begin

dbms_workload_repository.drop_snapshot_range(

low_snap_id =>10758,

high_snap_id =>10900,

dbid => 387090299);

end;

 

建议与总结：  
oracle 10G后新增表空间SYSAUX空间，默认情况下，AWR的信息每隔一个小时收集一次AWR信息，并保留7天。这些数据存放在SYSAUX表空间中，建议修改为每隔4小时收集一次并保留3天的数据。

 

Sharon

2015.04.09

----------------------------------------------------------------------------------------------
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/44957259
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/44957259
转载须注明出处！
http://blog.csdn.net/sharqueen_wu/article/details/44957259