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

[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 --//安全期间,我还是没敢在生产系统做这样的操作!!