SYSAUX表空间占用过高问题处理
案例一: WRH$_ACTIVE_SESSION_HISTORY
在11.2.0.4版本中WRH$_ACTIVE_SESSION_HISTORY不会自动分区,需要手工执行语句(alter session set "_swrf_test_action" = 72)
才会创建一个新的分区,保留时间过期后系统会自动清理该分区的数据,这里是一个Bug,需要打补丁14084247
一、查看SYSAUX表空间段占用情况
col segment_name for a50
col owner for a15
set linesize 300 pagesize 200
select * from (
select owner,
segment_name,
segment_type,
sum(bytes) / 1024/1024/1024 "OBJECT_SIZE(GB)"
from dba_segments
where tablespace_name='SYSAUX'
group by owner, segment_name, segment_type
order by 4 desc ) where rownum <20;
OWNER SEGMENT_NAME SEGMENT_TYPE OBJECT_SIZE(GB)
--------------- -------------------------------------------------- ------------------ ---------------
SYS WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 12.3916626
SYS WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 1.37506104
SYS WRH$_LATCH TABLE PARTITION 1.25006104
SYS WRH$_SYSSTAT_PK INDEX PARTITION .859436035
SYS WRH$_SYSSTAT TABLE PARTITION .808654785
SYS WRH$_PARAMETER_PK INDEX PARTITION .750061035
SYS WRH$_LATCH_PK INDEX PARTITION .734436035
SYS WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION .695373535
SYS WRH$_EVENT_HISTOGRAM TABLE PARTITION .664123535
SYS WRH$_SQLSTAT TABLE PARTITION .617248535
SYS WRH$_PARAMETER TABLE PARTITION .601623535
SYS WRH$_SEG_STAT TABLE PARTITION .421936035
SYS WRH$_SERVICE_STAT_PK INDEX PARTITION .328186035
SYS WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION .265686035
SYS WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION .251037598
SYS WRH$_SYSTEM_EVENT TABLE PARTITION .195373535
SYS WRH$_SQLSTAT_PK INDEX PARTITION .187561035
SYS WRH$_SERVICE_STAT TABLE PARTITION .171936035
SYS WRH$_SEG_STAT_PK INDEX PARTITION .171936035
19 rows selected.
二、WRH$_ACTIVE_SESSION_HISTORY表分区情况
col segment_name for a50
set linesize 300 pagsize 200
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB
--------------- -------------------------------------------------- ------------------------------ ------------------ ----------
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1166899141_0 TABLE PARTITION 12.3916016
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
三、清空数据
SQL> alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_1166899141_0 update global indexes;
Table truncated.四、让系统自动分区
alter session set "_swrf_test_action" = 72;五、查看新的分区
查看新的分区:
col segment_name for a50
set linesize 300 pagsize 200
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB
------------------------------ -------------------------------------------------- ------------------------------ ------------------ ----------
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1166899141_0 TABLE PARTITION .000183105
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1166899141_41178 TABLE PARTITION .000061035
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
六、手工清理AWR报告方法
查看快照ID:
set serveroutput on
declare
CURSOR cur_part IS
SELECT partition_name from dba_tab_partitions
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
query1 varchar2(200);
query2 varchar2(200);
TYPE partrec IS RECORD (snapid number, dbid number);
TYPE partlist IS TABLE OF partrec;
Outlist partlist;
begin
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
dbms_output.put_line('--------------------------- ------- ----------');
for part in cur_part loop
query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query1 bulk collect into OutList;
if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
end loop;
end if;
query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query2 bulk collect into OutList;
if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
dbms_output.put_line('---');
end loop;
end if;
end loop;
end;
/
PARTITION NAME SNAP_ID DBID
--------------------------- ------- ----------
WRH$_ACTIVE_164166555_0 Min 1 164166555
WRH$_ACTIVE_164166555_0 Max 84 164166555
PL/SQL procedure successfully completed.
清空快照:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1,84);
exec dbms_workload_repository.create_snapshot();案例二: WRI$_ADV_OBJECTS
因为在12.2中，引入了新的特性：optimizer statistics advisor。优化器统计信息顾问每天都会在维护窗口运行，auto_stats_advisor_task多次运行，因而会消耗大量sysaux表空间。
一、查看SYSAUX表空间段占用情况
col segment_name for a30
col owner for a15
col segment_type for 20
set linesize 300 pagesize 200
select * from (
select owner,
segment_name,
segment_type,
sum(bytes) / 1024/1024/1024 "OBJECT_SIZE(GB)"
from dba_segments
where tablespace_name='SYSAUX'
group by owner, segment_name, segment_type
order by 4 desc ) where rownum <20;
OWNER SEGMENT_NAME SEGMENT_TYPE OBJECT_SIZE(GB)
--------------- -------------------------------------------------- ------------------------------------------------------ ---------------
SYS WRI$_ADV_OBJECTS TABLE 13.5595703
SYS WRI$_ADV_OBJECTS_IDX_01 INDEX 6.78808594
SYS WRI$_ADV_OBJECTS_PK INDEX 4.47558594
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX .65625
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PARTITION .458984375
SYS I_WRI$_OPTSTAT_H_ST INDEX .368164063
SYS HEATMAP SYSTEM STATISTICS .166015625
SYS SCHEDULER$_EVENT_LOG TABLE .1328125
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE PARTITION .08972168
SYS SYS_LOB0000007348C00005$$ LOBSEGMENT .086120605
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX .079101563
SYS WRI$_SEGADV_OBJLIST TABLE .078125
MDSYS SYS_LOB0000067306C00006$$ LOBSEGMENT .04510498
AUDSYS SYS_LOB0000017939C00030$$ LOB PARTITION .041381836
SYS WRI$_ADV_PARAMETERS_PK INDEX .032226563
SYS I_WRI$_OPTSTAT_HH_ST INDEX .029296875
SYS WRI$_ADV_PARAMETERS TABLE .026367188
SYS WRI$_SEGADV_OBJLIST_IDX_OBJ INDEX .022460938
SYS SYS_LOB0000069542C00004$$ LOBSEGMENT .016784668
19 rows selected.二、查看v$sysaux_occupants
set lines 120
col occupant_name format a30
select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
OCCUPANT_NAME SPACE_USAGE_KBYTES
------------------------------ ------------------
SM/ADVISOR 26113280
SM/OPTSTAT 1814976
SM/OTHER 250304
JOB_SCHEDULER 151744
SDO 79104
AUDSYS 64768
XDB 62272
XSOQHIST 39552
AO 39552
ORDIM/ORDDATA 16640
PL/SCOPE 12928
WM 6656
SMON_SCN_TIME 4480
TEXT 2880
SQL_MANAGEMENT_BASE 896
SM/AWR 576
ORDIM 448
AUTO_TASK 320
EM_MONITORING_USER 192
LOGSTDBY 128
STREAMS 64
ORDIM/SI_INFORMTN_SCHEMA 0
EM 0
XSAMD 0
ULTRASEARCH 0
STATSPACK 0
EXPRESSION_FILTER 0
AUDIT_TABLES 0
TSM 0
LOGMNR 0
ORDIM/ORDPLUGINS 0
ULTRASEARCH_DEMO_USER 0
32 rows selected.三、查看表记录分布情况
col task_name format a35
select * from (select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc) where rownum<5;
TASK_NAME CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK 122885188
SYS_AUTO_SPCADV513000613082022 100
SYS_AUTO_SPCADV807000612082022 100
SYS_AUTO_SPCADV305460712082022 99四、查看Optimizer Statistics Advisor 任务：
select OWNER,DESCRIPTION,TASK_NAME,ADVISOR_NAME,EXECUTION_START,EXECUTION_END from dba_advisor_tasks;
select RULE_ID, NAME, RULE_TYPE, DESCRIPTION from V$STATS_ADVISOR_RULES order by 1;五、解决方法
1.方法一: 清理重建
---12c清理
declare
v_tname varchar2(32767);
begin
v_tname := 'AUTO_STATS_ADVISOR_TASK';
dbms_stats.drop_advisor_task(v_tname);
end;
/
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
---19c清理
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
EXEC DBMS_STATS.INIT_PACKAGE();
SQL> alter session set container=<PDB_NAME>;
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER TABLE WRI$_ADV_OBJECTS MOVE');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD'); /* This index is available from 19c only */
如果需要禁用Optimizer Statistics Advisor Task，则执行以下语句:
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/
查看并修改禁用Optimizer Statistics Advisor Task:
--查看保留时间
select task_name,parameter_name, parameter_value
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='AUTO_STATS_ADVISOR_TASK' and parameter_name='EXECUTION_DAYS_TO_EXPIRE';
--修改保留时间 10天
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'AUTO_STATS_ADVISOR_TASK',
parameter => 'EXECUTION_DAYS_TO_EXPIRE',
value => 10);
END;
/
2.方法二: truncate表
### Check the no.of rows in WRI$_ADV_OBJECTS for Auto Stats Advisor Task ###
SQL> select count(*) from wri$_adv_objects where task_id=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');
COUNT(*)
----------
122885188
### Do CTAS from WRI$_ADV_OBJECTS to keep the rows apart from AUTO_STATS_ADVISOR_TASK ###
SQL> create table wri$_adv_objects_new as select * from wri$_adv_objects where task_id !=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');
SQL> select count(*) from wri$_adv_objects_new;
COUNT(*)
----------
359
### Truncate the table ###
SQL> truncate table wri$_adv_objects;
### Insert the rows from backed up table WRI$_ADV_OBJECTS_NEW to restore the records of ther advisor objects ###
SQL> insert /*+ APPEND */ into wri$_adv_objects select * from wri$_adv_objects_new;
SQL> commit;
SQL> drop table wri$_adv_objects_new;
### Reorganize the indexes ###
SQL> alter index wri$_adv_objects_idx_01 rebuild;
SQL> alter index wri$_adv_objects_pk rebuild;