一个auto space advisor顾问SQL导致的01555错误分析

2020-10-22 14:45:34

01555错误往往是由于长查询造成的，由于undo的不足造成快照太旧，从而无法实现一致读，在Oracle引擎内部，自身的顾问工具需要收集段空间信息，从而给出空间回收等建议，这些SQL也可能导致01555错误，注意，这个错误不影响业务用户。一下是分析过程和处理方式。

告警详细信息
2020-10-22T00:38:52.834184+08:00
ORA-01555 caused by SQL statement below (SQL ID: 4757g72w150w9, Query Duration=9416 sec, SCN:
0x00000ece9dd1f990
):
2020-10-22T00:38:52.834308+08:00
select owner, object_name, partition_name, segment_type, tablespace_name, lobcol_name, lobseg_name, lobpart_name, iot_name from wri$_adv_segadv_segrow
2020-10-22T00:38:52.839172+08:00
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_68002"
ORA-01555: snapshot too old: rollback segment number 60 with name "_SYSSMU60_3671991499$" too small
ORA-06512: at "SYS.DBMS_ADVISOR", line 212
ORA-06512: at "SYS.PRVT_ADVISOR", line 3333
ORA-06512: at "SYS.PRVT_ADVISOR", line 756
ORA-06512: at "SYS.WRI$_ADV_OBJSPACE_TREND_T", line 1882
ORA-06512: at "SYS.PRVT_ADVISOR", line 739
ORA-06512: at "SYS.PRVT_ADVISOR", line 3238
ORA-06512: at "SYS.DBMS_ADVISOR", line 262
ORA-06512: at "SYS.DBMS_ADVISOR", line 207
ORA-06512: at "SYS.DBMS_SPACE", line 2440
ORA-06512: at "SYS.DBMS_SPACE", line 2730

分析：Oracle在执行自动空间顾问相关的job时，查询的SQL造成01555错误，从这个语句看是从底层视图数据中查找关于段空间空间变化信息，
确定段是否有可以回收的空间，从而给出整理碎片的建议，自动任务名为 "auto space advisor"
以供自动空间顾问使用，而这个查询超时了。
而这个查询不会记录 v$undostat dba_hist_undostat中，这个SQL的用户是sys也就是数据库引擎执行的查询操作
SQL> select max(MAXQUERYLEN) from v$undostat;

MAX(MAXQUERYLEN)
----------------
0

SQL> select max(MAXQUERYLEN) from dba_hist_undostat;

MAX(MAXQUERYLEN)
----------------
0
措施：针对这个错误，一个采取不关注的态度，这个对数据库性能没有影响，另一个就是关闭空间顾问自动任务。
下面我们查询当前库的几个自动任务情况
SQL> select client_name,status from dba_autotask_client;

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

下面关闭该自动任务

SQL> begin
dbms_auto_task_admin.disable(
client_name=>'auto space advisor',
operation=>NULL,
window_name=>NULL);
end;
/

PL/SQL procedure successfully completed.

begin
dbms_auto_task_admin.disable(
client_name=>'sql tuning advisor',
operation=>NULL,
window_name=>NULL);
end;
/
验证结果：
SQL> select client_name,status from dba_autotask_client;

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