Does ORA-1555 (snapshot too old) error occurs because of keeping the cursor open for long..?

[Vishwanath Patil]

Hi,

Does ORA-1555 (snapshot too old) error occurs because of keeping the cursor open for long and committing inside a loop frequently (as in the below example)?

DECLARE
cursor c1 is select empno
from emp where empno > 0;
empno_val number;
BEGIN open c1;
LOOP fetch c1 into empno_val, sal_val;
if c1%FOUND then
update emp set sal = sal_val * 1.04
where empno = empno_val;
commit;
else
exit;
end if;
END LOOP;
close c1;
END;

--
I guess not, bcoz I understand that when the cursor is opened, the query executes and the result set is moved to PGA & cursor fetch operation will be no more the dependent on the table/rollback segment to get the records.

I think it arises due to the high frequency of commits and delayed block clean out process, which makes the update statement not to get the consistent view.
--
Is my understandin correct...?

Does this process(as below steps) helps in avoiding the ORA-1555...?
1.Bulk fetching the cursor to collection
2.Close cursor immediately
3.Doing DML's looping through the collection rather that the cursor.

Any guidance's are appreciated.

Regards,
Vishwa...!

[Frank de Vries]

Hi,
ORA-1555 is an old foe :)

Your thinking is correct , when you do
commits you shouldn't get an ORA-1555.

It has nothing to do with the cursor being open. Snapshot is purely related to buffer blocks that have changed in memory (and are kept in rollback) untill they are committed.

But how many records are changed before commit occurs ?

I need to get some questions answered:

Which Oracle version are we talking about ?
Do you use automatic REDO or did you
size your rollback segs manually
if the latter,
What was the high water mark on your
v$rollstat

In the good 'old' days you could initialize
your session with
SET TRANSACTION USE ROLLBACK SEGMENT

Anyway,
if you use automatic redo it maybe worth checking on metalink to see you have not hit something rather obscure (like a bug or feature)

[Vishwanath Patil]

Hi Frank,

Thank you for quick response..!

Oracle version:
Oracle9i EE Release 9.2.0.8.0

Anyway, if we opt for undo mgmt to auto then oracle takes care of it, we need not to
worry.

I was only concerned about the possibilities ora-1555 due to keeping the cursor open for long during a DMLs.

Regards,
Vishwa!

[Volker Borowski]

Well sorry to contradict,
but a 1555 is all about a cursor being open "too long" (whatever this means in terms of micro-seconds) and concurrent commits.

The result set is not always beinig moved anywhere. Just in some cases (i.e. sorts) a copy to a temp area is done. The normal case is, that the cursor simply proceeds when fetches occur.

open cursor stores the current scn

fetch goes ahead and reads data

if fetch finds a block that has a scn being higher than the one of open cursor (means it has been modified), it goes to undo, find the required related undo block with a scn equal or next lower of the one from "open cursor" and gets data from this undo block.
-> If this is not possible, a 1555 will be thrown, no matter if classic rollback or new undo is in place.

Of course with new undo management chances are far better not to hit this pitfall, because automatic undo is able to utilize the entire UNDO space to avoid this as long as possible, while with classic rollback you'll need to deal with the resources of a single rollback segment, which might even not been related to your own transaction.

So the reason for 1555 is that the required resultset of a cursor is modified and committed (otherwise the required information can not get lost! If uncommited, it would ALWAYS be retrievable from UNDO) while the result is fetched
AND (!)
the related UNDO information for the timestamp of open cursor is no longer accessible to give you a consistent cursor.

For your problem, I'd recommend to use a
cursor for update and a single commit at the very end.

Best regards.
Volker