SYSAUX表空间使用率高问题处理-容器云计算,Devops,DBA,网络安全。

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

发表者：admin分类：数据库2015-07-28 17:10:39 阅读[2324]

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

view source print ?
01.SELECT * FROM (
02.SELECT D.TABLESPACE_NAME,
03.SPACE || 'M' "SUM_SPACE(M)",
04.BLOCKS "SUM_BLOCKS",
05.SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
06.ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
07."USED_RATE(%)",
08.FREE_SPACE || 'M' "FREE_SPACE(M)"
09.FROM ( SELECT TABLESPACE_NAME,
10.ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11.SUM (BLOCKS) BLOCKS
12.FROM DBA_DATA_FILES
13.GROUP BY TABLESPACE_NAME) D,
14.( SELECT TABLESPACE_NAME,
15.ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16.FROM DBA_FREE_SPACE
17.GROUP BY TABLESPACE_NAME) F
18.WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19.UNION ALL
20.SELECT D.TABLESPACE_NAME,
21.SPACE || 'M' "SUM_SPACE(M)",
22.BLOCKS SUM_BLOCKS,
23.USED_SPACE || 'M' "USED_SPACE(M)",
24.ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25.NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26.FROM ( SELECT TABLESPACE_NAME,
27.ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28.SUM (BLOCKS) BLOCKS
29.FROM DBA_TEMP_FILES
30.GROUP BY TABLESPACE_NAME) D,
31.( SELECT TABLESPACE_NAME,
32.ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33.ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34.FROM V$TEMP_SPACE_HEADER
35.GROUP BY TABLESPACE_NAME) F
36.WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37.ORDER BY 1)
38.WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');

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

view source print ?
1.SELECT occupant_name "Item",
2.space_usage_kbytes / 1048576 "Space Used (GB)",
3.schema_name "Schema",
4.move_procedure "Move Procedure"
5.FROM v$sysaux_occupants
6.ORDER BY 1

三：修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除
www.it165.net
view source print ?
01.SQL> select dbms_stats.get_stats_history_retention from dual;
02.
03.GET_STATS_HISTORY_RETENTION
04.---------------------------
05.31
06.
07.SQL> exec dbms_stats.alter_stats_history_retention(7);
08.PL/SQL procedure successfully completed.
09.
10.SQL> select dbms_stats.get_stats_history_retention from dual;
11.
12.GET_STATS_HISTORY_RETENTION
13.---------------------------
14.7
四：修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改

view source print ?
1.SQL> begin
2.dbms_workload_repository.modify_snapshot_settings (
3.interval => 60,
4.retention => 10080,
5.topnsql => 100
6.);
7.end;

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

view source print ?
1.select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID
2.
3.begin
4.dbms_workload_repository.drop_snapshot_range(
5.low_snap_id => 10758,
6.high_snap_id => 10900,
7.dbid => 387090299);
8.end;