Oracle19c manually cleans up large objects in PDB Sysaux, such as WRI $ _adv_Objects (ORA-65040)

racle19c manually cleans up large objects in PDB Sysaux, such as WRI $ _adv_Objects (ORA-65040)

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)

Дерево страниц