Passed A&A 023

As I said, personally, if I get the SANPSHOT TO OLD ERROR, I check concurrent connections and determine if I have enough rollback segments to handle the number of transactions. One of the main reason for SNAPSHOT TO OLD, is another transaction writting over the undo header in the segment's block thereby causing data inconsistency, especially if another transaction is referenicing this via CONSISTENT GETS.

I.e. This error occurs when a long-running query fails to get rollback needed for a consistent read, becuse it has been overwirtten by another transaction.

Now, if you had more rollback segments, it could help solve this issue. Just as, resizing the extents etc.

I'm sorry guys, but call me stubborn, I'm still sticking with A & C. :)

Cheers,



[Edited by grjohnson on 09-24-2001 at 02:20 AM]

Quote:

---

Originally posted by grjohnson
As I said, personally, if I get the SANPSHOT TO OLD ERROR, I check concurrent connections and determine if I have enough rollback segments to handle the number of transactions. One of the main reason for SNAPSHOT TO OLD, is another transaction writting over the undo header in the segment's block thereby causing data inconsistency, especially if another transaction is referenicing this via CONSISTENT GETS.

I.e. This error occurs when a long-running query fails to get rollback needed for a consistent read, becuse it has been overwirtten by another transaction.

Now, if you had more rollback segments, it could help solve this issue. Just as, resizing the extents etc.

I'm sorry guys, but call me stubborn, I'm still sticking with A & C. :)

Cheers,

[Edited by grjohnson on 09-24-2001 at 02:20 AM]

---

You are right. You should have a good number of RBSs.

The point now is that A, C and E are good answers. However, only 2 were required :-) Why also E: If you commit a change after the current transaction is started, then the rollback space where the information for that transaction is stored may possibly get overwritten by subsequent transactions. It could also be eliminated entirely by a shrink due to OPTIMAL. When Oracle tries to get the information for that row and the RB transaction is no longer existent, you will get ORA-1555.

Julian,

I agree with you that A. C. and E can all be correct. This is just another case of a stupid Oracle Certifiaction question. That's why it is so easy to get confused in an examination question if you really analyze the question, expecially if you can justify more than two answers.

Thanks mate,


Cheers,

It's been quite a discussion, i learn many things from you guys. Thanks a lot for all your feedback and comments. I hope we can have more discussion thread like this (rather than starting a thread to ask for cheet-sheets!)

Thank you very much!

The Maverick

Answer to the 'Snapshot too old' question
is: A & E

Reason:
A long running query in one session that starts before a change in that data is committed by another session
will see data as it existed precommit. If oracle cannot provide a read consistent view of the data, ORA-01555
(Snapshot too old) results.

The solution to the problem is to recreate the roll back segments with larger extents or higer optimal value of extents or higher minimum number of extents. It cannot be solved by increasingthe number of rollback segments.

Hi
I too got passed perfor and tuning. paper

It's very tough i just got passed by god's grace.

Most of the questions i got is from the dumps supplied by
[email protected] and TBT papers.

thanks to all
Bye
issac

Quote:

---

Originally posted by Raminder
Answer to the 'Snapshot too old' question
is: A & E

Reason:
A long running query in one session that starts before a change in that data is committed by another session
will see data as it existed precommit. If oracle cannot provide a read consistent view of the data, ORA-01555
(Snapshot too old) results.

The solution to the problem is to recreate the roll back segments with larger extents or higer optimal value of extents or higher minimum number of extents. It cannot be solved by increasingthe number of rollback segments.

---

True but still you should have enough number of extents if you want to avoid the 1555 error.

it is A & E, if you guys have the ILT (since 99% of questions is based on ILT) you will see that it states three solutions

• A higher MINEXTENTS value, this will avoid new transactions wrap the the extents freed by the large transaction therefore the read consistency wont be lost
• Larger extent sizes, this will avoid the large transaction to extend in several extents therefore the possibility of extents being wraped by other transaction is decreased
• A higher OPTIMAL value, avoid dynamic extent allocation, in other words keep large extents or high number of extents (both of above points)

Higher RBS may and may NOT help, in a busy system it wont help that is for sure. For example you have 10 RBS and they are all active, one with the large transaction (let's call it RBS01) and the others with smaller transactions but higher number of transactions
imagine this:

1. there is a long select going and it uses the before image in RBS01
2. the large transaction commits
3. a new transaction comes in
4. Oracle assigns RBS01 to this new transaction because Oracle 's RBS allocating algorithm is that it assigns the transaction to the RBS with less active transaction
5. the new transaction overwrites the before image of previous large transaction
6. the long select lost the trace of before image, 1555 occurs

In an active/busy system this will happen because of the small transactions (and many) so more RBS does not really help

Also the question is asking of how to avoid overwritting extents in RBS since A & E are related of tuning the number of extents they should be more correct

[Edited by pando on 09-24-2001 at 06:50 PM]

I'm with you one that Julian. Sorry Reminder, but If you have two rollback segments for 20 concurrent users, I don't care what sizing your rollback segments are, you'll get the snapshot to old error. A,C,E. Increasing the number of rollback segments can illiminate this problem. AS WELL as resizing them properly.

"since 99% of questions is based on ILT", still doesn't mean it's 100% correct.

And as you said yourself, "ADDING more may or may not help". I'm not saying its the be all and end all, it's tuning. But, of course the exam is black and white, and basically tests you from the TEXT BOOK, and not real life.

For all Oracle certification exam takers, me included, you are best answering the questions as the ILT and texts suggest and not from anything you've tried in real life situations.

Cheers

[Edited by grjohnson on 09-24-2001 at 06:54 PM]

so let's look this way

adding more RBS may or may not help

but increase OPTIMAL and extent size or higher number of extents DOES help for sure

one is may and the other is sure and we have to choose 2 ;)