ORA-1654 : SYS.WRI$_ADV_OBJECTS_IDX_02

2022/01/21閱讀時間約 8 分鐘

今天發生了錯誤訊息 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的技術報告

我是肯佐迪亞，畢業於交通大學【理學碩士】，一直從事資訊科技相關領域，目前擔任Oracle原廠工程師，主要分享一些技術應用與實務經驗，若有任何問題歡迎與我聯繫 https://kxodia.com

留言0

查看全部

發表第一個留言支持創作者！