The classic problem with Oracle database snapshot too old what caused it

The classic problem with Oracle database snapshot too old what caused it

Oracle Classic Error Solver: ORA-1555 error (Snapshot too old)-...

The book says it's because the rollback image need for read consistency have prbably been overwriteten by an active transaction. I am surprised if a transaction occupies some rollback segments until it commits, the rollback space should be locked, how can it be overwritten by other transaction?

--------------------------

Rollback segment Too small

--------------------------

Snapshot too old means you commit, before the mirror is overwritten, if there is a query that requires access to the pre-image to build a consistent read, it will cause ORA-01555 error

-----------------------------

Increase your undo segment initial extents value, minimize warp, of course, undo file is big enough:)-------------------------

A. Too little/too small to lead to this mistake, you can create more rollback sections, set larger extent for the rollback section and larger minextents B. Because the rollback segment was broken, causing the task to be changed before the content (Read-consistent snapshot). This mistake is also produced by putting the rollback section. The broken rollback section can be offline and removed for reconstruction. C. When a cursor is opened and then a loop is executed for fetch, UPDATE, COMMIT, if the updated table is the same table as the fetch, it is likely to happen. Solution: A. Use a large rollback segment, B. Reduce the submission frequency C. Create an hour table that stores an inquiry column for the table you want to update (such as the key and related terms column), from the time table fetch, update the original table. D. Inappropriate optimal parameters: too small a optimal will cause the rollback segment to be shrink quickly, causing the previous content to be lost when you continue to read the operation access. Carefully design optimal parameters, do not let the rollback section of the Extend/shrink can help solve the problem. E. DB BLOCK Buffer is too small: if you read consistency in the previous content in the buffer area, then you do not have to go back to the rolling section. And if the buffer zone is too small, the likelihood of the previous version's content in the cache becomes smaller, and the need for the frequency of the access back to the rolling section to obtain the previous content, which will greatly increase the likelihood of this mistake.

----------------------------

Assuming your EMP table is large, you run it at 18:00.

SELECT * from EMP;

The output of this statement should only depend on the data state of the EMP table at 18:00, but in fact, because the EMP table is large, you may have to run the statement for 10 minutes before you see the output

At 18:01, someone made a update,update on the EMP table and did a straight-up table, so how do you see the previous data in the select of the 18:00 run? Oracle uses the method of querying the pre-mapping, that is, the data before the update to the rollback segment to save, so that your select in the query to that block, will jump to find the front, so that your select can run correctly.

However, this can happen, that is, the person who did the update commits, so the previous image may be accessed by your select before being overwritten, if this happens, snapshot too old error occurred--------------------- ---

This also shows that your SQL execution is too slow, need to adjust, in 9i there is a undo_retention parameter, assuming that the period is specified 1 hours, then more than 1 hours of SQL is prone to snapshot too old error. -----------------------------

----------------------------

This problem does not occur with MySQL

The classic problem with Oracle database snapshot too old what caused it