ORA-01555 When Max Query Length Is Less Than Undo Retention [ID 1131474.1]
涉及的版本！
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
This issue was observed on Oracle 11.1.0.6 64-bit on a Solaris 64-bit server.It also occurs in 10gR2
现象：
- A job is consistently giving ORA-1555 errors.
- Increasing the undo_retention value has not stopped the errors.
- The ORA-1555 occurs even if there are no other jobs updating the objects reported in the failed SQL.
- The datafiles for the undo tablespace have autoextend turned on, and the file size is less than MAXBYTES.
- A look at the v$undostat shows that the query length is less than both the undo_retention and the tuned_undoretention
To find the max query length and the tuned_undo retention, run this SQL.
set pagesize 25
set linesize 100
column UNXPSTEALCNT heading "# Unexpired|Stolen"
column EXPSTEALCNT heading "# Expired|Reused"
column SSOLDERRCNT heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN heading "Max Query|Length"
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION
from gv$undostat
order by inst_id, begin_time;
Then look for the timestamp at which the ORA-1555 occurred.
The max query length will be less than undo_retention, and less than tuned_undoretention.
Note: When tuned_undoretention is used, the undo_retention becomes a minimum setting.
Here is an example of the output of the above SQL
Unexpired Expired ORA-1555 Out-Of-space Max Query
INST_ID BEGIN_TIME Stolen Reused Error Error Length TUNED_UNDORETENTION
------- ---------------- ---- ------ ----- ----- ---------- ------------
1 06/14/2010 20:51 0 0 0 0 719 2000
1 06/14/2010 21:01 0 0 1 0 1323 2225
1 06/14/2010 21:11 0 0 0 0 699 2000
1 06/14/2010 21:21 0 0 0 0 1303 2205
1 06/14/2010 21:31 0 0 0 0 678 2000
We can see that there were no stolen unexpired extents, and the length of
the query was 1313 seconds, while the tuned_undoretention was 2225 seconds.
There were also no out-if-space errors.
我们的环境里：
ops$admin@jrdw2>select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION
2 from gv$undostat
3 where begin_time between to_date('10/09/2011 03:10','MM/DD/YYYY HH24:MI')
4 and to_date('10/09/2011 04:50','MM/DD/YYYY HH24:MI')
5 order by inst_id, begin_time;
# Unexpired # Expired ORA-1555 Out-Of-space MaxQuery Tuned Undo
INST_ID BEGIN_TIME Stolen Reused Error Error Length Retention
---------- ----------- --------- -------- ---------- ---------- ----------
1 10/09/2011 03:13 0 0 0 0 1183 52010
1 10/09/2011 03:23 0 0 0 0 759 51895
2 10/09/2011 04:25 0 0 0 0 614 124019921
2 10/09/2011 04:35 0 0 1 0 1215 124112694
2 10/09/2011 04:45 0 0 0 0 595 124040364
20 rows selected.
原因：
This is the same issue as Bug 8231583
Abstract: ORA-1555 WHEN QUERY LENGTH
The bug has been closed at this point without finding a root issue. The problem is not easy to reproduce consistently.
Solution Bug 8231583 is unresolved.
措施：
方法一：Until the bug is resolved, one workaround is to create a new undo tablespace and switch to the new.
For example:
CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
The old undo can only be dropped after all transactions have cleared from the undo tablespace.
Please refer to the Oracle Database Administrator's manual for creating a new undo, switching to a new undo tablespace, and dropping the undo tablespace.
Oracle Database Administrator's Guide 10g Release 2 (10.2)
Chapter 10 Managing the Undo Tablespace
Oracle Database Administrator's Guide 11g Release 2 (11.2)
Chapter 15 Managing Undo
Section: Managing Undo Tablespaces
方法二：disabling auto tuning by setting "_undo_autotune" = false
after creating a new undo tablespace has avoided future occurrences of the problem condition at some database locations.
Until a consistent, reproducible test case can be developed in-house this problem remains unresolved.