Home » RDBMS Server » Performance Tuning » ORA-01555 (Oracle 11gR2, Linux)
ORA-01555 [message #506686] Tue, 10 May 2011 09:28 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I have encountered ORA-01555 and trying to find what caused the issue.

#UNDO parameters
undo_management AUTO
undo_retention 0
undo_tablespace UNDOTBS1

set pagesize 25
set linesize 120

select inst_id,tuned_undoretention,to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
          MAXQUERYLEN,MAXQUERYID,
          UNXPSTEALCNT "# Unexpired|Stolen",
          EXPSTEALCNT "# Expired|Reused",
          SSOLDERRCNT "ORA-1555|Error",
          NOSPACEERRCNT "Out-Of-space|Error"
from gv$undostat
where begin_time between
     to_date('05/10/2011 05:45:54','MM/DD/YYYY HH24:MI:SS')-8.65/24
and
     to_date('05/10/2011 05:45:54','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time
/


   INST_ID BEGIN_TIME       # Unexpired|Stolen # Expired|Reused ORA-1555|Error Out-Of-space|Error          Max query
---------- ---------------- ------------------ ---------------- -------------- ------------------            --
         1 05/10/2011 04:28                  0                0              0                  0            26916
         1 05/10/2011 04:38                  0                0              0                  0            27517
         1 05/10/2011 04:48                  0                0              0                  0            28118
         1 05/10/2011 04:58                  0                0              0                  0            28719
         1 05/10/2011 05:08                  0                0              0                  0            29321
         1 05/10/2011 05:18                  0                0              0                  0            29922
         1 05/10/2011 05:28                  0                0              0                  0            30523
         1 05/10/2011 05:38                  0                0              1                  0            31124



at the end you can see there is one occurance of ORA-555, but no Unexpired or expired blocks were over written .why Oracle didnt try to use them ?can someone explain ?
Re: ORA-01555 [message #506688 is a reply to message #506686] Tue, 10 May 2011 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have encountered ORA-01555 and trying to find what caused the issue.
Some session is doing DML against table from which SQL is selecting.

Session reporting the ORA-01555 error is victim; not culprit.
Re: ORA-01555 [message #506690 is a reply to message #506688] Tue, 10 May 2011 09:33 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Yesssss, never thought in that angle. Thanks Swan
Re: ORA-01555 [message #506691 is a reply to message #506690] Tue, 10 May 2011 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Yesssss, never thought in that angle. Thanks Swan
session reporting ORA-01555 could be BOTH victim & culprit if code contains COMMIT inside a LOOP.
Re: ORA-01555 [message #506692 is a reply to message #506690] Tue, 10 May 2011 09:41 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
in my case, why "culprit" session couldnt over write expired blocks so that other session could read it ?
Re: ORA-01555 [message #506693 is a reply to message #506692] Tue, 10 May 2011 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>in my case, why "culprit" session couldnt over write expired blocks so that other session could read it ?
Culprit session "must" be doing COMMIT which allow blocks to be overwritten & these blocks are required by SELECT for read consistency.

[Updated on: Tue, 10 May 2011 09:51]

Report message to a moderator

Re: ORA-01555 [message #506697 is a reply to message #506693] Tue, 10 May 2011 09:51 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Delayed block clean out can cause it but its pretty damned rare next to the other causes.
Re: ORA-01555 [message #506700 is a reply to message #506697] Tue, 10 May 2011 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
too short RETENTION can also produce ORA-01555
SQL> show parameter retention

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target	     integer	 1440
undo_retention			     integer	 900
Re: ORA-01555 [message #506701 is a reply to message #506697] Tue, 10 May 2011 09:58 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
"Delayed block clean out " - Yes that should be the reason. Else i would have seen some entry for "Expired|reused" blocks
Re: ORA-01555 [message #506702 is a reply to message #506701] Tue, 10 May 2011 09:58 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
my undo retention is AUTO

TUNED_UNDORETENTION MAXQUERYLEN MAXQUERYID    # Unexpired|Stolen # Expired|Reuse
------------------- ----------- ------------- ------------------ ----------
              28213       26916 dsf73v3jy2xz1                  0                0              0       
              27694       27517 dsf73v3jy2xz1                  0                0              0       
              29414       28118 dsf73v3jy2xz1                  0                0              0       
              29571       28719 dsf73v3jy2xz1                  0                0              0       
              29909       29321 dsf73v3jy2xz1                  0                0              0       
              29893       29922 dsf73v3jy2xz1                  0                0              0       
              30068       30523 dsf73v3jy2xz1                  0                0              0       
              30196       31124 dsf73v3jy2xz1                  0                0              1       
              30275        4085 ckbtc1ax928tk                  0                0              0       
Re: ORA-01555 [message #506703 is a reply to message #506702] Tue, 10 May 2011 10:00 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com

do KEYWORD search on ORA-01555

he has a couple of nice articles on this topic in detail with examples.
Previous Topic: verify if procedure has been completed
Next Topic: Query Performance Issue
Goto Forum:
  


Current Time: Thu Mar 28 04:56:28 CDT 2024