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 SYSOne 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:
- 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?)
- Optimizer Statistics Advisor — Analyzes how statistics are being gathered and suggests changes that can be made to fine tune statistics collection.
- 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 ENABLEDJOB_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:
- 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)
- SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
- Optimizer Statistics Advisor In 12.2 (Quick Overview) (Doc ID 2259398.1)
- How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
- How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)
JoeLi's TechLife 