Parallel recovery of large oracle transactions leads to database performance degradation-high cpu usage

Parallel recovery of large oracle transactions leads to database performance degradation-high cpu usage 

The rollback of a large transaction has a very high cost, not only locking the required resources

And the CPU and IO consumption, especially IO, will be extremely intensive. At this time, we hope to reduce the number of rollback results.

. It is impossible to stop. To maintain Database Consistency, rollback must be completed, so only

Can reduce the impact. The fast_start_parallel_rollback parameter can be used for this swap. The default value is

If it is low, a parallel process with 2 times the number of CPUs is started for rollback. There are two more options: high and false.

Value, high indicates that the number of parallel processes with 4 times the CPU is rolled back. This parameter can be set dynamically,

Dynamic settings may not interrupt parallel recovery. You can set restart as the best method.

Fast_start_parallel_rollback = false and restart the database.
After the database is restarted, no TRANSACTION information exists in the v $ TRANSACTION View,
However, the V $ fast_start_transactions view can find the number of rollback results.

In a large database, the failure cost of a large operation is relatively high. In severe cases, it may even cause a number

The database is suspended. It is necessary to check whether a transaction is necessary, especially before KILL a large transaction.

We also need to know how much rollback has been performed. V $ transaction, v $ session associated

To the transaction size. Select t. used_ublk from v $ transaction t v $ session s

Where t. ses_addr = s. saddr and s. sid = & sid
After the transaction fails or the session is killed, You can continuously monitor the results of the statement to estimate the rollback progress.

If we observe that used_ublk is almost immobile or the rollback is very slow, you can determine whether the following causes parallel recovery.

Recovery (parallel recovery sometimes causes database recovery to be suspended ). In the case of parallel recovery, Smon

Will capture the TX lock, and some PS lock PX processes should occupy a large amount of CPU resources. V

The two views $ fast_start_servers and v $ fast_start_transaction indicate whether and

Restore rows. If you find that parallel recovery is slow, you can try to turn off parallel recovery to see if it can speed up

Rollback. Alter system set fast_start_parallel_rollback = false

Switch off parallel rollback to serialization. Similarly, if it is a serialized ROLLBACK with CPU resources

If this is acceptable, you can use parallel recovery to speed up rollback. If the overall system is displayed

Can shut down the database and use parallel rollback. Parallel recovery can be performed at $ session_wait.

See a lot of PX process waiting, Smon process as a coordination process while waiting for the completion of the PX process. Data

After the database is closed or the shadow process is killed, transaction messages cannot be found in v $ transaction.

. At this time, there is transaction information in v $ fast_start_transaction. For versions earlier than 8i,

Only the uet $ and fet $ tables can be monitored continuously to check whether rollback is performed. At the same time, Smon should always capture

Stlock.

When the database performance declines, the first thing to do is:
1. First look for OS information:
Top --- view which process occupies the most system resources --- here we can also see that the process occupies

Most resources are used.
Mem --- usage, whether there are a large number of page breaks.
System --- I/o is normal.

2. AWR --- "top 5 user event

Here we will make a simple analysis on the case of parallel recovery of large transactions.

1. View wait for a undo record in the awr report

---- View historical information
2. select event, count (*) from dba_hist_active_sess_history
Where sample_time <to_timestamp

('20140901', 'yyyymmddhh24mis ')
And sample_time> to_timestamp ('20140901', 'yyyymmddhh24mis ')
And instance_number = 1
Group by event
Order by 2;

--- View the processes that have wait for a undo record
SQL> select sample_time, session_id sid,

Event, p1, p2, program, SQL _id
2 from dba_hist_active_sess_history
3 where event = 'Wait for a undo record'
4 order by sample_time;

Solution:
Find the smon process:
SQL> select pid, program from v $ process where program like '% SMON % ';

PID PROGRAM
----------------------------------------------------------
Oracle@localhost.localdomain (SMON)

Disable the transaction recovery function of smon:
Oradebug setorapid 8

Oradebug event 10513 trace name context forever, level 2;

Disable parallel transaction rollback:
Alter system set fast_start_parallel_rollback = false;

Re-enable the smon transaction recovery function:
Oradebug setorapid 8

Oradebug event 10513 trace name context off;

Determine the transaction recovery status:
Select * from V $ fast_start_transactions;