[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
--//安全期間,我還是沒敢在生產系統做這樣的操作!!