Does select statement make any impacts on rollback segment, so that we will get "SNAPSHOT too old" error?
Thanks
Printable View
Does select statement make any impacts on rollback segment, so that we will get "SNAPSHOT too old" error?
Thanks
Yes, in that if you have a cursor in a long running dml operation, when Oracle no longer has enough rollback room to make a read consistant view of the database it will throw the "SNAPSHOT too old" error message. But in my experience it is usually the cursor that gets the error message not an external query. So when possible don't have long running queries.
SELECTS do not create rollback, they do cause reads of rollback to get the read consistant view.
The impact is because of a long running DML operation, when the rollback segment is small, and the DML statment is not committing. In such a case, Oracle will be unable to provide a 'read consistent' view to the user with a query, and 'snapshot too old' errors will result.
It is not because of a long running DML and small rollback segment.
SNAPSHOT TOO OLD happens becuase someone modifies & commits data, and then it is overwritten in the rollback segment by another transaction all the while someone is reading the data. when they get to the modified record, it has been changed, they go look in the rollback segment for read consistency, it have been overwritten, snapshot too old.
if you have a long running dml and small rollback segment, you will get error unable to allocate next extent.
Well, I think they meant "long running query" instead of "long running DML". Little misconcept.Quote:
It is not because of a long running DML and small rollback segment.
In a few words, SNAPSHOT TOO OLD = Long running queries + small rollback segments (or little undo space) + DML on the data being queried. Mainly SELECT + DML together, the problem may be minimized but not completely solved with bigger segments or more room on undo tablespace (unless one have infinite disk resources, however this workaround is pretty stupid).
The problem isn't the SELECT or the DML, but them together.