ORA-1654:WRI$_ADV_OBJECTS in SYSAUX

症狀 ORA-1654

今天在alert log發現了錯誤訊息 ORA-1654: unable to extend index SYS.WRI$_ADV_OBJECTS_IDX_02 by 8192 in tablespace SYSAUX

分析

當發生了SYSAUX空間問題，可以透過執行@?/rdbms/admin/awrinfo.sql調查SYSAUX的佔用狀況，執行後發現占用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_typefrom dba_segmentswhere 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

19c預設為30,較低的版本則預設為UNLIMITED

處理方式

我們可以透過指令修改EXPIRED天數，以下範例修改為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

ORA-1654 : SYS.WRI$_ADV_OBJECTS_IDX_02

症狀 ORA-1654

分析

處理方式

參考文件

Leave a Comment Cancel Reply