Auto optimizer stats collection job is not running

Problem Statement:

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

Auto optimizer stats collection job is not running with below errors in alert log especially on 12.2 and higher environments

 

RA$AT_OS_OPT_SY_7247 auto optimizer stats collection FAILED 26-JUN-18 12.00.52.744989000 AM EUROPE/VIENNA +00 00:08:29.000000 ORA$AT_OS_OPT_SY_7247 "ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at "SYS.DBMS_STATS", line 47207

ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201

ORA-06512: at "SYS.DBMS_STATS", line 47197

"

ORA$AT_OS_OPT_SY_7257 auto optimizer stats collection FAILED 26-JUN-18 12.10.54.045062000 AM EUROPE/VIENNA +00 00:03:20.000000 ORA$AT_OS_OPT_SY_7257 "ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at "SYS.DBMS_STATS", line 47207

ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201

ORA-06512: at "SYS.DBMS_STATS", line 47197

"

ORA$AT_OS_OPT_SY_7258 auto optimizer stats collection FAILED 26-JUN-18 12.20.56.375243000 AM EUROPE/VIENNA +00 00:04:22.000000 ORA$AT_OS_OPT_SY_7258 "ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at "SYS.DBMS_STATS", line 47207

ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201

ORA-06512: at "SYS.DBMS_STATS", line 47197

 

Root Cause :

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

 

Seed database was most likely not created right by package dbms_stats.init_package not being ran.

Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute.

 

SOLUTION :

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

 

Run dbms_stats.init_package()  in the container database to create the tasks correctly:

 

$ sqlplus / as sysdba

 

EXEC dbms_stats.init_package();

 

column name format A35

set linesize 120

 

select name, ctime, how_created

  from sys.wri$_adv_tasks

where owner_name = 'SYS'

   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

 

 

Output of the query will look like this:

 

NAME                                 CTIME      HOW_CREATED

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

AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD

INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 CMD

 

If the query based on "where owner_name = 'SYS'" condition does not show any rows but the error continues, please change the query as shown below to

 

see if a non-SYS user like SYSTEM owns those tasks for some reason:

 

$ sqlplus / as sysdba

 

select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks

where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

 

If the OWNER is a non-SYS user, you have to drop the tasks as that user first and then try to solution mention in the Note again.

 

This was a case for one customer.

 

For example:

 

--- Connect as SYSTEM, for example, if that user owned the tasks and non SYS for some reason

 

SQL> conn system/&password

 

DECLARE

v_tname VARCHAR2(32767);

BEGIN

v_tname := 'AUTO_STATS_ADVISOR_TASK';

DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

 

SQL> DECLARE

v_tname VARCHAR2(32767);

BEGIN

v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';

DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

 

connect / as sysdba

EXEC DBMS_STATS.INIT_PACKAGE();

 

 

Refer 2127675.1.