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-processing ideas parallel recovery of large oracle transactions leads to database performance degradation-high cpu usage processing ideas large transaction rollback large-scale transaction rollback has a very high cost, it not only locks the required resources, but also consumes CPU and IO, especially IO, which will be extremely intensive. At this time, we hope to reduce the impact of rollback. It is impossible to stop. To maintain Database Consistency, rollback must be completed, so the impact can only be reduced. The fast_start_parallel_rollback parameter can be used to implement this swap. The default value is low, indicating that parallel processes with two times the number of CPUs are started for rollback. In addition, there are two values: high and false. high indicates that parallel processes with 4 times the number of CPUs are rolled back. This parameter can be set dynamically, but it may not interrupt the parallel recovery. You can set the restart method as the best. Set fast_start_parallel_rollback to false and restart the database. After the database is restarted, no TRANSACTION information exists in the v $ TRANSACTION view, but the V $ fast_start_transactions view can find the number of rollback tasks. In a large database, the failure cost of a large operation is relatively high. In severe cases, the database may even be suspended. It is necessary to check the transaction size before KILL a large transaction, and we also need to know how much rollback has been performed. V $ transaction and v $ session get the transaction size. Select t. used_ublk from v $ transaction t v $ session sWhere t. ses_addr = s. saddr and s. sid = & sid continuously monitors the result of this statement after the transaction fails or the session is killed to estimate the rollback progress. If you observe that used_ublk is almost immobile or the rollback is very slow, you can determine whether the following is caused by parallel recovery (parallel recovery may sometimes cause database recovery suspension ). In the case of parallel recovery, Smon will seize the TX lock, and some PS lock PX processes should occupy a large amount of CPU resources. The two views V $ fast_start_servers and v $ fast_start_transaction indicate whether parallel recovery is performed. 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 this statement turns off parallel rollback and uses serialization. Similarly, if it is serialized ROLLBACK and CPU resources are still available, you can use parallel recovery to speed up ROLLBACK. If the overall system has basically been suspended, you can shutdown the database and use parallel rollback. Parallel recovery shows many PX processes waiting in $ session_wait. the Smon process acts as a coordination process and waits for the completion of the PX process. After the database is closed or the shadow process is killed, transaction information cannot be found in v $ transaction. At this time, there is transaction information in v $ fast_start_transaction. For versions earlier than 8i, you can only continuously monitor the uet $ and fet $ tables to check whether rollback is performed, and Smon should often capture the ST lock. When the database performance declines, the first thing to do is: 1. first, look for the OS information: top --- view the process occupies the most system resources --- here we can also see that the process occupies the most resources. Mem --- usage, whether there are a large number of page breaks. System --- I/o is normal. 2. AWR --- "top 5 user event here is a simple analysis of parallel recovery cases of large transactions 1. view wait for a undo record in the awr report ---- view historical information 2. select event, count (*) from region where sample_time <to_timestamp ('000000', 'yyyymmddhh24mis ') and sample_time> to_timestamp ('20140901', 'yyyymmddhh24mis ') and instance_number = 1 group by eventorder by 2; --- view the processes that have wait for a undo recordSQL> select sample_time 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: locate the smon process: SQL> select pid, program from v $ process where program like '% SMON %'; pid program ---------- ------------------------------------------------ 8 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;