一.發現ORA-01555

Mon Dec 26 10:08:22 2011

ORA-01555 caused by SQL statement below (Query Duration=49146 sec, SCN: 0x0b4b.17f5ae42):

Mon Dec 26 10:08:22 2011

SELECT COMPANY_ID,

       COMPANY_MOBILE,

       TO_CHAR(NVL(REG_DATE, SYSDATE - 100), 'yyyymmddhh24miss'),

       BAK_FIELD2

  FROM TAB_XN_COMPANY

 WHERE (COMPANY_STATUS = 1 OR

       (COMPANY_STATUS = 3 AND

       NVL(UNREG_DATE, SYSDATE + 100) >=

       TO_DATE('20111226094500', 'yyyymmddhh24miss')))

   AND NVL(REG_DATE, SYSDATE - 100) <=

       TO_DATE('20111226095959', 'yyyymmddhh24miss')

   AND PAY_TYPE > 0

二.資料庫狀態

[oracle@ora02 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 4 10:48:17 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     10800

undo_suppress_errors                 boolean     FALSE

undo_tablespace                      string      UNDOTBS1

SQL> select sum(maxbytes)/1024/1024/1024,

   2 SUM(USER_BYTES)/1024/1024/1024 FROM dba_data_files where tablespace_NAME='UNDOTBS1';

SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024

---------------------------- ------------------------------

                  61.9999847                     32.6834106

SQL> SELECT DISTINCT STATUS "狀態",

  2                  COUNT(*) "EXTENT數量",

  3                  SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"

  4    FROM DBA_UNDO_EXTENTS

  5   GROUP BY STATUS;

狀態      EXTENT數量   UNDO大小

--------- ---------- ----------

ACTIVE             1 .000976563

EXPIRED         2549 31.2333298

UNEXPIRED          3 .000175476

透過undo_retention保留時間為10800秒，而該sql執行了49146秒，在這49146秒鐘，TAB_XN_COMPANY表中的資料被修改，而且被修改的undo資料在10800秒後被覆蓋導致，導致原查詢語句不能獲取到scn小於或者等於查詢時候的資料塊內容(在undo中)，所以出現ORA-01555。從這裡也可以看出來，在undo空間還剩餘的情況下，如果超過了undo_retention限制，undo內容還是有可能被覆蓋，而不是使用未使用的undo

三.出現ORA-1555原因
The ORA-1555 errors can happen when a query is unable to access enough undo to build a copy of the data at the time the query started. Committed “versions” of blocks are maintained along with newer uncommitted “versions” of those blocks so that queries can access data as it existed in the database at the time of the query. These are referred to as “consistent read” blocks and are maintained using Oracle undo management.
就是一個查詢要訪問某個資料塊，而這個資料塊在這個查詢執行過程中修改過，那麼該查詢需要查詢undo中資料塊，而undo中該資料塊已經不存在，從而出現ORA-1555

四.ORA-1555解決方法
Case 1 – Rollback Overwritten
1.縮短sql執行時間
2.增加undo_retention，這個同時需要考慮undo空間大小
3.減少commit(rollback)次數
4.在一條sql中儘量使資料塊訪問一次
4.1)Using a full table scan rather than an index lookup
4.2)Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks.

Case 2 – Rollback Transaction Slot Overwritten
這種問題，主要是延遲塊清理導致，一般建議在進行大批次的dml操作後，使用全表(全index)掃描執行一遍，或者收集全部統計資訊

ORA-01555 caused by SQL statement below

相關文章