2022-01-21|閱讀時間 ‧ 約 9 分鐘

ORA-1654 : SYS.WRI$_ADV_OBJECTS_IDX_02

今天發生了錯誤訊息 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)

分享至

成為作者繼續創作的動力吧！

工程師Kxodia的技術報告

追蹤

ORA-1654 : SYS.WRI$_ADV_OBJECTS_IDX_02

工程師Kxodia的技術報告的沙龍的其他內容

你可能也想看

發表回應

ORA-1654 : SYS.WRI$_ADV_OBJECTS_IDX_02

工程師Kxodia的技術報告的沙龍 的其他內容

你可能也想看

發表回應

工程師Kxodia的技術報告的沙龍的其他內容