Oracle DBA by Example. Code samples, Scripts, Reference.: March 2021

============

Issue

============

Tablespace SYSAUX nearly 100% full because table WRI$_ADV_OBJECTS and related indexes are very big

This is a know issue in Oracle 12.2

============

Solution

============

Purge Data from WRI$_ADV_OBJECTS.

Current space usage:

TABLESPACE_NAME FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB
--------------- ------------- ----------------- -------------
SYSAUX 46 7954 8000

SQL> select * from v$version;
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production

Top objects in SYSAUX Tablespace

0
OWNER TABLESPACE_NAME SEGMENT_NAME USED_MB
------- --------------- --------------------------- -------
SYS SYSAUX WRI$_ADV_OBJECTS 4145
SYS SYSAUX WRI$_ADV_OBJECTS_IDX_01 1666
SYS SYSAUX WRI$_ADV_OBJECTS_PK 1104
SYS SYSAUX SYS_LOB0000164068C00005$$ 160

COL TASK_NAME FOR A50

SELECT task_name, count(*) cnt

FROM dba_advisor_objects

GROUP BY task_name

ORDER BY cnt DESC;

TASK_NAME CNT
------------------------- ----------
AUTO_STATS_ADVISOR_TASK 11061381
ADDM:1066102538_1_28416 13
ADDM:1066102538_1_28062 13
ADDM:1066102538_1_27837 12
ADDM:1066102538_1_28086 11
ADDM:1066102538_1_28302 11
ADDM:1066102538_1_27885 11
ADDM:1066102538_1_28134 10

==========================================

Reference for cleanup from WRI$_ADV_OBJECTS:

==========================================

Reference:
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)

If there are too many records in the table WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK, then huge UNDO would be required.
In such cases, following method can be implemented to purge the data by avoiding excessive redo/undo generation.

CREATE TABLE WRI$_ADV_OBJECTS_NEW
AS SELECT * FROM WRI$_ADV_OBJECTS
WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SELECT COUNT(* )FROM WRI$_ADV_OBJECTS_NEW;

COUNT(*)

--------

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

Note - In case of error ORA-54013: INSERT operation disallowed on virtual columns - see below #Handle ORA-54013

commit;

DROP TABLE WRI$_ADV_OBJECTS_NEW;

--Reorganize the indexes
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

SYSAUX Tablespace usage after cleanup:

TABLESPACE_NAME FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB
--------------- ------------- ----------------- -------------
SYSAUX 6960 1040 8000

============

Prevent issue from happen again

============

Reference:

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

COL task_name FORMAT a25

COL parameter_name FORMAT a35

COL parameter_value FORMAT a20

SET lines 120

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

BEGIN

DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 30);

END;

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

Now, all the statistics advisor execution records older than 30 days are marked as expired and would be purged automatically through the Auto-Purge window.

To purge manually using the below command instead of Auto-Purge window:-

conn / as sysdba

BEGIN

PRVT_ADVISOR.delete_expired_tasks;

END;

Check status after purge

COL EXECUTION_NAME FOR A30

SELECT task_id,task_name,execution_name ,execution_start

FROM DBA_ADVISOR_EXECUTIONS

WHERE task_name='AUTO_STATS_ADVISOR_TASK'

ORDER BY execution_start;

TASK_ID TASK_NAME EXECUTION_NAME EXECUTION_START

---------- ------------------------- --------------- ---------------

8491 AUTO_STATS_ADVISOR_TASK EXEC_15898 01-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15899 01-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15912 02-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15913 02-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15930 03-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15931 03-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15948 04-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15960 05-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_15983 06-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16010 07-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16027 08-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16059 09-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16084 10-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16107 11-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16140 12-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16168 13-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16200 14-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16222 15-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16248 16-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16270 17-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16295 18-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16318 19-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16349 20-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16375 21-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16398 22-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16421 23-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16443 24-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16459 25-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16488 26-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16501 27-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16529 28-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16543 29-MAR-21

8491 AUTO_STATS_ADVISOR_TASK EXEC_16558 30-MAR-21

========================

Appendix

========================

1. crontab script to automate purge

crontab

2 7 * * * bash -l /software/oracle/oracle/scripts/space/space_cleanup/delete_expired_tasks.sh

delete_expired_tasks.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/space/space_cleanup

. /etc/sh/orash/oracle_login.sh igt

ORACLE_HOME=/software/oracle/1910

ORACLE_SID=igt

PATH=${PATH}:${ORACLE_HOME}/bin

cd ${WORK_DIR}

sqlplus / as sysdba @delete_expired_tasks.sql

delete_expired_tasks.sql

BEGIN

PRVT_ADVISOR.delete_expired_tasks;

END;

EXIT;

2. Handle ORA-54013: INSERT operation disallowed on virtual columns

SQL> INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

SELECT column_name, virtual_column

  FROM user_tab_cols

 WHERE table_name = 'WRI$_ADV_OBJECTS'

COLUMN_NAME                    VIRTUAL_COLU

------------------------------ ------------

ID                             NO

TYPE                           NO

TASK_ID                        NO

EXEC_NAME                      NO

ATTR1                          NO

ATTR2                          NO

ATTR3                          NO

ATTR4                          NO

ATTR5                          NO

ATTR6                          NO

ATTR7                          NO

ATTR8                          NO

ATTR9                          NO

ATTR10                         NO

ATTR11                         NO

ATTR12                         NO

ATTR13                         NO

ATTR14                         NO

ATTR15                         NO

ATTR16                         NO

ATTR17                         NO

ATTR18                         NO

ATTR19                         NO

ATTR20                         NO

OTHER                          NO

SPARE_N1                       NO

SPARE_N2                       NO

SPARE_N3                       NO

SPARE_N4                       NO

SPARE_C1                       NO

SPARE_C2                       NO

SPARE_C3                       NO

SPARE_C4                       NO

SQL_ID_VC                      YES

Need to specify field by field

COL column_name  FOR A40

SELECT  LOWER(column_name)||',' column_name 

  FROM USER_TAB_COLS 

 WHERE table_name = 'WRI$_ADV_OBJECTS_NEW' 

ORDER BY column_id;

INSERT INTO WRI$_ADV_OBJECTS

(

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

)

SELECT

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

 FROM WRI$_ADV_OBJECTS_NEW;

commit;

=======================

Create a job to cleanup

=======================

delete_expired_tasks.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/space

. /etc/sh/orash/oracle_login.sh igt

ORA_VER=1910

ORACLE_SID=igt

ORACLE_HOME=/software/oracle/1910

cd ${WORK_DIR}

sqlplus / as sysdba @delete_expired_tasks.sql

delete_expired_tasks.sql

-----------------------------
--WRI$_ADV_OBJECTS
-----------------------------
CREATE TABLE WRI$_ADV_OBJECTS_NEW 
AS SELECT * FROM WRI$_ADV_OBJECTS 
WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT INTO WRI$_ADV_OBJECTS
(
id,
type,
task_id,
exec_name,
attr1,
attr2,
attr3,
attr4,
attr5,
attr6,
attr7,
attr8,
attr9,
attr10,
attr11,
attr12,
attr13,
attr14,
attr15,
attr16,
attr17,
attr18,
attr19,
attr20,
other,
spare_n1,
spare_n2,
spare_n3,
spare_n4,
spare_c1,
spare_c2,
spare_c3,
spare_c4
)
SELECT
id,
type,
task_id,
exec_name,
attr1,
attr2,
attr3,
attr4,
attr5,
attr6,
attr7,
attr8,
attr9,
attr10,
attr11,
attr12,
attr13,
attr14,
attr15,
attr16,
attr17,
attr18,
attr19,
attr20,
other,
spare_n1,
spare_n2,
spare_n3,
spare_n4,
spare_c1,
spare_c2,
spare_c3,
spare_c4
 FROM WRI$_ADV_OBJECTS_NEW;

commit;

DROP TABLE WRI$_ADV_OBJECTS_NEW;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;


-----------------------------
--WRI$_ADV_OBJECTS
-----------------------------

BEGIN
 PRVT_ADVISOR.delete_expired_tasks;
END;
/
EXIT;

Optionally, stop package AUTO_STATS_ADVISOR_TASK from running.

DECLARE

 v_tname VARCHAR2(32767);

BEGIN

 v_tname := 'AUTO_STATS_ADVISOR_TASK';

 DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) can be recreated any time by running the following if DBA(s) wants to use the Statistics Advisor.

BEGIN

 DBMS_STATS.INIT_PACKAGE();

END;

/

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Wednesday, March 31, 2021

Clean SYSAUX Tablespace, purge data from WRI$_ADV_OBJECTS