Read consistency of undo series Learning (ORA-01555 error mechanism analysis)

During Oracle reading, data is static and there is no dirty read, that is, uncommitted data will never be read. We can understand that at the beginning of Oracle's reading, we took a "photo" for his future content and fixed all the content at a time point, as the basis for subsequent reading. Oracle uses scn to implement this theory. when starting the query, a select scn will be determined, which ensures that all the scn in the transaction slot is smaller than the select scn. Well, for example, It's, so all the words I typed earlier are done before.

We already know that the undo segment is used cyclically, and Oracle only overwrites inactive transactions. If a long query needs to read the covered block, a ora-01555 error is reported. The ssolderrcnt field in v $ undostat is used to query whether error 01555 has occurred in Oracle every 10 minutes.

The following figure shows the error 01555:

The error 01555 is generated for many reasons. Here are three columns:

1) undo_retention is not large enough

2) Great undo space pressure

3) Poor SQL Performance

Next we will simulate the 01555 error. Note: avoid confusion with error 30036.

Session 1:

1. Hr @ ORCL> var I refcursor
2. Hr @ ORCL>Exec Open: IFor Select*FromT;
4. PL/SQLProcedureSuccessfully completed.
6. Hr @ ORCL> print I;
8. IDNAME
9. --------------------
10. 1
11. 2 B
13. Hr @ ORCL>Exec Open: IFor Select*FromT;
15. PL/SQLProcedureSuccessfully completed.

Session 2:

1. Sys @ ORCL>Delete FromHr. t;
3. 3RowsDeleted.
5. Sys @ ORCL>Commit;
7. CommitComplete.

Session 1:

1. Hr @ ORCL> print I;
3. IDNAME
4. --------------------
5. 1
6. 2 B

If there is data, it indicates that the query is before modification; if there is no data, it indicates that the query is after modification. However, if there is no data in the undo, an error is returned. From the query output, we can see that the query is before modification.

1 2 Next Page