RE: Weird ORA-1555 error

From: Matt Adams <MAdams_at_equian.com>
Date: Thu, 29 Sep 2016 12:37:13 +0000
Message-ID: <5c681fb48a6b4e3aa0667a7ba88ab9e2_at_wpvl1dag02.hcrec.com>

Thank you to everyone for the suggestions. Based on an e-mail that arrived outside the list, I did a little more checking. The ORA-1555 errors only occurred when running statements that did index lookups or scans. When a FULL hint was applied to the statements, the error went away.

Based on this, we did a rebuild on all indexes of the 4 tables that were having this problem and it appears that our problem has gone away. It would be nice to know exactly what caused the situation in the first place, but I won't lose any sleep over not knowing.

Matt Adams
"It will make sense when you stop thinking logically, and start thinking Oracle-ly" - Ken Droppa

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Matt Adams Sent: Wednesday, September 28, 2016 3:24 PM To: Oracle L
Subject: Weird ORA-1555 error

I've seen ORA-1555 (snapshot too old) lots of times in the past and they always seemed to be caused by 1 of 2 scenarios.

Either an image of a block needed by a long running query is not available because another session has modified and committed a change to the block and the 'before modification' image of the block is no longer available in the rollback segments. (common) Or it's a delayed block cleanout issue where the ITL in the block header needs maintenance. (uncommon, but not exceedingly rare)

I'm now getting an ORA-1555 error that doesn't seem to fit either case.

This particular table is roughly 11 million rows in a database 10 terrabytes in size and not terrible busy. There's 300 Gig of undo tablespace and UNDO_RETENTION is set for 40 hours. (don't ask why 40 hours. I didn't set it up that way and nobody seems to remember why it was set that way)

Running the following query returns an error in anywhere from 2 to 10 seconds.

SQL> select count(*) from app_owner.type_history; select count(*) from hri1_owner.type_history

                                 *

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 491 with name "_SYSSMU491_93190984$" too small

The error is repeatable, with the same rollback segment (number 491) listed every time.

Since I'm getting this error in sometimes as little as 2 seconds or so, the first scenario I described above doesn't seem to apply.

Usually, the recommended action to eliminate problems with delayed block cleanout is to either gather stats on the table or do a 'select * from table' to force oracle to visit every block. That doesn't seem to be working here. All attempts to analyze the table or select count(*) from the table get the same ORA-1555 error

This is v 11.2.0.4 (EE) on solaris.

Searches on the web and metalink (yes, I still call it that) have proven fruitless so far.

Anybody have any thoughts on why this might be occurring?

• This communication may contain privileged and/or confidential information. If you are not the intended recipient, you are hereby notified that disclosing, copying, or distributing of the contents is strictly prohibited. If you have received this message in error, please contact the sender immediately and destroy any copies of this document. ****
• This communication may contain privileged and/or confidential information. If you are not the intended recipient, you are hereby notified that disclosing, copying, or distributing of the contents is strictly prohibited. If you have received this message in error, please contact the sender immediately and destroy any copies of this document. ****

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 29 2016 - 14:37:13 CEST