清理SYSAUX（其三）

2023-05-19 16:21 明朝散发阅读(50) 评论(0) 编辑收藏举报

SQL> col occupant_name format a30
select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;

OCCUPANT_NAME                  SPACE_USAGE_KBYTES
------------------------------ ------------------
SM/OPTSTAT                                9112960
SM/AWR                                     137728

SELECT occupant_name, schema_name, move_procedure,
space_usage_kbytes
FROM v$sysaux_occupants
ORDER BY 1;

--各个组件的含义
SM/AWR：AWR快照

SM/OPTSTAT：This stores older version of optimizer statistics.
We have new capability in Oracle Database 10g that allows you restore old stats in case you encounter execution plan regression when the stats are refreshed. The default retention for this data is 31 days.

SM/ADVISOR：This refers to the advisor frameworks tables. Advisors like SQL Tuning Advisor, SQL Access Advisor, ADDM use this infrastructure.

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;