OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » ORA-01555 (Oracle 11gR2, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01555 [message #506686]

Tue, 10 May 2011 09:28

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 ?

Report message to a moderator

Re: ORA-01555 [message #506688 is a reply to message #506686]

Tue, 10 May 2011 09:31

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.

Report message to a moderator

Re: ORA-01555 [message #506690 is a reply to message #506688]

Tue, 10 May 2011 09:33

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Yesssss, never thought in that angle. Thanks Swan

Report message to a moderator

Re: ORA-01555 [message #506691 is a reply to message #506690]

Tue, 10 May 2011 09:37

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.

Report message to a moderator

Re: ORA-01555 [message #506692 is a reply to message #506690]

Tue, 10 May 2011 09:41

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 ?

Report message to a moderator

Re: ORA-01555 [message #506693 is a reply to message #506692]

Tue, 10 May 2011 09:44

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

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.

Report message to a moderator

Re: ORA-01555 [message #506700 is a reply to message #506697]

Tue, 10 May 2011 09:54

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

Report message to a moderator

Re: ORA-01555 [message #506701 is a reply to message #506697]

Tue, 10 May 2011 09:58

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

Report message to a moderator

Re: ORA-01555 [message #506702 is a reply to message #506701]

Tue, 10 May 2011 09:58

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

Report message to a moderator

Re: ORA-01555 [message #506703 is a reply to message #506702]

Tue, 10 May 2011 10:00

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.

Report message to a moderator

Previous Topic:	verify if procedure has been completed
Next Topic:	Query Performance Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 14:14:24 CST 2025