Home » RDBMS Server » Server Administration » Fetch across commit, ora-1555 (DB > 9.0)
Fetch across commit, ora-1555 [message #660844] Mon, 27 February 2017 12:33 Go to next message
John Watson
Messages: 7608
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 #660846 is a reply to message #660844] Mon, 27 February 2017 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
https://docs.oracle.com/cd/B28359_01/server.111/b28310/undo002.htm#ADMIN10180

The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace.
Re: Fetch across commit, ora-1555 [message #660847 is a reply to message #660846] Mon, 27 February 2017 12:49 Go to previous messageGo to next message
John Watson
Messages: 7608
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 #660848 is a reply to message #660847] Mon, 27 February 2017 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
This issue is between you & Oracle.
From my perspective you have to choose between three alternatives.
1) Live with what you have.
2) remove the COMMIT from inside the LOOP
3) submit Service Request to MOS.
I really don't care which you decide to do.
Re: Fetch across commit, ora-1555 [message #660850 is a reply to message #660844] Mon, 27 February 2017 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try changing your undo tablespace to guarantee then even committed undo data should be kept if there are not too old:
ALTER TABLESPACE undo RETENTION GUARANTEE;

Re: Fetch across commit, ora-1555 [message #660851 is a reply to message #660848] Mon, 27 February 2017 13:13 Go to previous messageGo to next message
Caffeine+
Messages: 14
Registered: February 2017
Junior Member
Are there any LOB columns in the query that run's into the ORA-1555?
Re: Fetch across commit, ora-1555 [message #660854 is a reply to message #660851] Mon, 27 February 2017 13:40 Go to previous messageGo to next message
John Watson
Messages: 7608
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 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 7608
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 #660890 is a reply to message #660889] Tue, 28 February 2017 08:37 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Remember that committing in a loop will skew your stats because the undo is rarely going to show in use.
Re: Fetch across commit, ora-1555 [message #660891 is a reply to message #660890] Tue, 28 February 2017 08:40 Go to previous messageGo to next message
John Watson
Messages: 7608
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 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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 #660893 is a reply to message #660892] Tue, 28 February 2017 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I've seen snapshot too old errors and they are not shown there when I look.
There should be in SSOLDERRCNT column.

Re: Fetch across commit, ora-1555 [message #660894 is a reply to message #660891] Tue, 28 February 2017 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I hadn't thought of that. It should still show up in v$undostat as UNEXPIREDBLKS, though
Unless they have been stolen, this can be seen in UNXPSTEALCNT a,d/or UNXPBLKRELCNT columns.

Re: Fetch across commit, ora-1555 [message #660895 is a reply to message #660893] Tue, 28 February 2017 08:52 Go to previous message
Roachcoach
Messages: 1571
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 Smile

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

Previous Topic: Golden gate - In-database archiving
Next Topic: Oracle12 client
Goto Forum:
  


Current Time: Mon Oct 15 19:33:32 CDT 2018