RE: ORA-01555 seems bogus
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 7 Feb 2010 16:28:33 -0600
I'm not sure yet of all DML activities of this table (I'm still new to the
client). With the comparison of LAST_ANALYZED and DBA_TAB_MODIFICATIONS, I was
trying to show that it appears it was not involved in DML during the rash of
ORA-01555 errors. While I don't know the frequency of updates of the
underlying tables of DBA_TAB_MODIFICATION, I'm pretty sure it's less than a few
days. I believe MOS article 762738.1 says the stats are flushed every 3 hours.
What I've found is that Kellyn and others are leading me to the right path.
This table is being queried as part of a package. I'll need to dig through the
package to determine what's involved, as probably the query is within a block
and that block potentially accesses other objects that are being modified
concurrently. Just a guess. It would have been easiest to suggest jacking
undo_retention way up but I'd rather determine the exact cause and ideally help
the development staff change coding methods to avoid this situation. I
probably dream too much. :-)
Dave Herring | DBA, Global Technology Services
A c x i o m C o r p o r a t i o n
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
Service Desk: 888-243-4566, https://servicedesk.acxiom.com, GSCA@xxxxxxx
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Sunday, February 07, 2010 11:20 AM
To: Herring Dave - dherri; oracle-l@xxxxxxxxxxxxx
Subject: RE: ORA-01555 seems bogus
What is updating the 3100 row table? What is the commit frequency? Is
something doing an uncommitted update, a wait, and then a rollback? Does the
frequency occurrence of the 1555 plausibly overlap with rollbacks? I haven't
checked yet, does dba_tab_modifications get hit only on commit? I'm afraid I
don't have your version handy to test.
The test of course could be very simple, just create a junk table, put a row
in and commit, check dba_tab_modifications, update with no commit, check
dba_tab_modifications from another session, rollback, check
dba_tab_modifications again. Maybe the test could be even simpler but I
didn't have time to think it through much.
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Herring Dave - dherri
Sent: Saturday, February 06, 2010 10:10 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA-01555 seems bogus
Folks, I've got a curious situation where we're getting regular ORA-01555
errors yet they don't seem correct. First a few things on the set up:
* 4-node RAC (+ 4-node RAC DG)
* 10.2.0.2
* RHEL 8 x 64GB servers
The query returned in the error is doing a full scan of a 3,100 row table,
which takes less than 1 sec to complete. I've tried the same query
repeatedly and it always takes less than one second, even when trying it
while ORA-01555 errors are being returned saying that query is failing
elsewhere. The query is run 10,000+ times per day, always run from the same
node.
The ORA-01555 error lists this query as having a duration of around 12,000
seconds each time, which from what I can tell is impossible. Again, it's a
3,100 row table, no parallelism, 1 index (which isn't used by this query).
I checked LAST_ANALYZED and the table hasn't been analyzed in 4 days. I
checked DBA_TAB_MODIFICATIONS and it's had around 200 DML's since it was
analyzed, but the last of which was 2 days ago.
So is Oracle returning the wrong query? Is the ORA-01555 bogus? Checking
GV$UNDOSTAT, SSOLDERRCNT matches the # of ORA-01555 we're seeing (87 today,
all the same query). All instances have the same UNDO_RETENTION (3600) and
similarly sized undo tablespaces.
Any clues how to catch the trouble making queries?
Dave Herring | DBA, Global Technology Services
A c x i o m C o r p o r a t i o n
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
Service Desk: 888-243-4566, https://servicedesk.acxiom.com, GSCA@xxxxxxx
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
