注意事项：
    1、严格跟踪删除awr会话是否阻塞其他回话，是否存在严重的等待事件
    2、时刻关注归档空间情况，删除awr操作会产生大量归档日志

1、查询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 ;

2、查询sysaux表空间大字段对象情况

select owner, segment_name, segment_type, bytes / 1024 / 1024
  from (select *
          from dba_segments
         where tablespace_name = 'SYSAUX'
         order by bytes desc)
 where rownum < 20;

 

3、确认awr的snap_id情况，awr基线情况

select * from sys.WRM$_SNAPSHOT_DETAILS where rownum<5;
select max(snap_id), min(snap_id) from sys.WRM$_SNAPSHOT_DETAILS;
select max(snap_id), min(snap_id) from sys.WRM$_SNAPSHOT;
select max(snap_id), min(snap_id) from dba_hist_snapshot;
select * from dba_hist_baseline_details;

--清理指定awr基线

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'perfbl');

4、删除指定dbid、snap_id范围的快照

select sid from v$mystat where rownum=1;
select spid from v$process where addr in (select paddr from v$session where sid=&sid);
exec dbms_workload_repository.drop_snapshot_range(810,35550,2519839369);

5、观察清理awr会话sql执行情况

select sid,status,sql_id,sql_child_number,sql_exec_start,blocking_session,blocking_session_status,event,seconds_in_wait from v$session where sid=&sid;

重点：删除awr会话会按照以下sql_id顺序执行，中间有遗漏sql_id，执行时间长短不一
--期间可用以下sql跟踪会话具体sql的执行统计情况

select sql_id,child_number,executions,buffer_gets,disk_reads,buffer_gets/(executions+1),disk_reads/(executions+1) from v$sql where sql_id='5bvk329b8qz3p';

--用以下sql，查询具体删除对象的查询结果

select * from WRH$_EVENT_HISTOGRAM  tab
 where (35830 <= tab.snap_id and tab.snap_id <= 35831 and dbid = 2513064869)
   and not exists
 (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid)
   and (tab.snap_id >= b.start_snap_id)
   and (tab.snap_id <= b.end_snap_id));

--查看sql执行计划

select * from table(dbms_xplan.display_cursor('854knbb15976z',0));

c2a3w6dhws1v0
8n9fhq1rafbdy
40p1nuftyahzm
3wzq11a3p451a
f0vqzxggcjym5
bsbv3ch4gb4u4
6v0ggz9c764w0
btwc4xyd2zh9m
51u2p770s9uwp
39tn1f1n1xnt2
93dudrsmwtkbu
5bvk329b8qz3p
4uyb91vtx0c8p

6、清理awr完毕后，再次查询sysaux大字段对象情况，查看高水位线是否下降(正常情况下不会下降)

select owner, segment_name, segment_type, bytes / 1024 / 1024
  from (select *
          from dba_segments
         where tablespace_name = 'SYSAUX'
         order by bytes desc)
 where rownum < 20;

7、通过mv操作，降低大字段对象的高水位线
--查看mv表的索引状态、并导出索引ddl语句

select status from dba_indexes where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
select dbms_metadata.get_ddl('INDEX','WRH$_ACTIVE_SESSION_HISTORY_PK','SYS') from dual;

--mv指定表

alter table WRH$_ACTIVE_SESSION_HISTORY move;

--mv指定分区表的分区

select 'alter table '||table_name||' move partition '||partition_name||' tablespace sysaux;' from dba_tab_partitions where table_name ='WRH$_ACTIVE_SESSION_HISTORY';

--mv表后，查看索引是否失效

select status from dba_indexes where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';

--索引失效，则重建索引

alter index sys.WRH$_SQL_BIND_METADATA_PK rebuild;