個人網站: https://kxodia.com
今天發生了錯誤訊息 ORA-1654: unable to extend index SYS.WRI$_ADV_OBJECTS_IDX_02 by 8192 in tablespace SYSAUX
執行@?/rdbms/admin/awrinfo.sql後可以發現占用SYSAUX最多空間的Occupant Name = SM/AWR
接著查看占用最大的Segment
col segment_name format a30
col owner format a10
col tablespace_name format a10
col segment_type format a15
select segment_name,owner,tablespace_name,bytes/1024/1024 "SIZE(MB)",segment_type
from dba_segments
where tablespace_name='SYSAUX'
order by bytes desc;
可以看到前四名分別為
- WRI$_ADV_OBJECTS
- WRI$_ADV_OBJECTS_IDX_01
- WRI$_ADV_OBJECTS_IDX_02
- WRI$_ADV_OBJECTS_PK
這裡我們可以知道SYSAUX空間不足是由WRI$_ADV_OBJECTS所造成，WRI$_ADV_OBJECTS為加害人，並非被害人。
WRI$_ADV_OBJECTS為 AUTO_STATS_ADVISOR_TASK 或 INDIVIDUAL_STATS_ADVISOR_TASK 保留的大量舊記錄會導致佔據大量 SYSAUX 空間。
- AUTO_STATS_ADVISOR_TASK 用於自動統計顧問任務
- INDIVIDUAL_STATS_ADVISOR_TASK 用於手動統計顧問任務
以下指令可以查出AUTO_STATS_ADVISOR_TASK 幾天後變成EXPIRED
SQL> col TASK_NAME format a25
SQL> col parameter_name format a35
SQL> col parameter_value format a20
SQL> set lines 120
SQL> select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
TASK_NAME PARAMETER_NAME PARAMETER_VALUE
------------------------- ---------- ------------- --------
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30
19c預設為30,較低的版本則預設為UNLIMITED
也能透過以下指令修改為10天
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);自動刪除: 正常情況下AUTO_STATS_ADVISOR_TASK 的 EXECUTION_DAYS_TO_EXPIRE 參數設置為 30（默認情況下）。因此，超過 30 天的舊記錄被標記為過期。
手動刪除: 可以使用以下命令手動清除過期的統計顧問記錄，而不是依賴自動清除窗口。這將清除超過 30 天保留期的舊統計顧問記錄。
SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;
刪除後需要alter table WRI$_ADV_OBJECTS move;與rebuild indexes. 來釋放空間。
SQL> alter table WRI$_ADV_OBJECTS move;
SQL> alter index WRI$_ADV_OBJECTS_PK rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;
以下指令確認實際上有幾筆資料
SQL> col task_name format a25
SQL> col EXECUTION_NAME format a15
SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';
如果比數太多可能會產生大量undo,可以參考SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)使用truncate的方式
#執行次數調查
select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';#最後一次執行的紀錄
select ADVISOR_NAME,TASK_NAME,LAST_EXECUTION,EXECUTION_END,STATUS from dba_advisor_tasks where task_name like 'AUTO%';根據Bug 26749785 - Enhancement to have more controls on auto Statistics Advisor (Doc ID 26749785.8)/How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)
增強功能以對自動統計顧問進行更多控制
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
FALSE
參考文件:
- How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
- SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
