ORA-20001: Statistics Advisor: Invalid task name for the current user

On a recent upgraded 19c database from 12.1, we got the following errors:

2022-02-10T17:01:20.301348-05:00
Errors in file /opt/oracle/diag/rdbms/PROD/trace/PROD_j002_3750136.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_10969"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 51871
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 891
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22158
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24290
ORA-06512: at "SYS.DBMS_STATS", line 51859

There are lots of articles talking about this error on the internet. Basically “Invalid task name” means one of following tasks or both are missing.

  • AUTO_STATS_ADVISOR_TASK
  • INDIVIDUAL_STATS_ADVISOR_TASK

To fix it, need to recreate them. For example, for my sysetem:

SQL> col name format a30
SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                           CTIME     HOW_CREATED   OWNER_NAME
------------------------------ --------- ------------  -----------
INDIVIDUAL_STATS_ADVISOR_TASK  06-FEB-22 CMD           SYS

I only saw 1 task listed. So I just dropped it and recreated 2 tasks:

DECLARE
v_tname VARCHAR2(32767);
BEGIN
	v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
	DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

Now query again and I see two tasks listed

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                           CTIME     HOW_CREATED   OWNER_NAME
------------------------------ --------- ------------- ----------
AUTO_STATS_ADVISOR_TASK        11-FEB-22 CMD           SYS
INDIVIDUAL_STATS_ADVISOR_TASK  11-FEB-22 CMD           SYS

Optimizer Statistics Advisor is a new feature in 12.2 that runs everyday during the maintenance window. It is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks.

It runs a predefined task named AUTO_STATS_ADVISOR_TASK once per day in the maintenance window automatically. For the automated job to run, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. The task runs as part of the automatic optimizer statistics collection client. The automated task generates findings and recommendations, but does not implement actions automatically. As for any other task, you can configure the automated task, and generate reports. If the report recommends actions, then you can implement the actions manually.

To run the task manually, create your own task using the DBMS_STATS.CREATE_ADVISOR_TASK function, and then run it at any time using the EXECUTE_ADVISOR_TASK procedure. Unlike the automated task, the manual task can implement actions automatically. Alternatively, you can configure the task to generate a PL/SQL script, which you can then run manually.

I can not find anything from Oracle talking about INDIVIDUAL_STATS_ADVISOR_TASK other than it’s referenced with AUTO_STATS_ADVISOR_TASK. My best guess from the task name is that it is related to individual statistics.

Reference:

  1. How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
  2. SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
  3. How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)
  4. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/optimizer-statistics-advisor.html#GUID-B934DB62-F4AE-420C-B7C7-EE7FC3747C09
  5. https://oracle-base.com/articles/12c/optimizer-statistics-advisor-12cr2

One thought on “ORA-20001: Statistics Advisor: Invalid task name for the current user

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s