-
Select statement impact on Rollback segment
Does select statement make any impacts on rollback segment, so that we will get "SNAPSHOT too old" error?
Thanks
sumit
-
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.
--Christopher
-
It is not because of a long running DML and small rollback segment.
Well, I think they meant "long running query" instead of "long running DML". Little misconcept.
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.
An ounce of action is worth a ton of theory.
—Friedrich Engels
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|