Select statement impact on Rollback segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Select statement impact on Rollback segment

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Jul 2002
    Posts
    5
    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

  5. #5
    Join Date
    Feb 2001
    Posts
    295
    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
  •  


Click Here to Expand Forum to Full Width