Clean WRH$_ACTIVE_SESSION_HISTORY in SYSAUX
SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE --------- ----- ------------------------------ -------------- ---------------
ASH 408.0 WRH$_ACTIVE_SESSION_HISTORY - 98% TABLE PARTITION .WRH$_ACTIVE_21536658_7139e ASH 41.0 WRH$_ACTIVE_SESSION_HISTORY_PK - 98% INDEX PARTITION .WRH$_ACTIVE_21536658_7139 FIXED 36.0 WRH$_SYSMETRIC_HISTORY - 98% TABLE FIXED 26.0 WRH$_SYSMETRIC_HISTORY_INDEX - 98% INDEX -- -- Check the Snapshot retention, it works fine (only 8 snapshots exist). -- SQL> SELECT snap_interval, retention, most_recent_purge_time FROM sys.wrm$_wr_control; SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME ----------------- ----------------- ------------------------- +00000 01:00:00.0 +00007 00:00:00.0 27-NOV-13 12.06.06.995 AM SQL> select dbid, count(*) from SYS.WRM$_SNAPSHOT group by dbid; DBID COUNT(*) ---------- ---------- 21536658 8 -- One interesting thing is DBA_HIST_SNAPSHOT and SYS.WRM$_SNAPSHOT are different SQL> select * from DBA_HIST_SNAPSHOT; no rows selected -- -- Run below query to identify the big tables in SYSAUX -- SQL> SELECT * FROM ( SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type FROM dba_segments WHERE tablespace_name = 'SYSAUX' ORDER BY dim_Mb DESC) WHERE ROWNUM < 5; DIM_MB SEGMENT_NAME SEGMENT_TYPE ---------- ------------------------------ ------------ 544 WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 128 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 88 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 64 WRH$_SYSMETRIC_HISTORY TABLE -- -- Follow Oracle DOC ID 387914.1 to clean table WRH$_ACTIVE_SESSION_HISTORY -- SQL> select table_name,partition_name 2 from dba_tab_partitions 3 where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7139 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 1653076 SQL> alter session set "_swrf_test_action" = 72; Session altered. SQL> 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_21536658_7139 Min 7139 21536658 WRH$_ACTIVE_21536658_7139 Max 7881 21536658 --- PL/SQL procedure successfully completed. -- After above PL/SQL block ran, it became three partitions SQL> select table_name,partition_name from dba_tab_partitions where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7139 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7884 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN -- Run drop Procedure SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>7139,high_snap_id =>7881); PL/SQL procedure successfully completed. -- Now, WRH$_ACTIVE_SESSION_HISTORY has no rows SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 0 SQL> SELECT * FROM ( SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type FROM dba_segments WHERE tablespace_name = 'SYSAUX' ORDER BY dim_Mb DESC) WHERE ROWNUM < 5; DIM_MB SEGMENT_NAME SEGMENT_TYPE ---------- ------------------------------ ----------- 128 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 88 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 64 WRH$_SYSMETRIC_HISTORY TABLE 55 I_WRI$_OPTSTAT_H_ST INDEX -- But partitions were NOT dropped SQL> select table_name,partition_name from dba_tab_partitions where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7139 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7884 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN -- -- Follow DOC ID 287679.1 to shrink the tablespace -- SQL> SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a WHERE NOT EXISTS (SELECT 1 FROM wrm$_snapshot WHERE snap_id = a.snap_id AND dbid = a.dbid AND instance_number = a.instance_number ); ORPHANED_ASH_ROWS ----------------- 0 -- To reclaim the freed space (since row movement is enabled on WRH$_ACTIVE_SESSION_HISTORY by default) SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space; Table altered. SQL> column OCCUPANT_NAME format a15 SQL> SELECT occupant_name, occupant_desc, space_usage_kbytes FROM v$sysaux_occupants WHERE occupant_name LIKE '%AWR%'; OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES ------------- ------------------------------------------------------ ------------------ SM/AWR Server Manageability - Automatic Workload Repository 300416 -- -- Check again after a few days: -- It seems the old partition was dropped automatically and a new one was created. SQL> select table_name,partition_name from dba_tab_partitions where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7885 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN -- And new entries are written into table WRH$_ACTIVE_SESSION_HISTORY SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 31569 -- while the Snapshot retention keeps unchanged. SQL> SELECT snap_interval, retention, most_recent_purge_time FROM sys.wrm$_wr_control; SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME ----------------- ----------------- ------------------------- +00000 01:00:00.0 +00007 00:00:00.0 11-DEC-13 12.00.29.165 AM
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29135257/viewspace-1789499/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- WRH$_ACTIVE_SESSION_HISTORYSession
- ORACLE 10g SYSAUX表空間快速增長之WRH$_ACTIVE_SESSION_HISTORY篇Oracle 10gUXSession
- 11gr2 rac WRH$_ACTIVE_SESSION_HISTORY未自動清理導致SYSAUX空間過度增長SessionUX
- 一次WRH$_ACTIVE_SESSION_HISTORY問題處理Session
- oracle 10g SYSAUX表空間快速增長之WRH$_SQL_PLAN篇Oracle 10gUXSQL
- Oracle AWR wri$, wrh$ and wrm$Oracle
- WRH$_SQL_PLAN 被鎖SQL
- sysaux bigUX
- SYSAUX 說明UX
- The SYSAUX Tablespace (40)UX
- 《Clean Code》
- 計算sysaux中各主件對sysaux空間的使用!UX
- v$active_session_history檢視Session
- 聊聊 clean code
- 《The Clean Coder》中的「Clean」如何譯?【已結貼】
- 32、SYSAUX表空間UX
- v$active_session_history檢視[轉]Session
- how to clean failed crsAI
- V$ACTIVE_SESSION_HISTORY檢視的使用Session
- Oracle清理SYSAUX表空間OracleUX
- ORACLE的SYSAUX 表空間OracleUX
- Creating the SYSAUX Tablespace (72)UX
- 轉 SYSAUX tablespace 100% fullUX
- 前端的Clean Architecture前端
- Clean architecture for the rest of usREST
- 2.5.4.1 關於SYSAUX表空間UX
- 認識 SYSAUX 表空間(zt)UX
- 《Clean Code》閱讀筆記筆記
- 一個clean框架的demo框架
- Clean Code 閱讀總結
- Clean up a failed CRS installAI
- Clean context menu under MacOSXContextMac
- Clean CUPS cache under MacOSXMac
- 11g v$active_session_history的新增列Session
- 轉載--V$ACTIVE_SESSION_HISTORY檢視的使用Session
- V$ACTIVE_SESSION_HISTORY 檢視中包含的資訊Session
- SYSAUX表空間清理之SM/OPTSTATUX
- AWR佔用sysaux表空間太大UX