Отображение дочерних | ||
---|---|---|
|
Проблема наблюдается рост ТП SYSAUX (версия Oracle 12 и выше................)
Oracle Database 12.2 -- SYSAUX space usage is high, WRI$_ADV_OBJECTS is top consumer
Problem
SYSAUX tablespace usage is high
...
Due to large number of runs, considerable amount of space used in SYSAUX and will keep growing
Solution
How to Purge WRI$_ADV_OBJECTS the Recommend Way
In versions below 19.1, the parameter which controls automatic purging of old records of the Optimizer the Optimizer Statistics Advisor is set to UNLIMITED
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 UNLIMITED
The parameter is modifiable beginning with versions:
- Patch 30138470 12.2.0.1.191015 (Oct 2019) Database Release Update (DB RU)
- Patch 28822489 18.5.0.0.190115 (Jan 2019)
- 19.1.0
With the above mentioned versions available, the default value for the EXECUTION_DAYS_TO_EXPIRE parameter is set to 30 days, and old records will be automatically purged.
Also, auto purging can be forced with:
SQL> exec prvt_advisor.delete_expired_tasks;
Note. In a PDB the auto purging is not working, and the above mentioned command is required to be run manually regularly
The parameter can be adjusted also:
begin
DBMS_ADVISOR.SET_TASK_PARAMETER(
task_name=> 'AUTO_STATS_ADVISOR_TASK',
parameter=> 'EXECUTION_DAYS_TO_EXPIRE',
value => 10);
prvt prvt_advisor.delete_expired_tasks;
end;
/
In case of very large segments, huge amount of UNDO will be required, in this case some scripted iterative approach will be required to purge the data incrementally by days
After the purging, re-org the segments to release space:
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;
How to Purge WRI$_ADV_OBJECTS the Manual Way
In case the recommended way described above is not possible, purge the old records manually to release the space
1) Check the number of rows in WRI$_ADV_OBJECTS not pertaining to AUTO_STATS_ADVISOR_TASK:
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS
WHERE TASK_ID != (
SELECT ID FROM WRI$_ADV_TASKS
WHERE NAME='AUTO_STATS_ADVISOR_TASK');
2) Create a backup table to temporary keep the records NOT pertaining to the AUTOthe AUTO_STATS_ADVISOR_TASK:
SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW
AS SELECT * FROM WRI$_ADV_OBJECTS
WHERE TASK_ID != (
SELECT ID FROM WRI$_ADV_TASKS
WHERE NAME='AUTO_STATS_ADVISOR_TASK');
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;
3) Truncate the table:
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;
4) Restore the records of the table:
SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS
SELECT * FROM WRI$_ADV_OBJECTS_NEW;
SQL> COMMIT;
Note. 19c version introduced the virtual column SQL_ID_VC, so the star (*) will not work, and will require to list all the columns in the statement excluding the SQL_ID_VC column, otherwise ORA-54013 will be raised
How to disable the Optimizer Statistics Advisor
1) Possibility to control the autotask of the Optimizer Statistics Advisor was introduced only in 21c release. For versions below 21c release, a backport patch can be requested through Oracle Support
With this fix the autotask can be checked with:
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
-----------------------------------------------
TRUE
To disable/enable:
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
-----------------------------------------------
FALSE
2) Without the fix on the versions below 21c, the autotask can be dropped:
SQ> EXEC DBMS_STATS.DROP_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK');
But in this case the ORAthe ORA-20001: Statistics Advisor: Invalid Task Name For the current user error user error can be observed later when task is referenced
To recreate/enable the autotask:
SQL> EXEC DBMS_STATS.INIT_PACKAGE;
3) The safest way is to filter out all the rules for the autotask, it will keep the task enabled, but will restrain space usage, since all the rules for all operations will be disabled:
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
'AUTO_STATS_ADVISOR_TASK', NULL, NULL,'DISABLE');
END;
/
Rules can be viewed with V$STATSwith V$STATS_ADVISOR_RULES
Reference
- Optimizer Statistics Advisor In 12.2 (Quick Overview) (Doc ID 2259398.1)
- How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)
- 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)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle SYSAUX huge space Tips
2022-01-19
...
Oracle SYSAUX huge space Tips
If SYS.WRI$_ADV_OBJECTS is one of the largest objects, cancel or delete old segment adviser tasks.
The task ‘Get shrink advice based on object growth trend’ in particular can use a lot of space.
If there is space pressure, cancel and delete these tasks in Grid/Database Control via Performance> Advisors Home or manually using the DBMS_ADVISOR package.
Solution
The following sql is useful for fix the space problem.
-- Expect thousands of rows
select count(*) from DBA_ADVISOR_TASKS;
-- List results in html
set echo on
set markup html on spool on ENTMAP OFF
spool tasks.html
select * from DBA_ADVISOR_TASKS order by CREATED asc;
spool off
set markup html off spool off ENTMAP on
-- To delete a small number of old tasks, you can use syntax like:
DECLARE
task_name VARCHAR2(30);
BEGIN
task_name := '<task name> ';
DBMS_ADVISOR.DELETE_TASK(task_name);
END;
/
DECLARE
task_name VARCHAR2(30);
BEGIN
task_name := '<task name> ';
DBMS_ADVISOR.CANCEL_TASK('My Task');
END;
-- Delete ADDM tasks(Expect thousands of ADDM task rows)
-- create delete sql file
set head off
spool dt.sql
select 'exec DBMS_ADVISOR.DELETE_TASK(~'||task_name||'~);' from DBA_ADVISOR_TASKS where upper(task_name) like 'ADDM%';
spool off
-- then vi dt.sql in a text editor and replace the ~ characters with ' characters.
-- example
------
-- before:
-- exec DBMS_ADVISOR.DELETE_TASK(~ADDM:2591242759_1_26297~);
------
-- after:
------
-- exec DBMS_ADVISOR.DELETE_TASK('ADDM:2591242759_1_26297');
select count(*) from DBA_ADVISOR_TASKS;
-- run dt.sql to delete the tasks.
select count(*) from DBA_ADVISOR_TASKS;
Others sql tips
select task_name,status,to_char(created,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_tasks where task_name like '%ADVISOR%';
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
EXEC DBMS_STATS.INIT_PACKAGE();
select task_name,status,to_char(created,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_tasks where task_name like '%ADVISOR%';
Sample:
SYS@cdb1> set linesize 300
SYS@cdb1> col TASK_NAME for a50
SYS@cdb1> select task_name,status,to_char(created,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_tasks where task_name like '%ADVISOR%';
TASK_NAME STATUS TO_CHAR(CREATED,'YY
-------------------------------------------------- ----------- -------------------
AUTO_STATS_ADVISOR_TASK COMPLETED 2019/04/17 01:14:34
INDIVIDUAL_STATS_ADVISOR_TASK INITIAL 2019/04/17 01:14:34
DECLARE
v_tname VARCHAR2(32767);
3 BEGIN
4 v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
6 END;
7 /
PL/SQL procedure successfully completed.
SYS@cdb1> select task_name,status,to_char(created,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_tasks where task_name like '%ADVISOR%';
TASK_NAME STATUS TO_CHAR(CREATED,'YY
-------------------------------------------------- ----------- -------------------
INDIVIDUAL_STATS_ADVISOR_TASK INITIAL 2019/04/17 01:14:34
SYS@cdb1> EXEC DBMS_STATS.INIT_PACKAGE();
PL/SQL procedure successfully completed.
SYS@cdb1> select task_name,status,to_char(created,'yyyy/mm/dd hh24:mi:ss') from dba_advisor_tasks where task_name like '%ADVISOR%';
TASK_NAME STATUS TO_CHAR(CREATED,'YY
-------------------------------------------------- ----------- -------------------
INDIVIDUAL_STATS_ADVISOR_TASK INITIAL 2019/04/17 01:14:34
AUTO_STATS_ADVISOR_TASK INITIAL 2022/01/19 11:46:10
SYS@cdb1>
COL SEGMENT_NAME FORMAT A30
COL OWNER FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL SEGMENT_TYPE FORMAT A15
SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;
EXECUTION_DAYS_TO_EXPIRE Tips
Since AUTO_STATS_ADVISOR_TASK records are not purged from WRI$_ADV_OBJECTS, the SYSAUX space usage is growing rapidly.
The EXECUTION_DAYS_TO_EXPIRE parameter is set to 30 (by default) for AUTO_STATS_ADVISOR_TASK.
In Multitenant environment (CDB/PDB), even after applying the aforesaid respective RU patch, the expired AUTO_STATS_ADVISOR_TASK data is NOT purged automatically from PDB despite the default setting of EXECUTION_DAYS_TO_EXPIRE to 30 or to any custom value. In CDB, the expired tasks are purged through Auto-Purge window. In such cases, the expired tasks can be deleted manually using the above command in PDB to clear the SYSAUX space due to AUTO_STATS_ADVISOR_TASK pertaining to the particular PDB. There is an enhancement request raised to implement the Auto-Purge mechanism of expired advisor tasks from PDB as well.
If the default 30 days retention period is not required with the interest of SYSAUX space consumption then EXECUTION_DAYS_TO_EXPIRE parameter value can be adjusted. Following commands can be used to modify the parameter to custom value as per the requirement to purge the statistics advisor data with lesser retention period. In this example, it is set to 10.
SYS@cdb1> col parameter_name format a35
SYS@cdb1> col parameter_value format a20
SYS@cdb1> set lines 120
SYS@cdb1> 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
SYS@cdb1> exec prvt_advisor.delete_expired_tasks;
PL/SQL procedure successfully completed.
SYS@cdb1> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);
PL/SQL procedure successfully completed.
SYS@cdb1> 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 10
SYS@cdb1>
Reference
How To Recreate the SYSAUX Tablespace (Doc ID 468116.1)
How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)
How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)
12.2.0.1への移行後Statistics Advisor の機能により SYSAUX表領域が大きくなる (Doc ID 2355243.1) 2. WRI$_ADV_OBJECTS 表の AUTO_STATS_ADVISOR_TASK に関するレコードが非常に多くあり、データ削除に REDO/UNDO が多く使用されそうな場合には、1 のかわりに以下の方法でデータのパージを行うことで、REDO/UNDOの消費を避けることができます。
SYSAUX 表領域が肥大化した場合の対応方法(KROWN:125796) (Doc ID 1740178.1)
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
Bug 27983174 - lnx-183-mdb:got ORA-12012, ORA-20001 and ORA-6512 err from ora$at_os_opt_sy_xxx (Doc ID 27983174.8)
自動 UNDO 管理を使用している UNDO 表領域の変更、再作成方法(KROWN:136322) (Doc ID 1746713.1)
Have a good work&life! 2022/01 via LinHong