Shulou(Shulou.com)06/01 Report--

The carnival listened to a theme of Enmo College: "recreating ORA-01555 talking about Oracle 12c Undo data Management". Teacher Lu Xinghao introduced the concept of UNDO and the emergence of ORA-1555, as well as the new UNDO-related features of Oracle since 12c.

It describes how Oracle uses UNDO to achieve multi-version consistent reading, using OPEN CURSOR to construct reproducible cases with very little data. However, there is a slight flaw in this case, because if not careful, the result is likely to be not as expected, this is because there is an exception.

Let's first simulate the situation where UNDO constructs consistent reads. For Oracle, the default isolation level is READ COMMIT, which means that a session can only see changes that have been committed by other sessions, and uncommitted changes or changes committed after the current session query is initiated are not visible.

Again, when a cursor is opened in OPEN CURSOR,Oracle, its result set has been determined, that is, the cursor will construct a consistent query based on the SCN corresponding to OPEN CURSOR at this point in time. However, in the case of OPEN CURSOR, the corresponding SQL will not be executed, and the SQL will only be executed in the subsequent FETCH (for SQLPLUS, the PRINT command will trigger FETCH). Using this approach, you can simulate a large query. OPEN CURSOR is equivalent to the start time of the large query, which is earlier than the modification commit time of other sessions, while the time of FETCH is equal to the time that the large query read the record, which is later than the submission time of other sessions:

SQL > SET SQLP 'SQL1 >'

SQL1 > CREATE TABLE T_UNDO (ID NUMBER, NAME VARCHAR2 (30))

Table created.

SQL1 > INSERT INTO T_UNDO SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS

96920 rows created.

SQL1 > COMMIT

Commit complete.

SQL1 > CREATE INDEX IND_UNDO_ID ON T_UNDO (ID)

Index created.

SQL1 > SELECT NAME FROM T_UNDO WHERE ID = 1119

NAME

I_EXTERNAL_LOCATION1 $

SQL1 > VAR C REFCURSOR

SQL1 > EXEC OPEN: C FOR SELECT NAME FROM T_UNDO WHERE ID = 1119

PL/SQL procedure successfully completed.

A query has been constructed in the first session, and the record with an ID of 1119 is modified and submitted in session 2:

SQL > SET SQLP 'SQL2 >'

SQL2 > UPDATE T_UNDO SET NAME = 'UPDATED' WHERE ID = 1119

1 row updated.

SQL2 > COMMIT

Commit complete.

Execute the query on session 3, and you will see the result of session 2 modification submission:

SQL > SET SQLP 'SQL3 >'

SQL3 > SELECT NAME FROM T_UNDO WHERE ID = 1119

NAME

UPDATED

Going back to session 1, PRINT the CURSOR variable and check the result:

SQL1 > PRINT: C

NAME

I_EXTERNAL_LOCATION1 $

So far, all the results are expected. Oracle will use UNDO to store the front image of UPDATE. When the query finds that the data block SCN that needs to be accessed is larger than the session-initiated SCN, it needs to construct a consistent read through the previous image stored in UNDO to find the modified data that the session needs to read.

So where does the exception come from? in this example, we create an index on the ID column. What if this is not a normal index, but a primary key?

SQL1 > DROP INDEX IND_UNDO_ID

Index dropped.

SQL1 > ALTER TABLE T_UNDO ADD PRIMARY KEY (ID)

Table altered.

SQL1 > SELECT NAME FROM T_UNDO WHERE ID = 1118

NAME

EXTERNAL_LOCATION$

SQL1 > EXEC OPEN: C FOR SELECT NAME FROM T_UNDO WHERE ID = 1118

PL/SQL procedure successfully completed.

Session 2 modifies the record with an ID of 1118:

SQL2 > UPDATE T_UNDO SET NAME = 'UPDATED WITH PK' WHERE ID = 1118

1 row updated.

SQL2 > COMMIT

Commit complete.

Session 3 check to confirm the modification result:

SQL3 > SELECT NAME FROM T_UNDO WHERE ID = 1118

NAME

-

UPDATED WITH competition

Once again, go back to session 1, which is a cursor variable called print:

SQL1 > PRINT: C

NAME

UPDATED WITH competition

You can see that the exception is created, the result of the consistent read is broken, and the changes committed after the cursor is opened can be queried.

The reason for this exception comes from an implicit function _ row_cr:

After Oracle11g, the default value of this implicit parameter is changed to TRUE, which makes Oracle no longer use the default consistent read scheme for primary key-based access. Of course, the purpose of Oracle to make this change is to improve performance, and it only works for single-line access, which is very efficient in most cases, so the impact on consistency breach is not obvious. As of 18C, this parameter is still TRUE.

If you turn off this parameter:

SQL1 > ALTER SYSTEM SET "_ row_cr" = FALSE

System altered.

SQL1 > SELECT NAME FROM T_UNDO WHERE ID = 1117

NAME

I_EXTERNAL_TAB1 $

SQL1 > EXEC OPEN: C FOR SELECT NAME FROM T_UNDO WHERE ID = 1117

PL/SQL procedure successfully completed.

Session 2 is modified:

SQL2 > UPDATE T_UNDO SET NAME = 'UPDATED NO ROW CR' WHERE ID = 1117

1 row updated.

SQL2 > COMMIT

Commit complete.

Check results:

SQL3 > SELECT NAME FROM T_UNDO WHERE ID = 1117

NAME

-

UPDATED NO ROW CR

Go back to session 1 to check the results:

SQL1 > PRINT: C

NAME

I_EXTERNAL_TAB1 $

Oracle restores the default read consistency isolation level.

Although Oracle believes that this optimization is only effective for row-level access such as primary keys or unique indexes, and the possibility of data consistency damage is very small, it is recommended that this feature be turned off for industries with high consistency requirements, especially finance-related industries, to avoid the resulting consistency problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.