AUTO_STATS_ADVISOR_TASK missing after the upgrade causing ORA-20001

In another post I’ve talked about this error ORA-20001. And I saw this error again on another database on 19c:

From the alert log:

2022-04-06T22:01:18.392714-05:00
Errors in file /opt/oracle/diag/rdbms/prod/PROD/trace/PROD_j001_364587.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_16257"
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

And from the trace file, same error statck:

[oracle@joetestdb trace]$ cat /opt/oracle/diag/rdbms/prod/PROD/trace/PROD_j001_364587.trc
Trace file /opt/oracle/diag/rdbms/prod/PROD/trace/PROD_j001_364587.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
Build label:    RDBMS_19.14.0.0.0DBRU_LINUX.X64_211224.3
ORACLE_HOME:    /opt/oracle/190000
System name:    Linux
Node name:      joetestdb
Release:        5.4.17-2102.201.3.el8uek.x86_64
Version:        #2 SMP Fri Apr 23 09:05:57 PDT 2021
Machine:        x86_64
Instance name: PROD
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 364587, image: oracle@joetestdb (J001)


*** 2022-04-06T22:01:18.358373-05:00
*** SESSION ID:(268.41644) 2022-04-06T22:01:18.358436-05:00
*** CLIENT ID:() 2022-04-06T22:01:18.358444-05:00
*** SERVICE NAME:(SYS$USERS) 2022-04-06T22:01:18.358450-05:00
*** MODULE NAME:(DBMS_SCHEDULER) 2022-04-06T22:01:18.358456-05:00
*** ACTION NAME:(ORA$AT_OS_OPT_SY_16257) 2022-04-06T22:01:18.358461-05:00
*** CLIENT DRIVER:() 2022-04-06T22:01:18.358467-05:00

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_16257"
<error barrier> at 0x7ffdaf306660 placed jslv.c@1805
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

The fix is just as described in the previous post — run dbms_stats.init_package to create the task AUTO_STATS_ADVISOR_TASK:

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
--------------------------------------  --------- ---------------
INDIVIDUAL_STATS_ADVISOR_TASK           30-NOV-20 CMD

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   30-NOV-20 CMD             SYS

SQL> EXEC dbms_stats.init_package();

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'); 

NAME                             CTIME     HOW_CREATED      OWNER_NAME
-------------------------------- --------- ---------------- --------------
AUTO_STATS_ADVISOR_TASK          07-APR-22 CMD              SYS
INDIVIDUAL_STATS_ADVISOR_TASK    30-NOV-20 CMD              SYS

One thing I would like to add in this post — this new feature Optimizer Statistics Advisor from 12.2 is implemented within the Automated Database Maintenance Tasks Infrastructure which introduced since 11g.

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. There are 3 predefined tasks:

  1. Automatic Optimizer Statistics Collection — Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution. (remember those GATHER_STATS_JOB in 10g days?)
  2. Optimizer Statistics Advisor — Analyzes how statistics are being gathered and suggests changes that can be made to fine tune statistics collection.
  3. Automatic Segment Advisor — Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments.

And this Optimizer Statistics Advisor is executed within the Automatic Optimizer Statistics Collection task.

Below are the Dictionary views related to this infrastructure:

  • DBA_AUTO_SEGADV_SUMMARY
  • DBA_AUTO_STAT_EXECUTIONS
  • DBA_AUTOTASK_CLIENT_HISTORY
  • DBA_AUTOTASK_CLIENT_JOB
  • DBA_AUTOTASK_CLIENT
  • DBA_AUTOTASK_JOB_HISTORY
  • DBA_AUTOTASK_OPERATION
  • DBA_AUTOTASK_SCHEDULE
  • DBA_AUTOTASK_STATUS
  • DBA_AUTOTASK_TASK
  • DBA_AUTOTASK_WINDOW_CLIENTS
  • DBA_AUTOTASK_WINDOW_HISTORY
  • DBA_SCHEDULER_JOB_RUN_DETAILS

When I queried them, I did see the job failed in this case:

SQL> select job_name,  errors from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='ORA$AT_OS_OPT_SY_16257';

JOB_NAME                    ERRORS
--------------------------- --------------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_16257      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

This job is from Automated Maintenance Tasks — “auto optimizer stats collection”, you won’t see it in the view DBA_SCHEDULER_JOBS.

SQL> select client_name, operation_name, task_name, status, task_target_name, RETRY_COUNT, LAST_GOOD_DATE from DBA_AUTOTASK_TASK ;

CLIENT_NAME                      OPERATION_NAME                 TASK_NAME                  STATUS   TASK_TARGET_NAME           RETRY_COUNT LAST_GOOD_DATE
-------------------------------- ------------------------------ -------------------------- -------- -------------------------- ----------- ------------------------------------
sql tuning advisor               automatic sql tuning task      AUTO_SQL_TUNING_PROG       ENABLED  automatic sql workload               0 06-APR-22 10.00.02.335138 PM -05:00
auto optimizer stats collection  auto optimizer stats job       gather_stats_prog          ENABLED  system                              61 08-MAR-22 10.10.24.130063 PM -06:00
auto space advisor               auto space advisor job         auto_space_advisor_prog    ENABLED  system                               0 06-APR-22 10.04.58.531073 PM -05:00

As you can see from the resultes above, “auto optimizer stats collection” has 61 retry counts due to the failure.

Maintenance Windows:

SQL>  select * from DBA_AUTOTASK_SCHEDULE;

WINDOW_NAME                                        START_TIME                           DURATION
-------------------------------------------------- ------------------------------------ ------------------------------
MONDAY_WINDOW                                      11-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
MONDAY_WINDOW                                      18-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
MONDAY_WINDOW                                      25-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
MONDAY_WINDOW                                      02-MAY-22 10.00.00.653937 PM -05:00  +000 04:00:00
TUESDAY_WINDOW                                     12-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
TUESDAY_WINDOW                                     19-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
TUESDAY_WINDOW                                     26-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
TUESDAY_WINDOW                                     03-MAY-22 10.00.00.653937 PM -05:00  +000 04:00:00
WEDNESDAY_WINDOW                                   13-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
WEDNESDAY_WINDOW                                   20-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
WEDNESDAY_WINDOW                                   27-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
WEDNESDAY_WINDOW                                   04-MAY-22 10.00.00.653937 PM -05:00  +000 04:00:00
THURSDAY_WINDOW                                    07-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
THURSDAY_WINDOW                                    14-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
THURSDAY_WINDOW                                    21-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
THURSDAY_WINDOW                                    28-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
THURSDAY_WINDOW                                    05-MAY-22 10.00.00.653937 PM -05:00  +000 04:00:00
FRIDAY_WINDOW                                      08-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
FRIDAY_WINDOW                                      15-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
FRIDAY_WINDOW                                      22-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
FRIDAY_WINDOW                                      29-APR-22 10.00.00.653937 PM -05:00  +000 04:00:00
FRIDAY_WINDOW                                      06-MAY-22 10.00.00.653937 PM -05:00  +000 04:00:00
SATURDAY_WINDOW                                    09-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SATURDAY_WINDOW                                    16-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SATURDAY_WINDOW                                    23-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SATURDAY_WINDOW                                    30-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SATURDAY_WINDOW                                    07-MAY-22 06.00.00.653937 AM -05:00  +000 20:00:00
SUNDAY_WINDOW                                      10-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SUNDAY_WINDOW                                      17-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SUNDAY_WINDOW                                      24-APR-22 06.00.00.653937 AM -05:00  +000 20:00:00
SUNDAY_WINDOW                                      01-MAY-22 06.00.00.653937 AM -05:00  +000 20:00:00
SUNDAY_WINDOW                                      08-MAY-22 06.00.00.653937 AM -05:00  +000 20:00:00

32 rows selected.
SQL> select client_name, operation_name, status from DBA_AUTOTASK_OPERATION ;

CLIENT_NAME                      OPERATION_NAME                 STATUS
-------------------------------- ------------------------------ --------
auto optimizer stats collection  auto optimizer stats job       ENABLED
auto space advisor               auto space advisor job         ENABLED
sql tuning advisor               automatic sql tuning task      ENABLED

SQL> select client_name, status from DBA_AUTOTASK_CLIENT ;

CLIENT_NAME                      STATUS
-------------------------------- ---------------
sql tuning advisor               ENABLED
auto optimizer stats collection  ENABLED
auto space advisor               ENABLED

JOB_STATUS showed failed jobs and error information:

select client_name, window_name, job_name, job_status from DBA_AUTOTASK_JOB_HISTORY where job_status='FAILED' order by JOB_START_TIME;

CLIENT_NAME                      WINDOW_NAME                                        JOB_NAME                                 JOB_STATUS
-------------------------------- -------------------------------------------------- ---------------------------------------- ------------------------------
auto optimizer stats collection  SUNDAY_WINDOW                                      ORA$AT_OS_OPT_SY_16249                   FAILED
auto optimizer stats collection  MONDAY_WINDOW                                      ORA$AT_OS_OPT_SY_16251                   FAILED
auto optimizer stats collection  TUESDAY_WINDOW                                     ORA$AT_OS_OPT_SY_16254                   FAILED
auto optimizer stats collection  WEDNESDAY_WINDOW                                   ORA$AT_OS_OPT_SY_16257                   FAILED

SQL>  select job_error, job_duration, job_info from DBA_AUTOTASK_JOB_HISTORY where job_status='FAILED' and job_name='ORA$AT_OS_OPT_SY_16257';

 JOB_ERROR JOB_DURATION         JOB_INFO
---------- -------------------- ----------------------------------------------------------------------------------------------------
     20001 +000 00:01:16        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

FAILED showed number of objects for which statistics collection failed during the execution of the task:

SQL>  select origin, status, start_time, completed, failed from DBA_AUTO_STAT_EXECUTIONS order by start_time desc;

ORIGIN              STATUS          START_TIME                            COMPLETED     FAILED
------------------- --------------- ------------------------------------ ---------- ----------
AUTO_TASK           COMPLETED       06-APR-22 10.00.02.377457 PM -05:00         662          1
AUTO_TASK           COMPLETED       05-APR-22 10.00.01.650386 PM -05:00         628          1
AUTO_TASK           COMPLETED       04-APR-22 10.00.02.549487 PM -05:00         803          1
AUTO_TASK           COMPLETED       03-APR-22 10.07.20.168571 PM -05:00         194          1
AUTO_TASK           COMPLETED       03-APR-22 06.06.25.114365 PM -05:00         156          1
AUTO_TASK           COMPLETED       03-APR-22 02.05.29.982987 PM -05:00         173          1
AUTO_TASK           COMPLETED       03-APR-22 10.04.36.862805 AM -05:00         205          1
AUTO_TASK           COMPLETED       03-APR-22 06.00.02.423894 AM -05:00         629          1
AUTO_TASK           COMPLETED       02-APR-22 10.11.54.836391 PM -05:00         242          1
AUTO_TASK           COMPLETED       02-APR-22 06.10.59.672370 PM -05:00         164          1
AUTO_TASK           COMPLETED       02-APR-22 02.10.05.081360 PM -05:00         167          1

SQL> select client_name, job_name, task_name, TASK_TARGET_NAME, task_operation from DBA_AUTOTASK_CLIENT_job;

no rows selected

JOBS_COMPLETED showed 0 with the failed the job.

SQL> select client_name, window_name, window_start_time , JOBS_CREATED, JOBS_STARTED, JOBS_COMPLETED from DBA_AUTOTASK_CLIENT_HISTORY  order by 3 desc;

CLIENT_NAME                      WINDOW_NAME                    WINDOW_START_TIME                        JOBS_CREATED JOBS_STARTED JOBS_COMPLETED
-------------------------------- ------------------------------ ---------------------------------------- ------------ ------------ --------------
auto optimizer stats collection  WEDNESDAY_WINDOW               06-APR-22 10.00.00.124730 PM -05:00                 1            1              0
auto space advisor               WEDNESDAY_WINDOW               06-APR-22 10.00.00.124730 PM -05:00                 1            1              1
sql tuning advisor               WEDNESDAY_WINDOW               06-APR-22 10.00.00.124730 PM -05:00                 1            1              1
auto optimizer stats collection  TUESDAY_WINDOW                 05-APR-22 10.00.00.192456 PM -05:00                 1            1              0
auto space advisor               TUESDAY_WINDOW                 05-APR-22 10.00.00.192456 PM -05:00                 1            1              1
sql tuning advisor               TUESDAY_WINDOW                 05-APR-22 10.00.00.192456 PM -05:00                 1            1              1
auto optimizer stats collection  MONDAY_WINDOW                  04-APR-22 10.00.00.287968 PM -05:00                 1            1              0
auto space advisor               MONDAY_WINDOW                  04-APR-22 10.00.00.287968 PM -05:00                 1            1              1
sql tuning advisor               MONDAY_WINDOW                  04-APR-22 10.00.00.287968 PM -05:00                 1            1              1
auto optimizer stats collection  SUNDAY_WINDOW                  03-APR-22 06.00.00.248164 AM -05:00                 5            5              0
auto space advisor               SUNDAY_WINDOW                  03-APR-22 06.00.00.248164 AM -05:00                 5            5              5
sql tuning advisor               SUNDAY_WINDOW                  03-APR-22 06.00.00.248164 AM -05:00                 1            1              1
auto optimizer stats collection  SATURDAY_WINDOW                02-APR-22 06.00.00.251981 AM -05:00                 5            5              0
auto space advisor               SATURDAY_WINDOW                02-APR-22 06.00.00.251981 AM -05:00                 5            5              5
sql tuning advisor               SATURDAY_WINDOW                02-APR-22 06.00.00.251981 AM -05:00                 1            1              1
auto optimizer stats collection  FRIDAY_WINDOW                  01-APR-22 10.00.00.117471 PM -05:00                 1            1              0
auto space advisor               FRIDAY_WINDOW                  01-APR-22 10.00.00.117471 PM -05:00                 1            1              1
sql tuning advisor               FRIDAY_WINDOW                  01-APR-22 10.00.00.117471 PM -05:00                 1            1              1
auto optimizer stats collection  THURSDAY_WINDOW                31-MAR-22 10.00.00.249704 PM -05:00                 1            1              0
auto space advisor               THURSDAY_WINDOW                31-MAR-22 10.00.00.249704 PM -05:00                 1            1              1
sql tuning advisor               THURSDAY_WINDOW                31-MAR-22 10.00.00.249704 PM -05:00                 1            1              1
auto optimizer stats collection  WEDNESDAY_WINDOW               30-MAR-22 10.00.00.131710 PM -05:00                 1            1              0
auto space advisor               WEDNESDAY_WINDOW               30-MAR-22 10.00.00.131710 PM -05:00                 1            1              1
sql tuning advisor               WEDNESDAY_WINDOW               30-MAR-22 10.00.00.131710 PM -05:00                 1            1              1

Optimizer Statistics Advisor can be run in both an automated and manual mode:

Automated: The predefined task AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window once per day. 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. REPORT_STATS_ADVISOR_TASK Procedure reports the results of an Optimizer Statistics Advisor task.

Manual: Users can create their 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, users can configure the task to generate a PL/SQL script via the DBMS_STATS.SCRIPT_ADVISOR_TASK function, which can be run manually.

Execute Statistics Advisor manually and implement actions automatically:

DECLARE
	v_tname VARCHAR2(128) := 'adv_task';
	v_ename VARCHAR2(128) := NULL;
	v_report CLOB := null;
	v_script CLOB := null;
	v_implementation_result CLOB;
BEGIN
	v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname); /* To create the advisor task */
	v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname); /* To execute the task */
	
	v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname); /* To view the task report */
	DBMS_OUTPUT.PUT_LINE(v_report);

	v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname); /* To implement the recommendations */
END;

Generate a script that contains recommendations for the task adv_task:

VARIABLE b_script CLOB
DECLARE
	v_tname VARCHAR2(32767);
BEGIN
	v_tname := 'adv_task';
	:b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
END;  

Print the report for AUTO_STATS_ADVISOR_TASK — This would print the report for the last execution of AUTO_STATS_ADVISOR_TASK that runs every day automatically during the maintenance window.

SET LINESIZE 3000
SET LONG 500000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000
SELECT DBMS_STATS.REPORT_ADVISOR_TASK('ADV_TASK',NULL,'TEXT','ALL','ALL') AS REPORT FROM DUAL;

check the status of AUTO_STATS_ADVISOR_TASK executions:

COL EXECUTION_NAME FORMAT a14
SELECT EXECUTION_NAME, EXECUTION_END, STATUS 
FROM DBA_ADVISOR_EXECUTIONS 
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK' ORDER BY 2;

References:

  1. ORA-12012 Error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_ in 12.2.0 Database version or higher release (like 18c) (Doc ID 2127675.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. Optimizer Statistics Advisor In 12.2 (Quick Overview) (Doc ID 2259398.1)
  4. How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
  5. How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)

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