| Fetch across commit, ora-1555 [message #660844] |
Mon, 27 February 2017 12:33  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I have a problem with a procedure: it sometimes fails with a 1555 after a couple of hours. The code is doing what you are not supposed to do: committing within a cursor loop. The developer says "the procedure never takes more than three hours, so if you set undo_retention=10800 then it will always succeed" only it doesn't. Does anyone have an explanation of why the undo_retention does not work as one would like? Everyone knows that one should never fetch across commit in a loop cursor loop but I cant find a reason why the undo_retention does not fix the problem. I could do with a reason so that I can tell the developers to rewrite the code.
Thank you for any insight.
|
|
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660847 is a reply to message #660846] |
Mon, 27 February 2017 12:49  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Yes, but in that case the undo retention should be optimized to retain as much as possible given the size of the tablespace. Querying v$undostat (I don't have an example) always shows that the tuned undo retention is many times longer than the requirement.
|
|
|
|
|
|
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660854 is a reply to message #660851] |
Mon, 27 February 2017 13:40 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I (and others) have found that undo_retention even in conjunction with retention guarantee does not appear to function reliably in the case of a 1555 caused by fetch across commit. LOBs were a nice idea, but it isn't that. The last time I raised a TAR on this, all I got was "don't do it" without being told why not. It would be very helpful to have a reason for why this problem persists. Ask Tom has a few articles, but they all avoid the problem by saying the usual "don't do it" without saying why not. If it is a bug, OK: one can live with that. But I can't find any admission of this.
Thank you for the replies. It was always going to be a long shot that anyone could explain why.
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660876 is a reply to message #660854] |
Tue, 28 February 2017 02:29 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I've never tried guarantee (blocking DML would be suicide here), but the reason retention alone will not work is because it will always yield to pressure caused by other DML in order to not block it.
The v$undostat and the dba_hist flavour will shed some light on what is happening, but I'm assuming you've already looked there.
Additionally remember that you'll almost certainly find that the undo pressure is much higher than you would expect due to the fetching across the commits, unless you're remarkably lucky in the order of rows processed. i.e. You'll be all but guaranteed to have multiple versions of the "same" block for every row you've touched.
The developers assumption is completely flawed, especially if there are other operations happening in the database - which there *always* are, even if that is the database working recursively.
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660877 is a reply to message #660876] |
Tue, 28 February 2017 02:59 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:The v$undostat and the dba_hist flavour will shed some light on what is happening, but I'm assuming you've already looked there.
Yes, an abstract of DBA_HIST_UNDOSTAT (or similar) for the period during the procedure executes along with undo_retention parameter value and undo tablespace size during this period will help to diagnose.
[Updated on: Tue, 28 February 2017 03:01] Report message to a moderator |
|
|
|
| Re: Fetch across commit, ora-1555 [message #660889 is a reply to message #660877] |
Tue, 28 February 2017 08:34 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I don't have dba_hist_whatever (Standard Edition) the v$undostat rows showed that there shouldn't have been a problem: tuned undo retention of thousands of seconds longer than needed, and statspack showing that my undo tablespace had enough room to rollback all of Jupiter. All very odd. I've been here before: fetch across commit seems to be an insoluble problem. Perhaps the only solution is "don't do it".
Thank you all for the advice.
|
|
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660891 is a reply to message #660890] |
Tue, 28 February 2017 08:40 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Ah. I hadn't thought of that. It should still show up in v$undostat as UNEXPIREDBLKS, though. I'll ask the client to keep proper records next time it happens (if it happens....) in the meantime we've set the undo tablespace to autoextend. That will require a bit of monitoring.
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660892 is a reply to message #660891] |
Tue, 28 February 2017 08:43 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Yep, truth be told I've found that view....questionable. I've seen snapshot too old errors and they are not shown there when I look.
Have them run it without the commit in the middle and see what kind of undo use you see.
|
|
|
|
|
|
|
|
| Re: Fetch across commit, ora-1555 [message #660895 is a reply to message #660893] |
Tue, 28 February 2017 08:52 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I know, but they've not always been there nor the expected accompanying items I'd have expected like unexpired stolen, much like John describes 
I don't have an example to hand at the minute though.
BEGIN_TIME : 20-feb-2017 13:23:03
END_TIME : 20-feb-2017 13:33:03
DBID : 3399676100
INSTANCE_NUMBER : 1
SNAP_ID : 165547
UNDOTSN : 28
UNDOBLKS : 64352
TXNCOUNT : 219595
MAXQUERYLEN : 3197
MAXQUERYSQLID : d4zt50d826gtq
MAXCONCURRENCY : 42
UNXPSTEALCNT : 0
UNXPBLKRELCNT : 0
UNXPBLKREUCNT : 0
EXPSTEALCNT : 0
EXPBLKRELCNT : 0
EXPBLKREUCNT : 0
SSOLDERRCNT : 1
NOSPACEERRCNT : 0
ACTIVEBLKS : 54528
UNEXPIREDBLKS : 9076480
EXPIREDBLKS : 5373104
TUNED_UNDORETENTION : 43200
From the DB this was taken from, there's no way this hit that error without some blocks being stolen etc.
[Updated on: Tue, 28 February 2017 08:57] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
