racle19c manually cleans up large objects in PDB Sysaux, such as WRI $ _adv_Objects (ORA-65040)
tags: oracle19c
Loaded: 0.17%
Encounter similar problems, turn down the reference:
Recently, a customer 19C RAC CDB database grows large, analyzing the number of WRI $ _adv_objects object records caused by Optimizer Statistics Advisor characteristics, the following is the cleaning method.
1, find the biggest object
SQL> set lines 120
SQL> col occupant_name format a30
SQL> select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
or
prompt
prompt List of TOP 10 largest objects in SYSTEM AND SYSAUX TABLESPACE:
prompt
select * from (
select tablespace_name,topseg_seg_owner,topseg_segment_name,segment_type,mb,partitions, row_number() over(partition by tablespace_name order by mb desc) rn from (
select
tablespace_name,
owner topseg_seg_owner,
segment_name topseg_segment_name,
--partition_name,
segment_type,
round(SUM(bytes/1048576)) MB,
case when count(*) >= 1 then count(*) else null end partitions
from dba_segments
where upper(tablespace_name) in ('SYSTEM','SYSAUX') -- tablespace name
group by
tablespace_name,
owner,
segment_name,
segment_type ))
where rn<=10;
This customer discovers that SM / Advisor and the largest object are WRI $ _adv_objects, because a new feature is introduced in 12.2: the optimizer statistics consultant. Optimizer Statistical Consultants run in the Maintenance window every day, multiple times_stats_advisor_task, and take up a large number of SYSAUX tablespaces.
2, statistical record number
SQL> col task_name format a35
SQL> select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;
3, manual cleaning, such as WRI $ _adv_Objects
- Delete Statistics Advisor Tasks
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
Note:
1. If you encounter an error: ORA-20001: Statistics Advisor: Invalid Task Name for the Current User
implement
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
2, if Wri $ _adv_objects record too much, Delete will take up larger UNDO, you can store the desired data in the temporary table, Truncate Table WRI $ _adv_Objects, then Insert back.
- Reorganize tables and all indexes after deleting tasks
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
3. If there is no problem in the CDB, there is no problem in the above operation, but in the PDB, the Move table may have the following ORA-65040 error.
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER TABLE WRI$_ADV_OBJECTS MOVE
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> ho oerr ora 65040
65040, 00000, "operation not allowed from within a pluggable database"
// *Cause: An operation was attempted that can only be performed in the root
// or application root container.
// *Action: Switch to the root or application root container to perform the
// operation.
//
There are two ways to solve:
1, _oracle_scripts parameters
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
Table altered.
2,dbms_pdb.exec_as_oracle_script
SQL> exec dbms_pdb.exec_as_oracle_script('alter table sys.WRI$_ADV_OBJECTS move');
PL/SQL procedure successfully completed.
4. In order to reduce Advisor storage, retention period can be reduced
- Confirm that the current settings
select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
where task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME like '%EXPIRE%';
- Change the settings for the settings, such as the save time of historical data is 15 days:
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 15
);
END;
/
Note:
But you need to pay attention to bug 26764561 auto_stats_advisor_task not purging even wegh setting execution_stays_to_expire (DOC ID 2615851.1),
This configuration is independent of CDB and different PDBs.
5, disable auto_stats_advisor_task
If this Advisor is really useless, you can consider disabling, but 12C-20C defaults to install a bug 26749785 Patch to add auto_stats_advisor_task control, pay attention to this is not bug, just enhanced. The following features cannot be used before installing the patch (or 21.1).
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
Use
declare
filter1 clob;
begin
filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK',
'EXECUTE',
NULL,
'DISABLE');
END;
/
References SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)