OraFAQ Forum: Server Administration » Fetch across commit, ora-1555

Home » RDBMS Server » Server Administration » Fetch across commit, ora-1555 (DB > 9.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fetch across commit, ora-1555 [message #660844]

Mon, 27 February 2017 12:33

John Watson
Messages: 8978
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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660846 is a reply to message #660844]

Mon, 27 February 2017 12:46

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660847 is a reply to message #660846]

Mon, 27 February 2017 12:49

John Watson
Messages: 8978
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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660848 is a reply to message #660847]

Mon, 27 February 2017 12:55

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660850 is a reply to message #660844]

Mon, 27 February 2017 13:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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;

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660851 is a reply to message #660848]

Mon, 27 February 2017 13:13

Caffeine+
Messages: 14
Registered: February 2017

Junior Member

Are there any LOB columns in the query that run's into the ORA-1555?

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660854 is a reply to message #660851]

Mon, 27 February 2017 13:40

John Watson
Messages: 8978
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.

Report message to a moderator

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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660877 is a reply to message #660876]

Tue, 28 February 2017 02:59

Michel Cadot
Messages: 68758
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: 8978
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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660890 is a reply to message #660889]

Tue, 28 February 2017 08:37

Roachcoach
Messages: 1576
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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660891 is a reply to message #660890]

Tue, 28 February 2017 08:40

John Watson
Messages: 8978
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.

Report message to a moderator

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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660893 is a reply to message #660892]

Tue, 28 February 2017 08:49

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Fetch across commit, ora-1555 [message #660894 is a reply to message #660891]

Tue, 28 February 2017 08:52

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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 Smile

Smile

~~I don't have an example to hand at the minute though.~~

Code: [Select all] [Show/ hide]

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 09 20:48:16 CDT 2025