[20230206]整理awr占用空间3.txt

--//同事反应一台服务器sysaux增加到3个数据文件,看看主要是那些对象占用磁盘空间以及图和清理.检查发现awr占用很大的磁盘空间.
--//2/3号决定做一次清理工作,结果差不多到了下班才完成,事后感觉自己做得不好,今天再删除1部分,看看这样操作是否可以更快一些,
--//做一个记录:

1.环境:
SYS@127.0.0.1:9014/ywdb> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.分析:
SYS@127.0.0.1:9014/ywdb> select * from (select * from dba_segments where tablespace_name='SYSAUX' order by bytes desc) where rownum<=3
  2  @ pr

--//结果我不贴出了,WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_3102522797_0分区占用61.77G.
--//使用来自WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy的脚本检查发现:

SYS@127.0.0.1:9014/ywdb> @ awr.sql
PARTITION  NAME              SNAP_ID DBID
---------------------------- ------- ----------
WRH$_ACTIVE_3102522797_0 Min       1 3102522797
WRH$_ACTIVE_3102522797_0 Max   23062 3102522797
PL/SQL procedure successfully completed.
--//这是我当时看到的结果,也就是这台机器从来就没有删除过WRH$_ACTIVE_SESSION_HISTORY的信息.
--//另外说明一下脚本执行超慢,主要原因我事后发现走的是index全扫描.事后我修改awr.sql脚本.

--//当前的情况:
SYS@127.0.0.1:9014/ywdb> select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = 3102522797;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
       22342        23061

--//我当时想还是保留部分awr信息.执行如下,但是我在执行前已经想到执行会很慢的,因为WRH$_ACTIVE_SESSION_HISTORY占用空间很大.
--//应该执行的删除操作,估计要产生大量的redo日志,所以我在执行前删除WRH$_ACTIVE_SESSION_HISTORY的分区WRH$_ACTIVE_3102522797_0,
--//不然根本无法完成.
alter session set "_swrf_test_action" = 72;
alter table WRH$_ACTIVE_SESSION_HISTORY drop partition WRH$_ACTIVE_3102522797_0;
exec dbms_workload_repository.drop_snapshot_range(1, 22342);

--//最后一步很慢,这是才想到我都把分区drop掉了,选择范围应该是1,23062.剩下其它不删除估计保留下来意义不大.
--//完成后我检查发现sysaux占用2XG.看来一些对象给回收看看.
--//我执行如下:
alter table WRH$_ACTIVE_SESSION_HISTORY shrink space cascade;

--//不知道为什么这步执行并不快,有点不理解,我已经drop最大的分区,分区索引也应该drop了.剩下的数据很少,为什么这么慢呢.
--//接着我对其它WRH$%的表也进行类似操作,最后sysaux占用2.3G.这步也是很慢.
--//完成后我检查日志切换,发现切换31次,每个日志文件1G,这样我估计我的操作产生接近30G的日志.

--//事后我想对于这样大量清理awr的操作,应该先drop相关分区.再执行
--//exec dbms_workload_repository.drop_snapshot_range(1, max);操作.

--//生成删除各个分区PARTITION_NAME like 'WRH$%3102522797_0'以及的表空间回收脚本.
--//大概步骤如下:
SYS@127.0.0.1:9014/ywdb> alter session set "_swrf_test_action" = 72;
Session altered.
--//不理解为什么要先设置这个!!

SYS@127.0.0.1:9014/ywdb> @awr -- ''
PARTITION  NAME             SNAP_ID
--------------------------- ------------------
WRH$_ACTIVE_3102522797_23064 Min 23063
WRH$_ACTIVE_3102522797_23064 Max 23064
---
WRH$_ACTIVE_3102522797_23065 Min 23065
WRH$_ACTIVE_3102522797_23065 Max 23207
---
WRH$_ACTIVE_3102522797_23209 Min
WRH$_ACTIVE_3102522797_23209 Max
---
WRH$_ACTIVE_SES_MXDB_MXSN Min
WRH$_ACTIVE_SES_MXDB_MXSN Max
---
PL/SQL procedure successfully completed.
--//最好修改一下脚本,利用索引取min,max的特性.因为我已经删除对应分区.了snap_id = 1 到 22342.
SYS@127.0.0.1:9014/ywdb> select min(snap_id), max(snap_id),dbid from sys.WRH$_latch group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID)       DBID
------------ ------------ ----------
       22343        23206 3102522797
--//实际上现在要删除snap_id 22343 到 23062

SELECT   'alter table '|| SEGMENT_NAME || ' DROP partition '|| PARTITION_NAME ||';' c100
  FROM dba_segments a
   WHERE owner = 'SYS'
   AND segment_name like 'WR%'
   AND SEGMENT_TYPE like 'TABLE PARTITION'
   AND PARTITION_NAME like 'WR%3102522797_0';

C100
----------------------------------------------------------------------------------------------------
alter table WRH$_DB_CACHE_ADVICE DROP partition WRH$_DB_CAC_3102522797_0;
alter table WRH$_DLM_MISC DROP partition WRH$_DLM_MI_3102522797_0;
alter table WRH$_EVENT_HISTOGRAM DROP partition WRH$_EVENT__3102522797_0;
alter table WRH$_SEG_STAT DROP partition WRH$_SEG_ST_3102522797_0;
alter table WRH$_SERVICE_STAT DROP partition WRH$_SERVIC_3102522797_0;
alter table WRH$_SERVICE_WAIT_CLASS DROP partition WRH$_SERVIC_3102522797_0;
alter table WRH$_SGASTAT DROP partition WRH$_SGASTA_3102522797_0;
alter table WRH$_SQLSTAT DROP partition WRH$_SQLSTA_3102522797_0;
alter table WRH$_SYSSTAT DROP partition WRH$_SYSSTA_3102522797_0;
alter table WRH$_LATCH_CHILDREN DROP partition WRH$_LATCH__3102522797_0;
alter table WRH$_LATCH_MISSES_SUMMARY DROP partition WRH$_LATCH__3102522797_0;
alter table WRH$_LATCH_PARENT DROP partition WRH$_LATCH__3102522797_0;
alter table WRH$_MVPARAMETER DROP partition WRH$_MVPARA_3102522797_0;
alter table WRH$_SYSTEM_EVENT DROP partition WRH$_SYSTEM_3102522797_0;
alter table WRH$_SYS_TIME_MODEL DROP partition WRH$_SYS_TI_3102522797_0;
alter table WRH$_TABLESPACE_STAT DROP partition WRH$_TABLES_3102522797_0;
alter table WRH$_WAITSTAT DROP partition WRH$_WAITST_3102522797_0;
alter table WRH$_INST_CACHE_TRANSFER DROP partition WRH$_INST_C_3102522797_0;
alter table WRH$_INTERCONNECT_PINGS DROP partition WRH$_INTERC_3102522797_0;
alter table WRH$_LATCH DROP partition WRH$_LATCH_3102522797_0;
alter table WRH$_OSSTAT DROP partition WRH$_OSSTAT_3102522797_0;
alter table WRH$_PARAMETER DROP partition WRH$_PARAME_3102522797_0;
alter table WRH$_ROWCACHE_SUMMARY DROP partition WRH$_ROWCAC_3102522797_0;
23 rows selected.
--//执行以上生成脚本!!这样可以加快下面的dbms_workload_repository.drop_snapshot_range(22343 ,23062)操作.

SYS@127.0.0.1:9014/ywdb> set timing on
SYS@127.0.0.1:9014/ywdb> exec dbms_workload_repository.drop_snapshot_range(22343 ,23062);
PL/SQL procedure successfully completed.
Elapsed: 00:02:20.98
SYS@127.0.0.1:9014/ywdb> set timing off
--//140秒完成.

SELECT 'alter table '|| SEGMENT_NAME || ' shrink space cascade;'
  FROM dba_segments a
   WHERE owner = 'SYS'
   AND segment_name like 'WR%'
   AND SEGMENT_TYPE like 'TABLE PARTITION'
   AND PARTITION_NAME like 'WR%3102522797_0';
no rows selected

--//昏!!应该先保存这个结果集合,然后再操作.不过现在不执行问题也不大.
--//使用vim编辑:%s/drop.*$/shrink space cascade;/生成执行脚本如下,我没有执行.
alter table WRH$_DLM_MISC shrink space cascade;
alter table WRH$_EVENT_HISTOGRAM shrink space cascade;
alter table WRH$_SEG_STAT shrink space cascade;
alter table WRH$_SERVICE_STAT shrink space cascade;
alter table WRH$_SERVICE_WAIT_CLASS shrink space cascade;
alter table WRH$_SGASTAT shrink space cascade;
alter table WRH$_SQLSTAT shrink space cascade;
alter table WRH$_SYSSTAT shrink space cascade;
alter table WRH$_LATCH_CHILDREN shrink space cascade;
alter table WRH$_LATCH_MISSES_SUMMARY shrink space cascade;
alter table WRH$_LATCH_PARENT shrink space cascade;
alter table WRH$_MVPARAMETER shrink space cascade;
alter table WRH$_SYSTEM_EVENT shrink space cascade;
alter table WRH$_SYS_TIME_MODEL shrink space cascade;
alter table WRH$_TABLESPACE_STAT shrink space cascade;
alter table WRH$_WAITSTAT shrink space cascade;
alter table WRH$_INST_CACHE_TRANSFER shrink space cascade;
alter table WRH$_INTERCONNECT_PINGS shrink space cascade;
alter table WRH$_LATCH shrink space cascade;
alter table WRH$_OSSTAT shrink space cascade;
alter table WRH$_PARAMETER shrink space cascade;
alter table WRH$_ROWCACHE_SUMMARY shrink space cascade;

--//事后我也看了以前写的http://blog.itpub.net/267265/viewspace-2673515/ => [20200115]重新建立awr report.txt
--//安全期间,我还是没敢在生产系统做这样的操作!!