ORA-1555 classic errors

Symptom:

The application's night dimension starts to run at, but for hang reasons (it is assumed that a piece of data processed by the night dimension is the same as that processed by the current application, wait for the same resource lock until the next day. However, the application logs are as follows:

Snapshot too old: rollback segment number 29 with name "_ SYSSMU29 $" too small

Cause analysis:

Due to hang, the DELETE statement of the night dimension remains in the waiting state (more than one day) until the resource lock is released. However, because the time before the image is stored in the UNDO instance exceeds the time set by the UNDO_RETENTION parameter, and this is a high concurrency system, soon may be overwritten by the application session records in the UNDO, resulting in the failure to find records in the UNDO to generate consistent read, so the error ORA-1555, this execution failed.

Although the analysis may be the cause, the statement that makes the system's night dimension statement hang has not been found yet. Further analysis is required...

Extended:

But from this error phenomenon can be exposed to the classic ORA-1555 error number, especially in production, is also a rare situation, especially in the current UNDO is basically using Oracle automatic management, when the disk space allocation is large.

The error with this ORA-1555 is a classic of Oracle rollback segment errors. UNDO is used to record DML operation data before the image, ORA-1555 errors are simply summarized in one sentence, I think is when the DML statement needs to use UNDO record data to find the front image, this record has been overwritten, and therefore the UNDO record cannot be used to complete consistent read. Of course, Oracle also has UNDO_RETENTION and other parameters to avoid this situation, but it may still happen for a variety of reasons and there are also a variety of solutions. The following is a brief description.

First of all, Tom, hellodba and other people have a classic introduction to ORA-1555. In terms of the cause, there are two possible causes of ORA-1555 errors, one being consistent read, and the other being delayed block (LOCK) clearing.

ORA-1555-related parameters:

1. UNDO_RETENTION.

The UNDO_RETENTION parameter sets the forced retention time of the data submitted or rolled back in the rollback segment. However, it does not mean that the data in the rollback segment is cleared if the rollback segment expires, it waits for the rollback data generated by the subsequent transactions to overwrite the previous data.

2. for Oracle 9i and later versions, there are two UNDO management methods, which are specified by the UNDO_MANAGEMENT parameter. The difference between manual UNDO management and automatic UNDO management is that manual management will be bypassed, the UNDO space will be reused as much as possible. Oracle will expand the UNDO segment instead of bypassing it to ensure the time requirement of UNDO_RETENTION. So from this we can see that in order to avoid ORA-1555, using the automatic management of UNDO method may reduce the probability to a certain extent.

Concepts:

1. consistent read (from hellodba's blog: "http://www.hellodba.com/reader.php? ID = 170 & lang = CN ")

Oracle performs consistent read through rollback segments, which avoids dirty read and greatly reduces system congestion and deadlocks. When Oracle updates Data blocks (the smallest storage unit in Data Block Oracle), this update action is recorded in two locations. One is Redo Segment, which is used for database recovery (Recover. One is the UNDO Segment, and the Rollback Segment is used for transaction Rollback (We only care about the Rollback Segment ). In the data block header, check whether the data has been modified. When a statement reads data quickly, if it finds that the data block is modified during the reading process (that is, it is not modified when the read operation is started ), instead of reading data directly from data blocks, the data is read from the corresponding rollback segment entries. This ensures that the final result should be the snapshot at the beginning of the read operation, without being affected by other transactions during the read. This is consistent reading of Oracle, also known as Multi-Versioning ).

2. Delay block clearing (from hellodba's blog: "http://www.hellodba.com/reader.php? ID = 170 & lang = CN ")

When Oracle updates data blocks, this update action is recorded in the UNDO Segment. A Cleanout SCN is generated. In the rollback segment, the corresponding Transaction ID and corresponding data record image are generated. And generates a lock mark on the corresponding data record. Before a Transaction is committed, the Cleanout SCN (Csc), Undo Block Address (Uba), and Transaction ID (Xid) are recorded in the header of the data Block ); set the lock flag in the corresponding Interested Transaction List (Itl) to record the number of locks generated by this Transaction in this data block; at the same time, the upstream lock flag is applied to the modified data records and mapped to the corresponding Itl. When the transaction is committed, it does not clear all lock flags one by one. Instead, it marks the corresponding Itl and tells the subsequent transactions that access the data block. The corresponding transactions have been committed. This is called Fast Commit ). The transactions that access the data block will first check the lock mark and the corresponding transaction status. If the previous transaction is not committed and the data records to be accessed are locked, otherwise, the lock flag is cleared, and the lock flag is submitted. This will delay block clearing.

If a large transaction comes into contact with a large number of blocks and reaches more than 10% of the buffer cache, the blocks to be cleared will appear and will not be cleared by the COMMIT operation, that is, not fast commit, no other transaction DML comes into contact with these blocks, but a ORA-1555 error may occur when you SELECT a table.

In addition, to see a lot of online verification of ORA-1555 error experiments, are tested using the SELECT statement, but it should be the "transaction" or "query" statement needs to UNDO data, this error may be reported when the record is overwritten, so the night dimension error introduced at the beginning is the ORA-1555 reported by the DELETE statement, in addition, this night dimension is special because it contains several (22) DELETE statements in a transaction, that is, only one COMMIT after all the statements are executed, this increases the complexity of transactions. If one of the statements is waiting, other statements cannot be submitted or can only be waited. Although this is determined by the business, this situation should be avoided, of course this is another topic.

Instance: ORA-1555 caused by too small UNDO Space.

Create an UNDO space of 2 MB, which cannot be expanded automatically.

SQL> create undo tablespace undo_small datafile '/opt/oracle/oradata/bisal/undo_small.dbf' size 2 m autoextend off;

Set it to the system UNDO space.
SQL> alter system set undo_tablespace = undo_small;
System altered.

Create a test table. (Note: Here dbms_random.random is used to mess up the rows so that they do not think there is a certain order and thus get a random distribution, because the CTAS method is used to create a table, rows are placed in blocks according to the query order .)
SQL> create table t as select * from all_objects order by dbms_random.random;
Table created.

Create a primary key.
SQL> alter table t add constraint t_undo_pk primary key (object_id );
Table altered.

Collect the statistical information of the table.
SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );
PL/SQL procedure successfully completed.

Update all data in the table repeatedly.

SQL> begin
For x in (select rowid rid from t)
Loop
Update t set object_name = lower (object_name) where rowid = x. rid;
Commit;
End loop;
End;
/
PL/SQL procedure successfully completed.

When the preceding statement is executed, create a query statement. Here DBMS_LOCK.SLEEP (0.01) is used to simulate that the query time is 0.01 seconds at a time, because it is randomly inserted into the table, therefore, it is equivalent to randomly querying the blocks in the table. This query statement may fail after several seconds.
Declare
Cursor c is
Select/* + first_rows */object_name
From t
Order by object_id;

Rochelle object_name t. object_name % type;
Rochelle rowcnt number: = 0;
Begin
Open c;
Loop
Fetch c into l_object_name;
Exit when c % notfound;
Dbms_lock.sleep (0.01 );
Rochelle rowcnt: = maid + 1;
End loop;
Close c;
Exception
When others then
Dbms_output.put_line ('rows fetched = '| l_rowcnt );
Raise;
End;
/
(Note: An error is reported.

Rochelle rowcnt number: = 0;
*
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00103: Encountered the symbol "L_ROWCNT" when expecting one of
Following:
: = (; Not null range default character
The symbol ";" was substituted for "L_ROWCNT" to continue.
It may be l_object_name t. object_name % type with less semicolons)

The error is:

Declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 11 with name "_ SYSSMU11 $" too small
ORA-06512: at line 21

Summary:
The error is reported because the SELECT statement reads data according to the object_id INDEX (index full scan). Because the previous data is inserted randomly, therefore, a random read operation is performed on the entire table. In this case, the data read by the SELECT statement may be in different blocks. In this case, the data is updated and submitted to indicate that the record in the UNDO statement can be overwritten, since the UNDO space is small, the possibility that the UNDO block read by the SELECT statement will be overwritten by the UPDATE, commit, and so the error of the ORA-1555 appears.

To solve this problem, we can set the UNDO to be extended so that Oracle can automatically manage UNDO and expand the UNDO capacity to meet the UNDO_RETENTION time requirements, it also ensures that the block that can be read by the SELECT statement will not be overwritten by the UPDATE commit.
SQL> column file_name new_val F
SQL> select file_name from dba_data_files where tablespace_name = 'undo _ small ';
FILE_NAME
--------------------------------------------------------------------------------
/Opt/oracle/oradata/bisal/undo_small.dbf

SQL> Alter database datafile '& F' autoextend on
2 next 1 m
3 maxsize: 2048 m;
Old 1: Alter database datafile '& F' autoextend on
New 1: Alter database datafile '/opt/oracle/oradata/bisal/undo_small.dbf' autoextend on
Database altered.

SQL> select bytes/1024/1024 from dba_data_files where tablespace_name = 'undo _ small ';
BYTES/1024/1024
---------------
4

Then execute the UPDATE and SELECT statements to complete the execution.