如何分析SYSAUX表空间占用过大或增长过快问题
如何分析SYSAUX表空间占用过大或增长过快问题
SYSAUX表空间也称为系统辅助表空间,这个是Oracle 10g版本开始推出的新功能，它是SYSTEM表空间的辅助表空间。Oracle 9i等早期版本中某些使用SYSTEM表空间存放数据或其自身表空间存放数据的一些组件和产品，现在改为使用SYSAUX表空间存放数据。每个数据库(Oracle 10g或更高版本)都必须拥有SYSAUX表空间。辅助表空间SYSAUX用于存储数据库组件的相关数据，例如，OEM库（Oracle Enterprise Manager Repository）、AWR快照信息库、统计信息、审计信息等。如果SYSAUX表空间过大(使用率过高)或增长过快，DBA必须取分析、定位原因，并采取相关措施解决问题，避免数据库出现异常。下面简单整理/总结一下，如何分析SYSAUX表空间占用过大或增长过快的思路与方法。仅供学习参考。
首先总体分析，使用下面脚本分析SYSAUX表空间的历史增长情况，对表空间增长的趋势，以及从什么时间开始出现暴增等大概信息有一个基本的了解掌握。主要分析SYSAUX表空间是否出现暴增情况，为什么关注是否出现暴增呢？因为这个信息非常关键，有些案例中，这个信息有助于我们分析"破案”。当然，大部分场景根本没有表空间使用暴增的情况。
--Oracle 10/11g
set linesize 640
set pagesize 36
col snap_id for 999999
col con_id for 99999
col pdbname for a16
col ts_name for a20
col rtime for a18
col ts_size_mb for 999999.9
col ts_used_mb for 999999.9
col pct_used for 99.99
select u.snap_id,
to_char(s.begin_interval_time, 'yyyy-mm-dd hh24') begin_time,
to_char(s.end_interval_time, 'yyyy-mm-dd hh24') end_time,
t.name,
round(u.tablespace_size * ts.block_size / 1024 / 1024, 2) ts_size_mb,
round(u.tablespace_usedsize * ts.block_size / 1024 / 1024, 2) ts_used_mb,
round((u.tablespace_size - u.tablespace_usedsize) * ts.block_size / 1024 / 1024, 2) ts_free_mb,
round(u.tablespace_usedsize / u.tablespace_size * 100, 2) pct_used
from dba_hist_tbspc_space_usage u,
v$tablespace t,
dba_hist_snapshot s,
dba_tablespaces ts
where u.tablespace_id = t.ts#
and u.snap_id = s.snap_id
and t.name = ts.tablespace_name
and s.instance_number = 1
and t.name = upper('&tablespace_name')
and s.end_interval_time > sysdate - 7
order by snap_id desc;
--多租户架构
set linesize 640
set pagesize 36
col snap_id for 999999
col con_id for 99999
col pdbname for a16
col ts_name for a20
col rtime for a18
col ts_size_mb for 999999.99
col ts_used_mb for 999999.99
col pct_used for 99.99
select a.snap_id,
a.con_id,
e.name pdbname,
c.tablespace_name ts_name,
to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
2) ts_free_mb,
round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
from cdb_hist_tbspc_space_usage a,
(select tablespace_id,
nb.con_id,
substr(rtime, 1, 10) rtime,
max(snap_id) snap_id
from dba_hist_tbspc_space_usage nb
group by tablespace_id, nb.con_id,substr(rtime, 1, 10)) b,
cdb_tablespaces c,
v$tablespace d,
V$CONTAINERS e
where a.snap_id = b.snap_id
and a.tablespace_id = b.tablespace_id
and a.con_id=b.con_id
and a.con_id=c.con_id
and a.con_id=d.con_id
and a.con_id=e.con_id
and a.tablespace_id=d.TS#
and d.NAME=c.tablespace_name
and c.tablespace_name=upper('&tablespace_name')
and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
order by a.con_id,a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;
然后分析SYSAUX表空间中，统计AWR/统计信息/审计信息数据占用了多少空间：
SET LINESIZE 720;
SET PAGESIZE 36;
COL "Item" FOR A16;
COL SCHEMA_NAME FOR A30;
SELECT OCCUPANT_NAME "Item",
SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
SCHEMA_NAME ,
MOVE_PROCEDURE
FROM V$SYSAUX_OCCUPANTS
WHERE SPACE_USAGE_KBYTES > 1048576
ORDER BY "Space Used (GB)" DESC;
根据OCCUPANT_NAME列的数据来判断：
- 如果该列为SM/AWR， 它是Server Manageability - Automatic Workload Repository的缩写，那么表示AWR信息占用空间的大小
- 如果该列为SM/OPTSTAT，它是Server Manageability - Optimizer Statistics History的缩写，那么表示统计信息占空间的大小
- 如果该列为AUDIT_TABLES，表示审计信息占用的空间大小。
- 如果该列为LOGMNR，表示logminer产生的数据占用了表空间
上面是从高一点的维度分析，其实我们还可以继续下钻分析SYSAUX表空间下面哪些对象/表占用了大量的空间，一般只需关注最大的几个表，例如AUD$,WRH$_ACTIVE_SESSION_HISTORY,WRR$_REPLAY_CALL_INFO
查看表空间里面里面TOP 50大小的数据库对象
SET LINESIZE 720;
SET PAGESIZE 36;
COL OWNER FOR A16;
COL SEGMENT_TYPE FOR A20;
COL SEGMENT_NAME FOR A38;
COL TABLESPACE_NAME FOR A20;
SELECT *
FROM (SELECT OWNER,
SEGMENT_TYPE,
SEGMENT_NAME,
ROUND(SUM(BYTES) / 1024 / 1024/1024,2) TOTAL_SIZE_GB,
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME =UPPER('&TABLESPACE_NAME')
GROUP BY OWNER,
SEGMENT_TYPE,
SEGMENT_NAME,
TABLESPACE_NAME
ORDER BY TOTAL_SIZE_GB DESC)
WHERE ROWNUM <= 50;
通过上面两个维度，我们基本就可以知道那些组件/对象占用了SYSAUX的表空间，我们可以从这几个方面/维度继续分析/定位原因了
SYSAUX表空间设置不当
这种情况比较少见，但是也不是没有，例如，SYSAUX表空间设置过小，SYSUAX的最大大小为4G，没有设置数据文件自增长。空间过小引起的表空间告警。这种只需调整表空间大小(设置表空间的数据文件自增长或增加数据文件)
这种情况，其实使用下面脚本分析一下即可给出判断。确切来说，这种情况不属于SYSAUX表空间占用过大的情况，但是这种情况也会触发表空间告警，属于资源不足或数据库表空
间设置不当。
SET PAGESIZE 9999 LINESIZE 180;
TTI 'Tablespace Usage Status'
COL TABLESPACE_NAME FOR A20;
COL TBS_MAX_SIZE FOR 99999.99;
COL TABLESPACE_SIZE FOR 99999.99;
COL TBS_AVABLE_SIZE FOR 999999.99;
COL "USED_RATE(%)" FOR A16;
COL "ACT_USED_RATE(%)" FOR A16;
COL "FREE_SIZE(GB)" FOR 99999999.99;
SELECT UPPER(F.TABLESPACE_NAME) AS "TABLESPACE_NAME",
ROUND(D.MAX_BYTES,2) AS "TBS_MAX_SIZE" ,
ROUND(D.AVAILB_BYTES ,2) AS "TBS_ACT_SIZE",
ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "TBS_USED_SIZE",
ROUND(F.USED_BYTES, 2) AS "FREE_SIZE(GB)",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
2),
'999.99') AS "USED_RATE(%)",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)/D.MAX_BYTES*100,
2),
'999.99') AS "ACT_USED_RATE(%)",
ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2) AS "TBS_AVABLE_SIZE"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY "ACT_USED_RATE(%)" DESC;
审计信息占用过大
如果审计信息占用了大量空间(SM/AWR),那么一般表现为AUD$表非常大，考虑取消某些审计，调整审计级别/粒度设置。如果公司政策或规范要求非常严格，那么只能扩展表空间大小。
可以用下面SQL查询/确认那种审计占用了大量空间
select action_name,count(*) from dba_audit_trail group by action_name;
一般是LOGON和LOGOFF类型的审计数据最多。可以截断占用空间最大的AUD$表（需要确认审计信息是否需要保留）
--截断AUD$
truncate table sys.aud$ reuse storage;
alter table sys.aud$ deallocate unused keep xxxm;
AWR数据占用过大
如果AWR数据占用过大，那么一般情况下是由于AWR信息没有及时清理引起的.
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql
通过上面SQL，你可以得到AWR的数据空间占用分布信息，如下例子所示(部分信息)
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 7,096.3 MB ( 43% of 16,384.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 6,961.3 MB ( 98.1% )
| Schema XDB occupies 62.9 MB ( 0.9% )
| Schema AUDSYS occupies 50.3 MB ( 0.7% )
| Schema SYSTEM occupies 12.6 MB ( 0.2% )
| Schema WMSYS occupies 6.6 MB ( 0.1% )
| Schema GSMADMIN_INT occupies 1.4 MB ( 0.0% )
| Schema DBSNMP occupies 1.2 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/AWR SYS 5,137.9 MB
| AUDIT_TABLES SYS 1,262.0 MB
| XDB XDB 62.9 MB
| AUDSYS AUDSYS 50.3 MB
| SM/OTHER SYS 49.9 MB
| SM/ADVISOR SYS 48.2 MB
| SM/OPTSTAT SYS 14.8 MB
| LOGMNR SYSTEM 10.8 MB
| JOB_SCHEDULER SYS 8.9 MB
| WM WMSYS 6.6 MB
| SMON_SCN_TIME SYS 3.3 MB
| PL/SCOPE SYS 2.9 MB
| SQL_MANAGEMENT_BASE SYS 2.7 MB
| AO SYS 1.9 MB
| STREAMS SYS 1.7 MB
| LOGSTDBY SYSTEM 1.6 MB
| EM_MONITORING_USER DBSNMP 1.2 MB
| AUTO_TASK SYS 0.6 MB
| EM SYSMAN 0.0 MB
| EXPRESSION_FILTER EXFSYS 0.0 MB
| ORDIM ORDSYS 0.0 MB
| ORDIM/ORDDATA ORDDATA 0.0 MB
| ORDIM/ORDPLUGINS ORDPLUGINS 0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA 0.0 MB
| SDO MDSYS 0.0 MB
| STATSPACK PERFSTAT 0.0 MB
| TEXT CTXSYS 0.0 MB
| TSM TSMSYS 0.0 MB
| ULTRASEARCH WKSYS 0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST 0.0 MB
| XSAMD OLAPSYS 0.0 MB
| XSOQHIST SYS 0.0 MB
|
| Others (Unaccounted space) 428.3 MB
|
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema GSMADMIN_INT occupies 1.4 MB
|
| Total space 1.4 MB
|
*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM 426.9 MB
|
| Total space 426.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 30 mins snapshot INTERVAL:
| AWR size/day 168.6 MB (3,596 K/snap * 48 snaps/day)
| AWR size/wk 1,180.0 MB (size_per_day * 7) per instance
|
| Estimates based on 48 snaps in past 24 hours:
| AWR size/day 168.6 MB (3,596 K/snap and 48 snaps in past 24 hours)
| AWR size/wk 1,180.0 MB (size_per_day * 7) per instance
|
**********************************
(3a) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
ASH 1,400.3 27.3 980 45.9 321.6 87% : 13%
FIXED 1,348.6 26.2 944 44.2 309.7 45% : 55%
EVENTS 455.8 8.9 319 15.0 104.7 42% : 58%
SQLPLAN 392.0 7.6 274 12.9 90.0 65% : 35%
SQLBIND 240.0 4.7 168 7.9 55.1 50% : 50%
SQL 100.7 2.0 70 3.3 23.1 66% : 34%
SPACE 86.3 1.7 60 2.8 19.8 63% : 37%
SQLTEXT 16.9 0.3 12 0.6 3.9 94% : 6%
RAC 0.6 0.0 0 0.0 0.1 50% : 50%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
ASH 265.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_SESSION_HISTORY_37363 - 99% TABLE PARTITION
ASH 257.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_SESSION_HISTORY_37363 - 97% TABLE PARTITION
..........................................................
主要有几种情况：
- 1：AWR数据保留周期太长。这种情况可以通过设置AWR的保留时间来减少AWR信息的存储空间。如果是默认的保留周期，不建议这样做。
- 2：一些数据库设置或问题引起的。例子，碰到过一起案例，由于网络调整，导致数据库中某些使用了dblink的SQL出现大量和长时间的 'SQL*Net break/reset to client'等待，导致MMON进程采集了大量这些SQL存储在WRH$_ACTIVE_SESSION_HISTORY中，导致这个表的数据从某个时间点后采集了大量的数据。
关于调整AWR的保留时间来减小AWR信息的存储空间。通过如下的SQL语句可以获取AWR的保留时间：
SELECT * FROM DBA_HIST_WR_CONTROL;
通过如下的SQL语句可以设置AWR信息的保留时间为N天（例如：72460），每隔1小时收集一次AWR信息：
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>7*24*60);
在以上设置完成后，可以删除不需要的AWR快照信息，从而释放SYSAUX表空间，相关SQL语句如下所示：
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => xxx,
HIGH_SNAP_ID => xxx,
DBID => xxxx);
END;
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通过DELETE操作来完全清理工作的。所以，执行完成后，并不会真正的释放空间归还给SYSAUX表空间。此时，应该对相关的大表执行降低高水位线操作来释放空间。
还有一些非常规操作，这些最好不要在生产环境操作，可用于测试环境或紧急情况下使用：
set linesize 680
col sql_cmd for a90;
select distinct 'truncate table '||segment_name||';' as sql_cmd
,s.bytes/1024/1024 as table_size
from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;
更多详细信息可以参考学习资料[1]
统计信息占用过大
如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL; --查询统计信息的保留时间
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); --设置统计信息的保留时间
如果发现统计信息占用了SYSAUX表空间的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理或调整保留周期。
exec dbms_stats.purge_stats(sysdate-&days);
--设置历史统计信息保留天数
exec dbms_stats.alter_stats_history_retention(&day);
