Re: ORA-01555 seems bogus
Date: Sat, 6 Feb 2010 19:58:26 -0800 (PST)
Message-ID: <789785.24231.qm_at_web32007.mail.mud.yahoo.com>
Hey Dave,
Have you tried a different approach and investigated what is actually in your UNDO tablespace? How much space do you really have available to allocate to the process? It does sound like you have investigated the current direction with some depth and
I would also be curious to see the query utilized stating the process had a 12,000 second duration. Some of the information in the undo views can be a bit misleading, where a previous statement might be the culprit instead of the current one.
I might recommend looking in the undo tablespace at the queries that are not active, with a retention that is quite high, (I am always looking for those higher than the set undo_retention time) I've ordered these by the maxqueryid. You can also use the query below and change what you want to look deeper into the undo at what EXACTLY is in your undo, not just unactive, etc...
select vst.sql_text, vu.maxqueryid, vu.maxquerylen, vu.tuned_undoretention from v$undostat vu, v$session vs, v$sqltext vst where vu.tuned_undoretention > 43200 <--set this to your undo_retention!! and vu.maxqueryid=vst.sql_id
and vst.piece=0
and vu.maxqueryid not in vs.sql_id
group by vst.sql_text,vu.maxqueryid,vu.maxquerylen, vu.tuned_undoretention order by vu.maxqueryid;
If you run the above query, do you see your accused statement? Then you have an avenue to look down for what is behind this statement and why it might still be utilizing undo if it's not active. First look at the code that the SQL_ID belongs to:
select o.owner, o.object_name, o.object_type, s.program_line#
from v$sql s, dba_objects o
where sql_id = '<SQL_ID>'
and s.program_id=o.object_id;
This will give you the exact object and line the statement comes from, (especially helpful if you have code like mine where it's re-used often...)
Next, if this code is listed as the issue, then you are going to have to "prove it innocent"... Trace back through the session to the prev_id, (use the sql_id from your statement and then select from v$session where prev_id equals it.) See if it is being mistakenly blamed and this should aim you to the processes that are eating up the undo if it's a still active process or a background process if it's a process that's gone awry.
Hope this helps,
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
- On Sat, 2/6/10, Herring Dave - dherri <Dave.Herring_at_acxiom.com> wrote:
From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Subject: ORA-01555 seems bogus
To: oracle-l_at_freelists.org
Date: Saturday, February 6, 2010, 8:10 PM
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_at_DNB.com
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.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sat Feb 06 2010 - 21:58:26 CST