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

Https://blog.csdn.net/hijk139/article/details/21543127

The speed of rollback is realized by the parameter fast_start_parallel_rollback, which can be adjusted dynamically.

The parameter fast_start_parallel_rollback determines the number of parallel rollback starts. In busy systems or systems with poor IO performance, if a large number of rollback operations occur, it will significantly affect the system, which can be reduced by adjusting this parameter. The official document is defined as follows:

FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. If a system fails when there are uncommitted parallel DML or DDL transactions, then you can speed up transaction recovery during startup by using this parameter.

Values:

FALSE: Parallel rollback is disabled

LOW: Limits the maximum degree of parallelism to 2 * CPU_COUNT

HIGH: Limits the maximum degree of parallelism to 4 * CPU_COUNT

If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.

During the rollback, the progress of the rollback can be determined by the view V$FAST_START_TRANSACTIONS

SQL > select usn, state, undoblocksdone, undoblockstotal, CPUTIME, pid,xid, rcvservers from v$fast_start_transactions

USN STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME PID XID RCVSERVERS

--

454 RECOVERED 110143 110143 210 01C600210027E0D9 1

468 RECOVERED 430 430 17 01D40000001F3A36 128

USN: the undo segment corresponding to the transaction

STATE: status of the transaction. Optional values are (BE RECOVERED, RECOVERED, or RECOVERING)

UNDOBLOCKSDONE: the completed undo block in the transaction

UNDOBLOCKSTOTAL: total undo data blocks that require recovery

CPUTIME: time that has been rolled back (in seconds)

RCVSERVERS: number of parallel processes rolled back

Add, query scripts with better rollback time

SQL > select undoblockstotal "Total"

Undoblocksdone "Done"

Undoblockstotal-undoblocksdone "ToDo"

Decode (cputime

0

'unknown'

To_char (sysdate + ((undoblockstotal-undoblocksdone) /

(undoblocksdone / cputime)) / 86400)

'yyyy-mm-dd hh34:mi:ss')) "Estimated time to complete", to_char (sysdate,' yyyy-mm-dd hh34:mi:ss')

From v$fast_start_transactions

Total MB Done ToDo Estimated time to complete TO_CHAR (SYSDATE,'YYYY-MM-DDHH24:MI:SS'

-

36767 36767 0 2014-03-19 16:59:19 2014-03-19 16:59:19

7209 7209 0 2014-03-19 16:59:19 2014-03-19 16:59:19

3428 3428 0 2014-03-19 16:59:19 2014-03-19 16:59:19

34346 1604 32742 2014-03-19 17:25:31 2014-03-19 16:59:19

The following is the handling of a large number of wait for a undo record wait events

1. A user uses plsql to perform an insert operation exception, resulting in a growing tablespace, so manual kill should be rolled back and stopped. After kill, there are a large number of wait for a undo record, about 100.

2. Query the v$fast_start_transactions view. Because the fast_start_parallel_rollback parameter is set to HIGH and the number of cpu is 32, the number of parallel processes is 32 × 458.

SQL > select usn, state, undoblocksdone, undoblockstotal, CPUTIME, pid,xid, rcvservers from v$fast_start_transactions

USN STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME PID XID RCVSERVERS

--

454 RECOVERING 26922 464160 103 3744 01C600210027E0D9 128

468 RECOVERED 430 430 17 01D40000001F3A36 128

SQL > SHOW parameter ROLLBACK

NAME TYPE VALUE

-

Fast_start_parallel_rollback string HIGH

SQL > show parameter cpu

NAME TYPE VALUE

-

Cpu_count integer 32

3. Since it is estimated that there are still 103 / (26922 impulse 464160) = 30 minutes to complete, in order to reduce the impact on system performance, the related tables are truncate (the data in the business table is no longer needed)

SQL > truncate table user1.JT_t1_20140318

4During truncate, the row cache lock exception wait occurred in a short time, and then returned to normal after about tens of seconds. Can the truncat operation end the undo rollback operation?

5. In fact, in order to reduce the impact of undo, you can set fast_start_parallel_rollback, which can be modified online and take effect immediately

Alter system set fast_start_parallel_rollback= FALSE

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.