Oracle rollback segment concept, usage and planning and problem solving

oracle| Concept | planning | solving | Problem ROLLBACK segment Management has been a problem in Oracle database management, this paper introduces the concept, usage and planning of Oracle rollback segment and solution of problems through examples.

Rollback Segment Overview
The rollback segment is used to hold the value before the data modification, including the position and value before the data was modified. The head of the rollback segment contains information about the rollback segment transaction that is in use. A transaction can only use a rollback segment to hold its rollback information, and a rollback segment may hold multiple transaction rollback information.

The role of the rollback segment
Transaction rollback: When the transaction modifies the data in the table, the value before the data modification (that is, the front image) is stored in the rollback segment, and when the user rolls back the transaction (ROLLBACK), Oracle will use the data before the rollback segment to restore the modified data to its original value.

Transaction recovery: When the transaction is being processed, the routine fails and the rollback segment information is saved in the Redo log file, and Oracle will use the rollback to recover the uncommitted data the next time the database is opened.

Read consistency: When a session is modifying data, other sessions will not see the uncommitted changes to the session. Also, when a statement is executing, the statement does not see uncommitted modifications (statement-level read consistency) that have been executed since the statement started. When Oracle executes a SELECT statement, Oracle follows the current system change NUMBER-SCN to ensure that any uncommitted changes prior to the current SCN are not processed by the statement. As you can imagine: When a long-running query is executing, if another session changes a block of data to be queried by the query, Oracle constructs a read-consistent view with the data-fore image of the rollback segment.

Read consistency at transaction level
Oracle generally provides read consistency at the SQL statement level (SQL STATEMENT levels), and you can use the following statement to implement transactional-level read consistency.

SET TRANSACTION READ only;

Or:

SET tannsaction SERIALIZABLE;

All two of these statements provide read consistency after the transaction starts. It should be noted that using the second statement will have an impact on the concurrency and performance of the database.

Type of rollback segment
System rollback segment: When the database is created, a system rollback segment is automatically created, which is used only to hold the front image of objects in the system tablespace.

Non-system rollback segment: A database that has more than one table space should have at least one non-system rollback segment for storing data-front images of objects in a system-table space. The non-system rollback segment is divided into a private rollback segment and a public rollback segment, and the private rollback segment should be listed in the rollback segments parameter of the parameter file so that the routine is automatically online when it is started. Public rollback segments typically appear in OPS (Oracle parallel servers) and are automatically online when the routine is started.

defered rollback segment: The rollback segment is automatically created by the system when the tablespace is offline (OFFLINE) and automatically deleted by the system when the tablespace is online again, which is used to store rollback information generated when the tablespace is offline.

Use of rollback segment
Assign a rollback segment: When the transaction begins, Oracle assigns a rollback segment to the transaction and assigns the rollback segment with the least transaction to the transaction. A transaction can request a specified rollback segment with the following statement:

SET transtraction use ROLLBACK SEGMENT rollback_segment

Transactions use the area of the rollback segment (extents) in a sequential, circular manner, and move to the next area when the current area is full. Several transactions can be written in the same area of the rollback segment, but the block of each rollback segment can only contain information for one transaction.

For example (two transactions use the same rollback segment, and the rollback segment has four extents):

1. Transactions are in progress, and they are using the third area of the rollback segment;

2. When two transactions generate more rollback information, they will continue to use the third zone;

3, when the third area full, the transaction will be written to the fourth area, when the transaction begins to write to a new area, called Flip (WRAP);

4. When the fourth area is full, if the first zone is idle or inactive (all transactions using the zone are completed without active transactions), the transaction will then use the first zone.

Expansion of the rollback segment (EXTEND)
When all the blocks of the current rollback segment are exhausted and the transaction requires more rollback space, the pointer to the rollback segment moves to the next area. When the last area is run out, the pointer moves to the front of the first area. The rollback segment pointer moves to the next area if the next zone has no active transactions, and the pointer cannot cross the zone. When the next zone is in use, the transaction assigns a new zone to the rollback segment, which is called the extension of the rollback segment. The rollback segment extends to the number of the rollback segment area until the parameter maxextents of the rollback segment reaches the value.

Recovery and optimal parameters for rollback segments
The optimal parameter indicates where the rollback segment is shrunk to when it is idle, indicating that the optimal parameter of the rollback segment can reduce the waste of the rollback segment space.
Create a rollback segment
Grammar:

CREATE [public] ROLLBACK SEGMENT rollback_segment

[Tablespace tablespace]

[STORAGE (][initial integer][k| M]] [NEXT integer][k| M]]

[Minextents integer]

[Maxtents {integer| UNLIMITED}]

[OPTIMAL {integer][k| m]| NULL}])

Note:

The rollback segment can be specified private or public when it is created and cannot be modified once it is created.

Minextents must be greater than or equal to 2

The pctincrease must be 0.

Optimal if you want to specify, you must be greater than or equal to the initial size of the rollback segment (specified by minextents)

Suggestions:

Under normal circumstances, Initial=next

Set the optimal parameters to save space use

Do not set maxextents to unlimited

The rollback segment should be created within a specific rollback segment tablespace

Cases:

CREATE ROLLBACK SEGMENT rbs01

Tablespace RBS

STORAGE (INITIAL 100K NEXT 100K minextents 10

Maxextents OPTIMAL 1000K);

Make the rollback segment online
When the rollback segment is created, the rollback segment is offline and cannot be used by the database, and the rollback segment must be online in order for the rollback segment to be exploited by the transaction. You can use the following command to make the rollback segment online:

ALTER ROLLBACK SEGMENT rollback_segment ONLINE;

Cases:

ALTER ROLLBACK SEGMENT rbs01 ONLINE;

To make the rollback segment automatically online when the database is started, you can list the name of the rollback segment in the database's parameter file. For example, add the following line to the parameter file:

Rollback_segment= (RBS01,RBS02)

Modifying storage parameters for a rollback segment
You can use the Alter ROLLBACK segment command to modify the storage parameters (including optimal,maxextents) of the rollback segment.

Grammar:

ALTER ROLLBACK SEGMENT rollback_segment

[STORAGE (][next integer][k| M]]

[Minextents integer]

[Maxextents {integer| UNLIMITED}]

[OPTIMAL {integer][k| m]| NULL}])

Cases:

ALTER ROLLBACK SEGMENT rbs01 STORAGE (maxextents 1000);

Reclaim space for a rollback segment
If the optimal parameter of the rollback segment is specified, Oracle automatically reclaims the roll segment to the optimal specified location. The user can also manually recycle the space of the rollback segment.

Grammar:

ALTER ROLLBACK SEGMENT rollback_segment SHRINK [to Integer][k| M]];

Description

If the value of to integer is not indicated, Oracle will attempt to reclaim the location of the optimal.

Cases:

ALTER ROLLBACK SEGMENT rbs01 SHRINK to 2M;

To get the rollback segment offline
The rollback segment will be offline for the following two purposes:

1. Prevent new transactions from using the rollback segment;

2. The rollback segment must be deleted.

Grammar:

ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;

Cases:

ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

Description

If a transaction is using the rollback segment, the status of the rollback segment will be pending OFFLINE after the command is run. After the transaction ends, the status is changed to offline, and the status of the segment can be rolled back by V$rollstat query.

Delete a rollback segment
You can delete a rollback segment when it is no longer needed or to be rebuilt to change the Initial,next or minextents parameters. To delete a rollback segment, do not allow the rollback segment to be offline.

Grammar:

DROP ROLLBACK SEGMENT rollback_segment;

Cases:

DROP ROLLBACK SEGMENT rbs01;

Querying for information about rollback segments
Data dictionary used: Dba_rollback_segs

Information you can query: the identity of the rollback segment (segment_id), the name (segment_name), the table space (tablespace_name), type (OWNER), status (status).

Cases:

Sql>select segment_name,tablespace_name,owner,status from Dba_rollback_segs;

Statistics for rolling back segments
Data dictionary: V$rollname,v$rollstat

Cases:

Sql>select N.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status

From V$rollname N,v$rollstat s

WHERE N.usn=s.usn;

The current active transaction of the rollback segment
Data dictionary: v$session,v$transaction

Cases:

Sql>select s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk

From V$session s,v$transaction t

WHERE s.saddr=t.ses_addr;

USERNAME Xidusn Ubafil ubablk used_ublk

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

SYSTEM 2 2 7 1

SCOTT 1 2 163 1

2 rows selected.

Planning for the number of rollback segments
For OLTP systems, there is a large number of small transactions, generally recommended:

A large number of small rollback segments, one rollback segment per four transaction, and no more than 10 transactions per rollback segment.

For batch processing, general recommendations:

Fewer large rollback segments, one rollback segment per transaction.

Problems and solutions of rollback segment
Problem one: The transaction requirements of the rollback segment space is not enough, the performance of the table space with full (ORA-01560 error), rollback segment extension reached the value of the parameter maxextents (ORA-01628).

Workaround: Add a file to the rollback segment tablespace or make the existing file larger, and increase the value of the maxextents.

Problem Two: Read consistency error (ORA-01555 SNAPSHOT TOO old)

Workaround: Increase the value of the minextents, increase the size of the zone, and set a high optimal value.