Home » RDBMS Server » Performance Tuning » Need help to understand in depth about ORA-01555 (Oracle , 9.2.0.8, solaris)
Need help to understand in depth about ORA-01555 [message #524223] Thu, 22 September 2011 05:11 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I am getting the below error in alert log file,when my application calling a procedure.

ORA-01555 caused by SQL statement below (Query Duration=1576 sec, SCN: 0x09a2.5dda3165):
Fri Sep 16 16:33:40 2011
UPDATE SSPT_NETWORK_DETAILS SET INCLUDE_OFFERS = 'Yes' WHERE SESS_ID = SESS_ID

There is no ROLLBACK statement in my procedure.

As per my understanding, the ORA-1555 error will occur,

1. The required old image is not in the undo,when we rollback the trasaction.
2. the select query may face this error because of delayed block cleanout concept.

But I don't know why this update statement causing this 1555 error? Can anybody help to clarify this?
Re: Need help to understand in depth about ORA-01555 [message #524235 is a reply to message #524223] Thu, 22 September 2011 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1. The required old image is not in the undo,when we rollback the trasaction

No, when you need it that is when you search for the block (SELECT)

Quote:
2. the select query may face this error because of delayed block cleanout concept.

No, never.

Quote:
But I don't know why this update statement causing this 1555 error?

Because Oracle cannot (re)build the block as it was at the time the update started.

Regards
Michel
Re: Need help to understand in depth about ORA-01555 [message #524239 is a reply to message #524235] Thu, 22 September 2011 06:11 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi Michel,

Thanks for your response.

Quote:
Because Oracle cannot (re)build the block as it was at the time the update started.


I am not clear in your answer.Can you please give me some more detail?

why oracle has to (re)build the block? Also this update statement is running 200 to 250 times per hour. every time, it is updating 10 to 50 records only.
Re: Need help to understand in depth about ORA-01555 [message #524240 is a reply to message #524239] Thu, 22 September 2011 06:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read up on read consistency in the DB concepts manual
Re: Need help to understand in depth about ORA-01555 [message #524243 is a reply to message #524240] Thu, 22 September 2011 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to CM introduction, read How Oracle Manages Data Concurrency and Consistency

Regards
Michel

Re: Need help to understand in depth about ORA-01555 [message #524248 is a reply to message #524239] Thu, 22 September 2011 06:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
it is updating 10 to 50 records only.
Surely that is not correct? Your predicate is WHERE SESS_ID = SESS_ID which is always TRUE, so you are updating every row in the table.
Re: Need help to understand in depth about ORA-01555 [message #524249 is a reply to message #524248] Thu, 22 September 2011 06:56 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so you are updating every row in the table.

But those with null SESS_ID. Wink

Regards
Michel
Previous Topic: POWER function slows query way down?
Next Topic: Performance Issue
Goto Forum:
  


Current Time: Fri Mar 29 00:23:24 CDT 2024