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

2024-01-29 17:09:58

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

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

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

修改任务结果过期时间

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;
/

码农公寓

相关文章