ORA-01555 caused by SQL statement below

一.发现ORA-01555

alert.log 告警日志：
Fri Apr 12 03:11:34 2013
ORA-01555 caused by SQL statement below (Query Duration=39516 sec, SCN: 0x0b06.a18b860b):
Fri Apr 12 03:11:34 2013
SELECT COUNT(*) FROM (SELECT DISTINCT ACCOUNT AS NAME FROM v_query_line_allquery WHERE FLDTAG = 'GZD002002') ROW_ WHERE upper(ROW_.NAME) LIKE '%123%'
Fri Apr 12 03:12:30 2013
ORA-01555 caused by SQL statement below (Query Duration=39572 sec, SCN: 0x0b06.a18b85e5):
Fri Apr 12 03:12:30 2013
SELECT COUNT(*) FROM (SELECT DISTINCT ACCOUNT AS NAME FROM v_query_line_allquery WHERE FLDTAG = 'GZD002002') ROW_ WHERE upper(ROW_.NAME) LIKE '%XO%'
Fri Apr 12 03:13:47 2013
ORA-01555 caused by SQL statement below (Query Duration=39364 sec, SCN: 0x0b06.a18ba41c):
Fri Apr 12 03:13:47 2013
SELECT COUNT(*) FROM (SELECT DISTINCT ACCOUNT AS NAME FROM v_query_line_allquery WHERE FLDTAG = 'GZD002002') ROW_ WHERE upper(ROW_.NAME) LIKE '%新华社%
二.数据库状态
$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Apr 12 10:15:50 2013

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

SQL> conn /as sysdba
Connected.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                             integer     21600                    --s
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> SELECT DISTINCT STATUS "状态",
        COUNT(*) "EXTENT数量",
         SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
         FROM DBA_UNDO_EXTENTS
         GROUP BY STATUS;

状态      EXTENT数量   UNDO大小
--------- ---------- ----------
ACTIVE             1             .000976563
EXPIRED          286         .23840332
UNEXPIRED         88      .110923767

SQL> select sum(maxbytes)/1024/1024/1024,
          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
---------------------------- ------------------------------
                  31.9999847                     28.4031982

通过undo_retention保留时间为21600秒，而该sql执行了39572秒，在这39572秒钟，v_query_line_allquery表中的数据被修改，
而且被修改的undo数据在21600秒后被覆盖导致，导致原查询语句不能获取到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空间大小;

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
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)扫描执行一遍，或者收集全部统计信息

 

http://www.xifenfei.com/2296.html