Shulou(Shulou.com)05/31 Report--

How to understand the rollback section of ORACLE, I believe that many inexperienced people do not know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

ORACLE rollback segment

Overview of rollback segment

The rollback segment is used to store the values before the data was modified (including the position and value before the data was modified). The header of the rollback segment contains information about the transaction of the rollback segment that is being used. A transaction can only use one rollback segment to store its rollback information, and a rollback segment can store rollback information for multiple transactions.

The role of the rollback section

Transaction rollback: when the transaction modifies the data in the table, the pre-modified value (that is, the previous image) of the data will be stored in the rollback segment. When the user rolls back the transaction (ROLLBACK), ORACLE will use the front image of the data in the rollback segment to restore the modified data to the original value.

Transaction recovery: when a transaction is being processed, the routine fails, the information of the rollback segment 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 one session is modifying data, other sessions will not see the uncommitted changes in that session. Moreover, when a statement is being executed, the statement will not see the uncommitted changes (statement-level read consistency) since the statement was executed. When the ORACLE executes the SELECT statement, the ORACLE ensures that any uncommitted changes prior to the current SCN are not processed by the statement according to the current system change number (SYSTEM CHANGE NUMBER-SCN). As you can imagine: when a long query is being executed, if another session changes a block of data to be queried by the query, ORACLE will use the data front image of the rollback segment to construct a read consistency view.

Type of rollback segment

The rollback segment can be divided into system rollback segment and non-system rollback segment, in which the non-system rollback segment is divided into PUBLIC rollback segment and PRIVATE rollback segment.

Rollback segment: 1 system rollback segment

2 non-system rollback segment: (1) PUBLIC rollback segment

(2) PRIVATE rollback segment

The system rollback segment is used to deal with things related to the CATALOG of the system (such as most DDL). It is located in the SYSTEM table space, and since only the SYSTEM table space can be kept available at any time, do not place the SYSTEM rollback segment in other table spaces.

Principle 1: the system rollback segment should be placed in the SYSTEM tablespace and should always remain ONLINE.

The PUBLIC rollback segment is available to all instances of the database (INSTANCE) unless it is explicitly set to OFFLINE.

PRIVATE rollback segment is private to an instance of the database. In order to use PRIVATE rollback segment, an instance should indicate all the PRIVATE rollback segments to be used in the ROLLBACK_SEGMENTS of its INITsid.ORA, or use a certain PRIVATE rollback segment by using ALTER ROLLBACK SEGMENT XXX ONLINE.

Recommendation 1: in a single instance system, it is recommended that all rollback segments be set to PUBLIC.

Recommendation 2: in multi-instance systems (such as OPS,RAC), it is recommended that the PRIVATE rollback segment of each instance be placed on a local device with faster access.

Quantity planning of rollback segment

For OLTP systems, there are a large number of small transactions, which are generally recommended:

A large number of small rollback segments; one rollback segment for every four transactions; no more than ten transactions for each rollback segment.

For batch processing, it is generally recommended that:

Fewer large rollback segments; one rollback segment per transaction.

Use of rollback segments

Allocate rollback segments: when a transaction starts, ORACLE allocates a rollback segment to the transaction and assigns the rollback segment with the least number of transactions to the transaction. A transaction can request a specified rollback segment with the following statement:

SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment

Transactions will use the EXTENTS of the rollback segment in a sequential, circular manner, when the current zone is full and moved to the next zone. Several transactions can be written in the same area of the rollback segment, but the block of each rollback segment can only contain information about one transaction.

For example (two transactions use the same rollback segment, which has four zones):

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

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

3. When the third zone is full, the transaction will be written to the fourth zone. When the transaction begins to write to a new zone, it is called WRAP.

4. When the fourth zone is full, if the first zone is idle or inactive (using all transactions in that area to complete but no active transactions), the transaction will then use the first zone.

Query for rollback segment

1 query the rollback segment of the database

Select owner,segment_id,segment_name,tablespace_name,status from dba_rollback_segs

2 View the basic information of the rollback segment of the system

Select segment_name,tablespace_name,bytes,next_extent from dba_segments where segment_type='ROLLBACK'

From the above is only to query the basic information of the rollback segment, to the current dynamic information of each rollback segment, but also to query the V$ROLLNAME and V$ROLLSTAT views. The V$ROLLNAME view only stores the number and name of each rollback segment, and the V$ROLLSTATS stores the current situation information of each rollback segment. If you want to get the information of each rollback segment, you have to query the information of two tables to get it. Such as:

Select s.usn,n.name,s.extents,s.optsize,s.hwmsize,s.status from v$rollstat s, v$rollname n where s.usn=n.usn

2 check the usage of the rollback segment and which user is using the resources of the rollback segment (when the resources are released after submission or rollback):

SELECT s.username, u.name FROM v$transaction t, v$rollstat r, v$rollname u, v$session s WHERE s.taddr = t.addr AND t.xidusn = r.usn AND r.usn = u.usn ORDER BY s.username

3 what is currently active in the rollback segment (automatically emptied after the transaction is committed or rolled back)

SELECT s.usernameret.xidusnret.ubafilret.ubablkmemt.usedroomublk FROM v$session srecovertransaction t

4 analyze the use of UNDO

SELECT TABLESPACE_NAME,STATUS,TRUNC (SUM (BLOCKS) * 8 / 1024) AS "Size M", COUNT (*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME,STATUS

5 Monitoring undo tablespace

SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT

6 query whether there is contention for rollback segments

Select * from v$waitstat

SELECT name, waits, gets, waits/gets "Ratio" FROM v$rollstat a, v$rollname b WHERE a.usn = b.usn

7 View the statistics of the rollback segment:

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

8 query the transaction fallback rate of the rollback segment

Transaction rollbacks/ (transaction rollbacks+user commits)

Select name,value from v$sysstat where name in ('user commits','transaction rollbacks')

9 the number of times extent is looping when querying the rollback segment in use, expansion, and retraction

Select usn,wraps from v$rollstat

10 query the contraction of the rollback segment

Select usn,optsize,shrinks from v$rollstat

Create a rollback segment

Syntax:

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:

Rollback segments can be specified as PRIVATE or PUBLIC at the time of creation, but cannot be modified once created.

MINEXTENTS must be greater than or equal to 2

PCTINCREASE must be 0

If OPTIMAL is to be specified, it must be greater than or equal to the initial size of the rollback segment (specified by MINEXTENTS)

Recommendations:

In general, INITIAL=NEXT

Set the OPTIMAL parameter to save space

Do not set MAXEXTENTS to UNLIMITED

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

The meaning of the parameter is the same as that of the table, but the pctincrease parameter is not allowed here, so its percentage increase is always zero. The use (execution effect) of the parameters here is very different from the parameters of the table. The data in a table is still placed in the data file corresponding to the table space after shutdown, while the rollback segment is only used when the system is running and used in insert, update, and delete, and then it can be released (for other transaction use), and all the rollback information recorded after shutdown is released, so there is the problem of how to release the expanded space, that is, the question of how much space is best (set by OPTIMAL). Optimal can avoid "snapshot too old" errors and assign an optimal to each rollback segmal to maintain a small cache value in memory, thereby improving performance.

Example:

Create tablespace rbs datafile'/ data/oradata/cts/rbs01.dbf' size 100m autoextend on next 10m maxsize 150m

Create public rollback segment rbs01 tablespace rbs storage (initial 100K next 100K minextents 10 maxextents 100 optimal 1000K)

Select segment_name,tablespace_name,status from dba_rollback_segs

However, it cannot be queried after the creation is successful, because the rollback segment is automatically managed from 9i and does not need to be created manually.

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

If you want to change back to manual management, you need to do the following:

ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE

Alter system set undo_management=MANUAL scope=spfile

Shutdown immediate

Startup

Select segment_name,owner,status from dba_rollback_segs

Change the rollback segment

When the rollback segments are established, they sometimes need to be modified. The storage parameters of the rollback segment can be modified, and an offline (OFFLINE) rollback segment can be modified to online (ONLINE). It may also be set to offline for rollback segments that are already online, for example, when we IMP or modify a large number of data, ORACLE always needs a large rollback segment. However, because the allocation of rollback section is arranged by the ORACLE system. In order to get large rollback segments in the transaction, we can only set the smaller rollback segments offline until the transaction we are dealing with is completed.

Set the rollback segment online after the instance is restarted

In addition to creating the rollback segment using the CREATE ROLLBACK SEGMENT command above, to keep the ORACLE system online after shutdown and reboot, add the name of the rollback segment to the rollback_segments parameter in the initsid.ora parameter file, such as:

Rollback_segments= (r0memr1recoi R2)

Bring the rollback segment online

When the rollback segment is created, the rollback segment is offline and cannot be used by the database. In order for the rollback segment to be utilized by the transaction, the rollback segment must be online. You can bring the rollback segment online with the following command:

ALTER ROLLBACK SEGMENT rollback_segment ONLINE

Example:

ALTER ROLLBACK SEGMENT rbs01 ONLINE

In order to make the rollback segment online automatically when the database starts, you can list the name of the rollback segment in the database parameter file. For example, add the following line to the parameter file:

ROLLBACK_SEGMENT= (rbs01,rbs02)

Select name,status,gets,waits from vs. rollname. vandalism rollstat where v$rollstat.usn=v$rollname.usn

Rollback segment expansion (EXTEND)

When all blocks of the current rollback segment are used up and the transaction needs more rollback space, the pointer of the rollback segment will be moved to the next zone. When the last zone is used up, the pointer will be moved to the front of the first zone. The rollback segment pointer moves to the next zone on the premise that there are no active transactions in the next zone, and the pointer cannot span the zone. When the next zone is in use, the transaction allocates a new zone to the rollback segment, which is called the extension of the rollback segment. The rollback segment will be extended until the number of rollback segments reaches the value of the parameter MAXEXTENTS of the rollback segment.

Limit on the number of rollback segment extensions

In older versions of ORACLE, the number of times the rollback segment was extended was limited, depending on the block size of the instance. For example:

When ORACLE block = 2k, maxextents 121,

When ORACLE block = 4k, maxextents 249

When ORACLE block = 8k, maxextents 505

When ORACLE block = 16k, maxextents 1017

When ORACLE block = 32k, maxextents 2041

Note: after ORACLE V7.3, the number of Maxextents extensions has been cancelled, you can set it large enough. It can even be set to UNLIMITED (= 249000000).

Recovery and OPTIMAL parameters of rollback segment

The OPTIMAL parameter indicates where the rollback segment shrinks when it is idle, and the OPTIMAL parameter of the rollback segment can reduce the waste of space in the rollback segment.

Set OPTIMAL parameters

Because the rollback segment is a dynamic object, it increases (expands) according to the amount of data added or deleted in use, but is released after use, and then other transactions can be used. In order to avoid the total space occupied by one rollback segment after expansion, which is not conducive to the expansion of other rollback segments, ORACLE provides an optimal parameter to control the rollback segment. The meaning of this parameter is that during the expansion process, when the transaction is completed (after sending commit,rollback), the size of the rollback segment will be shrunk according to the optimal value.

It is important to note that the setting of the optimal parameter cannot be smaller than the allocated space. In other words, the optimal setting should be larger than the original extension. For example:

General optimal = minextents * initial +

(minextents + n) * next

Where minextents > = 1; n > = 1 is required

For example:

CREATE ROLLBACK SEGMENT rbs21 tablespace rbs

STORAGE (initial 10m next 2m minextents 2 optimal 16m)

Here, optimal can only be filled with 14m, 16m or 18m, but not 12m, because the minimum expansion is 2 times, that is, the initial allocation of rbs21 rollback segment is 10m + 2m = 12m. According to the principle that optimal should be greater than the initial value, optimal starts at least 14m.

Example:

Initial allocation of systen rollback segment initial = 409600

Next assignment next = 57344

Best extended value optimal= null

In this case, we can change the value of the next allocation to 1MB (= 1024000 bytes), and the best extension value is: optimal = initial + 2 * next = 409600 + 2 * 1024000 = 2457600. So the command to modify SYSTEM rollback is:

SQL > alter rollback segment system storage (next 1m optimal 2457600)

Note: optimal indicates that in transaction processing, the rollback segment is constantly expanded to meet the requirements of the transaction due to the increase, deletion and modification of the amount of data, but the rollback segment can be reduced to an optimal range after the transaction is completed. This is controlled by the OPTIMAL parameters of the rollback segment. General optimal = initial + n * next, and n > 1.

Modify the storage parameters of the rollback segment

You can use the ALTER ROLLBACK SEGMENT command to modify the storage parameters of the rollback segment, including OPTIMAL,MAXEXTENTS.

Syntax:

ALTER ROLLBACK SEGMENT rollback_segment

[STORAGE ([NEXT Integer [K | M]

[MINEXTENTS integer]

[MAXEXTENTS {integer | UNLIMITED}]

[OPTIMAL {integer [K | M] | NULL}])]

Example:

ALTER ROLLBACK SEGMENT rbs01 STORAGE (MAXEXTENTS 1000)

Take back the space of the rolling section

If the OPTIMAL parameter of the rollback segment is specified, ORACLE automatically takes back the rollback segment to the location specified by OPTIMAL. The user can also manually reclaim the space of the rolling segment.

Syntax:

ALTER ROLLBACK SEGMENT rollback_segment SHRINK [TO integer [K | M]]

Description:

If you do not specify a value for TO integer, ORACLE will try to recycle it to the location of OPTIMAL.

Example:

ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 2M

Take the rollback segment offline

Select name,status,gets,waits from vs. rollname. vandalism rollstat where v$rollstat.usn=v$rollname.usn

The rollback segment will be offline in order to achieve the following two purposes:

1. Prevent new transactions from using the rollback segment

two。 The rollback segment must be deleted.

Syntax:

ALTER ROLLBACK SEGMENT rollback_segment OFFLINE

Example:

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 running the command. At the end of the transaction, the state is changed to OFFLINE, and the status of the rollback segment can be queried through V$ROLLSTAT.

Observe the growth of the rollback segment

Although the rollback segment has been expanded during use and automatically reduced in accordance with the requirements of OPTIMAL after use, it will be recorded in the dynamic dictionary V$ROLLSTAT. The command is as follows:

Select n.name, optsize, hwmsize from v$ROLLNAME n, V$ROLLSTAT s where n.usn=s.usn

Optsize is the optimal size value, and hwmsize = the highest value in the Hight water mark of rollback segment size rollback segment extension (water level). If optsize is empty, hwmsize is the current highest value.

Add ORACLE system rbs tablespace data file

Alter tablespace rbs add datafile'/ disk1/ORACLE/oradata/ora8/rbs02.dbf' size 120m

Specify the use of rollback segments in a transaction

After the rollback segment is established, the use of the rollback segment is arranged by the system, so it is possible that a larger rollback segment is actually needed in the processing, and the system always allocates a smaller rollback, which leads to processing failure. To avoid such a failure. It is necessary to use the SET TRANSACTION USE ROLLBACK SEGMENT command in the command or in the program.

It should be noted that when using the setup command in the program, the SET TRANSACTION statement should be used at the beginning of the program, and the SET TRANSACTION statement should be reused after each COMMIT or ROLLBACK statement. If the SET TRANSACTION statement is no longer used after the COMMIT or ROLLBACK statement, the system will release the original assigned rollback segment and randomly assign a new rollback segment. Examples of using rollback segments under SQL and PL/SQL are given below.

When performing a large transaction, oracle sometimes reports the following error:

ORA-01555:snapshot too old (rollback segment too small)

This indicates that the rollback segment randomly assigned by oracle to this transaction is too small, so you can specify a rollback segment large enough to ensure the successful execution of the transaction.

Set transaction use rollback segment roll_abc

Delete from table_name where...

Commit

The rollback segment roll_abc is assigned to the delete transaction, and the commit command cancels the rollback segment assignment after the transaction ends.

1. Use rollback segment under SQL >:

Rollback segments are used for any large amount of INSERT, UPDATE, and DELETE under SQLPLUS. If you want the operation to be successful, you should specify a large rollback segment before the operation. Such as:

Commit

Set transaction use rollback segment r1

Delete from...

Commit

Set transaction use rollback segment r1

Example: create a large rollback segment for large transactions:

Create rollback segment interest tablespace interest_tabspace

Storage (initial 50m next 10m optimal 80m pct_increase 0)

Determine the number of rollback segments

The number of rollback segments directly affects the performance of the system. If the number of rollback segments is not enough, there will be a waiting phenomenon when multiple users add or delete them at the same time.

To determine whether to increase the number of rollback segments, you first need to query two dynamic views, namely, Vendor ROLLSTATD Vendor WAITSTAT. Such as:

SQL > select * from v$waitstat where class='undo header'

CLASS COUNT TIME

Undo header 0 0

SQL > select usn,extents,waits from v$rollstat

USN EXTENTS WAITS

0 5 0

1 8 0

2 8 0

3 8 0

4 8 0

5 8 0

6 8 0

7 8 0

If there is a number of waits greater than 0, you need to increase the number of rollback segments. Generally, the number of rollback segments is mainly determined by the type of application system. For example, the general historical file system, because its main processing is query. For such applications with relatively few additions, deletions and changes, fewer rollback segments can be established. And want to banking, securities and other applications. You need a lot of rollback segments. So how much does it take to be relative? The following answers are general:

In the application systems with high concurrency requirements, multiple transaction are competing for the fallback segment at the same time. If transaction is the number of transactions; there are:

N = transaction/transactions_per_rollback_segment

Where:

N = number of fallback segments

Transaction is the ORACLE system parameter, the maximum number of transaction allowed for concurrent processing in the system.

Transactions_per_rollback_segment is the ORACLE parameter, and the maximum number of transaction that can be written simultaneously per fallback segment.

In addition, if it is found from the v$waitstat dynamic view that the number of waits for the fallback header since the database was started is high, more rollback segments should be established.

Create a universal rollback segment

Regardless of any type of application, it is recommended to re-establish a new rollback segment. In addition to the establishment of a special rollback section described above, you need to establish the initial value, the next increase and the appropriate value of the optimal value. It is generally recommended that the initial value should be above 5MB, the next growth is between 2MB and 5MB, and the best value is between 20m and 30MB.

Delete the existing rollback segment R01

When the rollback segment is no longer needed or is to be rebuilt to change the INITIAL,NEXT or MINEXTENTS parameters, it can be deleted. To delete a rollback segment, do not take the rollback segment offline.

Syntax:

DROP ROLLBACK SEGMENT rollback_segment

Example:

DROP ROLLBACK SEGMENT rbs01

Query the information of the rollback segment

Data dictionary used: DBA_ROLLBACK_SEGS

Alter rollback segment r01 offline

Drop rollback segment r01

Estimation of usage of rollback segment

How to ensure that there are enough rollback segments to meet the simultaneous use of multiple concurrent transaction, but also consider that there should be a large enough rollback segment to meet the needs of special transaction (such as long-runing transaction). This is how to consider the number and size of rollback segments. In addition to system rollback segments, multiple rollback segments are usually created, and shorter transaction is usually suitable for using smaller rollback segments, which will enable the system to have better performance (because a large amount of rollback information can be cached in sga. Thus reducing the Istroke O to the hard disk. A large transaction needs to use a larger rollback segment, because a large amount of rollback information can be stored in the pre-allocated extent to avoid dynamic allocation of space; at the same time, it also prevents ora-01562 errors in the database when the space of the rollback segment is exhausted during transaction operation.

1. Roll back information

The amount of rollback information stored in the rollback segment depends on the type of transaction (insert, update, delete) and the amount of data actually processed. In general, the rollback data generated by inserting a record into the table by insert is less than delele deleting a record from the table. Because the record produced by rollback insert only needs to be deleted, while rollback of a deleted record requires reinsertion of the record, the former stores only rowid in the rollback segment, while the latter stores all the information that restores the record.

2. Estimation of the amount of data rolled back

So far, ORACLE cannot provide a good calculation of the amount of data in the rollback segment. As a dba, it can be estimated by the following methods:

1) create a smaller test table (data from the actual table EMP)

Create table emp1 as select * from emp where deptno select sum (writes) "begin" from sys.v$rollstat

SQL > update emp1 set deptno=1000 where deptno select sum (writes) "end" from sys.v$rollstat

Information content of small table = end-begin = test

Rollback information of the actual transaction = test * (emp_row / emp1_row) * 1.05

Because all the rollback information of a transaction can be written to multiple extent, but the number of extent per rollback segment is limited. So be careful when setting the initial and next parameters of storage. Generally, it is ideal to keep the extent in the rollback segment between 10 and 20. In order to release the space part after the extent, the optimal parameter is provided in the STORAGE to control the size of the rollback segment. When the rollback segment needs to allocate more space than the optimal value, RDBMS will check the size of the rollback segment. Once there is no working transaction in the additional allocated extent and the rollback segment header is pointing to the extent, the system will release the size of the extent rollback segment to the optimal specified value.

Problems in rollback section and their solutions

(1) there is not enough space for the rollback segment required by the transaction, which shows that the table space is full (ORA-01560 error), and the rollback segment is extended to the value of the parameter MAXEXTENTS (ORA-01628).

Solution:

a. Expand the table space of the rollback segment

b. Set larger MAXEXTENTS parameters

c. Set OPTIMAL parameters for rollback segment

d. Recreate the rollback segment with larger EXTENT parameters

Add files to the rollback segment tablespace or make existing files larger; increase the value of MAXEXTENTS.

ORA-01562: failed to extend rollback segment number 12

ORA-01628: max # extentsreached for rollback segment RBS12

Expand the tablespace

Add a data file to the table space of the rollback segment, and set the maxextents value of the large rollback segment apprbs to infinite:

Alter tablespace rbs add datafile'/ opt/oracle/db02/oradata/ORCL/rbs02.dbf' size 8192 m autoextend on next 10m maxsize unlimited

Enlarge parameter

ALTER ROLLBACK SEGMENT rbs01 STORAGE (MAXEXTENTS 1000)

You can replace it with the following statement (batch submission frees up space for fallback segments):

one

Create table tt (id number,sal number,age number)

two

Declare

Begin

For i in 1..10000 loop

Insert into tt values (iMagazine, iMagnum, 10, and 100)

End loop

End

Select * from tt order by id

Delete all data in table tt whose id is not equal to 10

three

Begin

Loop

Delete from tt where id! = 10 and rownum

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.