12c执行统计信息收集报ORA-12012 ORA-20001 ORA-06512

收到一套生产库的告警信息：

2021-03-20T10:07:49.857362+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl13/trace/orcl13_j000_28097.trc:
ORA-12012: 自动执行作业 "SYS"."ORA$AT_OS_OPT_SY_10947" 出错
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: 在 "SYS.DBMS_STATS", line 47214
ORA-06512: 在 "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: 在 "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: 在 "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: 在 "SYS.DBMS_STATS", line 47204

数据库及补丁版本：

SQL> select * from v$version where rownum=1;

BANNER                                                                                         CON_ID
------------------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production                        0

[oracle@rac3 ~]$ opatch lspatches
30138470;Database Oct 2019 Release Update : 12.2.0.1.191015 (30138470)
30122814;OCW OCT 2019 RELEASE UPDATE 12.2.0.1.191015 (30122814)

版本有些旧了，还是一套3节点的rac，跑web应用。

Oracle Database 12.2 includes a new feature called the Optimizer Statistics Advisor. The goal of the advisor is to analyze how statistics are gathered, validate the quality of statistics already gathered and check the status of auto stats gathering (for example, checking for successful completion). To achieve this, it examines the data dictionary with respect to a set of rules. Where exceptions to the rules are found, findings may be generated and these, in turn, may lead to specific recommendations. The advisor will generate a report that lists findings (with the associated “broken” rule), and then list specific recommendations to remedy the situation. Finally, the recommendations can be implemented using a set of actions. Actions can be output in the form of a SQL script or they can be implemented automatically.

12.2中引入了统计信息顾问，用于提高统计信息收集的质量。

查看统计信息任务：

SQL> col owner_name for a15;
SQL> col name for a35;
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

创建任务：

$ sqlplus / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
SQL> col owner_name for a15;
SQL> col name for a35;
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        2021-03-20 10:47:26    CMD        SYS
INDIVIDUAL_STATS_ADVISOR_TASK    2021-03-20 10:47:26    CMD        SYS

开启该任务后可能会导致SYSAUX表空间增长过快，：

SQL> SET LINES 120
SQL> COL OCCUPANT_NAME FORMAT A30
SQL> SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC;
OCCUPANT_NAME                  SPACE_USAGE_KBYTES
------------------------------ ------------------
SM/ADVISOR                     5901376
SM/OPTSTAT                     574080

可以通过以下两种方法处理：

1.修改任务结果过期时间

    mos中描述在12.2.0.1中，EXECUTION_DAYS_TO_EXPIRE默认为UNLIMITED，该套系统查询的默认值为30天。

SQL> col TASK_NAME format a25
SQL> col parameter_name format a35
SQL> col parameter_value format a20
SQL> set lines 120
SQL> 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

修改为10天

SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);

可能不生效，参考AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)

2.禁用任务

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname); ---删除任务
END;
/

exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

相关文档：

AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)
Recurring ORA-12012, ORA-20001, ORA-06512 In Container Database (DocID 2420581.1)
12.2.0.0.2 Automatic Statistics Advisor Job Errors with Statistics Fatal Error (Doc ID 2448436.1)
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
AUTO_STATS_ADVISOR_TASK Running Outside of Maintenance Window (Doc ID 2387110.1)
ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c)(Doc ID 2127675.1)
How To Set DAYS_TO_EXPIRE and EXECUTION_DAYS_TO_EXPIRE of Automatic Statistics Advisor Task (Doc ID 2544788.1)
升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长 (Doc ID 2440139.1)
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)
How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)

©著作权归作者所有：来自51CTO博客作者jsj_007的原创作品，如需转载，请注明出处，否则将追究法律责任

好知识，才能预见未来

赞赏

0人进行了赞赏支持

好知识，才能预见未来

更多相关文章

随机推荐