10G上查詢時間0秒的ORA-1555錯誤

最近又是連續幾天和bug打交道。其實ORA-1555錯誤應該是比較常見的錯誤資訊，在回滾段自動管理模式下，如果查詢的執行時間超過了初始化引數UNDO_RETENTION的設定，就可能導致這個問題。

 

 

不過這個錯誤比較奇怪，出錯SQL的執行時間居然是0秒：

Wed Mar 25 01:19:28 2009
ORA-01555 caused by SQL statement below (SQL ID: 5t3djd7212nsm, Query Duration=0 sec, SCN: 0x0002.144867d3):
Wed Mar 25 01:19:28 2009
SELECT /*+ */ DISTINCT "A1"."PURCHASE_ID" FROM "CENTER"."MLOG$_ORD_PURCHASE" "A1" WHERE "A1"."PURCHASE_ID"<>ALL (SELECT "A2"."PURCHASE_ID" FROM "CENTER"."ORD_PURCHASE" "A2" WHERE "A1"."PURCHASE_ID"="A2"."PURCHASE_ID") AND "A1"."SNAPTIME$$">:1 AND "A1"."DMLTYPE$$"<>'I'

從這個錯誤資訊可以看出兩點，除了前面提到的查詢時間為0秒這個資訊外，第二點就是這個SQL是系統發出的SQL。而且是系統在執行物化檢視重新整理時，從基表和物化檢視日誌中獲取需要重新整理的資料時，出現的錯誤。

這個錯誤發生在主站點，在對應的目標站點，可以看到對應的錯誤資訊：

Errors in file /u1/oracle/admin/repdb01/bdump/repdb01_j002_6026.trc:
ORA-12012: error on auto execute of job 1
ORA-12008: error in materialized view refresh path
ORA-01555: 快照過舊: 回退段號 20 (名稱為 "_SYSSMU20$") 過小
ORA-02063: preceding line from NEWTRADE
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

其實如果只是SQL查詢時間為0，且產生ORA-1555錯誤，這個問題倒是碰到過，在9i中指定一個過早的TIMESTAMP進行閃回查詢，就會引發這個錯誤：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
     31400

SQL> SELECT COUNT(*) FROM TEST AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' DAY);
SELECT COUNT(*) FROM TEST AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' DAY)
                     *
ERROR 位於第 1 行:
ORA-01555: 快照過舊: 回退段號 5 在名稱為 "_SYSSMU5$" 過小

這時後臺就會出現查詢時間為0的ORA-1555錯誤：

Wed Mar 25 16:49:24 2009
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0000.a5f572cc):
Wed Mar 25 16:49:24 2009
SELECT COUNT(*) FROM TEST AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' DAY)

不過這種報錯方式在10g中已經有了改變：

SQL> SELECT COUNT(*) FROM T_NUMBER;

  COUNT(*)
----------
         2

SQL> SELECT COUNT(*) FROM T_NUMBER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' DAY);
SELECT COUNT(*) FROM T_NUMBER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' DAY)
                     *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

而當前的環境是10g的，且並沒有執行AS OF查詢，所以出現這個錯誤就很奇怪了。

檢查系統中UNTO_RETENTION的設定：

bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 3月 25 16:18:46 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

雖然資料庫中UNDO_RETENTION的設定並不長，但是對於支援絕大部分SQL的執行是沒有問題的，何況這個SQL的執行時間是0。

檢視了V$UNDOSTAT檢視，沒有發現重現ORA-1555錯誤的時間點有何異常。

搜尋了一下METALINK，結果確定了是Oracle的bug：Bug No. 6799685。這個bug還處於解決的過程中，估計整個10.2版本都可能會出現這個問題。

不過這個bug不可重現，而且出現機率很低，一般第二次執行同樣的SQL，就不會有問題，因此對系統影響不大。