空间管理您的位置: ITPUB个人空间 » yxyup's footprint » 日志

投入多少，收获多少；投入多深，领悟多深。太阳底下没有新鲜事，排列组合就是创新。每天多做一点点，就是进步的开始，每天进步一点点，就是成功的开始，每天创新一点点，就是领先的开始。

兄弟,rollback也要三思

上一篇 / 下一篇 2008-03-27 20:21:50 / 个人分类：ORACLE

今天突然间发现系统中出现那么多的并行进程，搞得系统IDLE急剧下降。查看一下这些并行进程都在并行恢复。我KAO，好好的恢复什么呢。不经意地发现，竟然是64个并行进程，难道说fast_start_parallel_rollback 有关？查了一下手册，果然是这样。
手册上指出当这个值是LOW,rollback的进程数就是2*cpu个数，而我的系统中正好是LOW，CPU正好是32个，那么就是64个进程了。
可是又是什么引起rollback呢，并且还要rollback这么多这么久？感觉应该是有人做了批量DML，然后rollback了。
跑过去问下兄弟们，果然是一个兄弟在批量delete几千万量的数据，然后又rollback了. 无语......

oracle@HP:/home/oracle#top -h

System: HP                                       Thu Mar 27 19:37:04 2008
Load averages: 0.15, 0.10, 0.07
629 processes: 597 sleeping, 32 running
Cpu states: (avg)
LOAD   USER   NICE    SYS   IDLE BLOCK SWAIT   INTR   SSYS
0.15 10.9%   0.0%   1.2% 87.9%   0.0%   0.0%   0.0%   0.0%

Memory: 22847512K (11490808K) real, 25484440K (13146076K) virtual, 4590484K free Page# 1/23

CPU TTY PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU %CPU COMMAND
5   ? 6708 oracle   154 20 18144M 16956K sleep   37:12 22.21 22.17 ora_smon_hpehr
12   ? 6704 oracle   199 20 18149M 19296K run   1590:17 11.91 11.89 ora_lgwr_hpehr
25   ? 6702 oracle   154 20 18151M 24224K sleep 235:54 11.26 11.24 ora_dbw3_hpehr
4   ? 11354 oracle   154 20 18133M 5952K sleep    0:18 9.05 9.03 ora_p000_hpehr
25   ? 11476 oracle   154 20 18133M 5968K sleep    0:18 7.96 7.95 ora_p059_hpehr
24   ? 11432 oracle   154 20 18133M 5968K sleep    0:17 7.76 7.75 ora_p039_hpehr
26   ? 11484 oracle   154 20 18133M 5968K sleep    0:17 7.75 7.74 ora_p063_hpehr
26   ? 11416 oracle   154 20 18133M 5968K sleep    0:17 7.61 7.60 ora_p031_hpehr
21   ? 6700 oracle   154 20 18160M 29216K sleep 222:53 7.59 7.58 ora_dbw2_hpehr
31   ? 11360 oracle   154 20 18133M 5968K sleep    0:17 7.33 7.32 ora_p003_hpehr
29   ? 11424 oracle   154 20 18133M 5984K sleep    0:17 7.31 7.30 ora_p035_hpehr
29   ? 11440 oracle   154 20 18133M 5968K sleep    0:17 7.27 7.26 ora_p043_hpehr
30   ? 11392 oracle   154 20 18133M 5968K sleep    0:17 7.15 7.14 ora_p019_hpehr
27   ? 11384 oracle   154 20 18133M 5968K sleep    0:17 7.06 7.05 ora_p015_hpehr
27   ? 11376 oracle   154 20 18133M 5968K sleep    0:17 7.04 7.03 ora_p011_hpehr
29   ? 11456 oracle   154 20 18133M 5968K sleep    0:17 6.96 6.95 ora_p051_hpehr
26   ? 11400 oracle   154 20 18133M 5968K sleep    0:18 6.90 6.89 ora_p023_hpehr
25   ? 11408 oracle   154 20 18133M 5968K sleep    0:17 6.90 6.88 ora_p027_hpehr
11   ? 6698 oracle   138 20 18159M 32608K sleep 302:13 6.88 6.87 ora_dbw1_hpehr
31   ? 11468 oracle   154 20 18133M 5968K sleep    0:17 6.85 6.83 ora_p055_hpehr
30   ? 11368 oracle   154 20 18133M 5968K sleep    0:17 6.82 6.81 ora_p007_hpehr
28   ? 11448 oracle   154 20 18133M 5968K sleep    0:17 6.76 6.74 ora_p047_hpehr
11   ? 11388 oracle   154 20 18133M 5984K sleep    0:13 6.49 6.47 ora_p017_hpehr
17   ? 11464 oracle   154 20 18133M 5968K sleep    0:13 6.31 6.30 ora_p054_hpehr
3   ? 6696 oracle   154 20 18169M 38304K sleep 218:06 6.28 6.27 ora_dbw0_hpehr
13   ? 11364 oracle   154 20 18133M 5968K sleep    0:13 6.10 6.09 ora_p005_hpehr
16   ? 11446 oracle   154 20 18133M 5968K sleep    0:14 6.08 6.07 ora_p046_hpehr

HUNTER@hp>selects.program s.event from v$session s ,v$px_process p where s.sid=p.sid;

PROGRAM                               EVENT
----------------------------------- -----------------------------------
oracle@HP(P002)                PX Deq: Txn Recovery Start
oracle@HP(P006)                PX Deq: Txn Recovery Start
oracle@HP(P001)                PX Deq: Txn Recovery Start
oracle@HP(P005)                PX Deq: Txn Recovery Start
oracle@HP(P000)                PX Deq: Txn Recovery Start
oracle@HP(P003)                PX Deq: Txn Recovery Start
oracle@HP(P007)                PX Deq: Txn Recovery Start
oracle@HP(P011)                PX Deq: Txn Recovery Start
oracle@HP(P015)                PX Deq: Txn Recovery Start
oracle@HP(P004)                PX Deq: Txn Recovery Start
oracle@HP(P019)                PX Deq: Txn Recovery Start
oracle@HP(P023)                PX Deq: Txn Recovery Start
oracle@HP(P027)                PX Deq: Txn Recovery Start
oracle@HP(P009)                PX Deq: Txn Recovery Start
oracle@HP(P031)                PX Deq: Txn Recovery Start
oracle@HP(P035)                PX Deq: Txn Recovery Start
oracle@HP(P039)                PX Deq: Txn Recovery Start
oracle@HP(P010)                PX Deq: Txn Recovery Start
oracle@HP(P043)                PX Deq: Txn Recovery Start
oracle@HP(P047)                PX Deq: Txn Recovery Start
oracle@HP(P051)                PX Deq: Txn Recovery Start
oracle@HP(P055)                PX Deq: Txn Recovery Start
oracle@HP(P008)                PX Deq: Txn Recovery Start
oracle@HP(P059)                PX Deq: Txn Recovery Start
oracle@HP(P012)                PX Deq: Txn Recovery Start
oracle@HP(P063)                PX Deq: Txn Recovery Start
oracle@HP(P016)                PX Deq: Txn Recovery Start
oracle@HP(P020)                PX Deq: Txn Recovery Start
oracle@HP(P024)                PX Deq: Txn Recovery Start
oracle@HP(P028)                PX Deq: Txn Recovery Start
oracle@HP(P032)                PX Deq: Txn Recovery Start
oracle@HP(P036)                PX Deq: Txn Recovery Start
oracle@HP(P040)                PX Deq: Txn Recovery Start
oracle@HP(P044)                PX Deq: Txn Recovery Start
oracle@HP(P048)                PX Deq: Txn Recovery Start
oracle@HP(P052)                PX Deq: Txn Recovery Start
oracle@HP(P014)                PX Deq: Txn Recovery Start
oracle@HP(P056)                PX Deq: Txn Recovery Start
oracle@HP(P060)                PX Deq: Txn Recovery Start
oracle@HP(P013)                PX Deq: Txn Recovery Start
oracle@HP(P018)                PX Deq: Txn Recovery Start
oracle@HP(P022)                PX Deq: Txn Recovery Start
oracle@HP(P026)                PX Deq: Txn Recovery Start
oracle@HP(P030)                PX Deq: Txn Recovery Start
oracle@HP(P034)                PX Deq: Txn Recovery Start
oracle@HP(P038)                PX Deq: Txn Recovery Start
oracle@HP(P042)                PX Deq: Txn Recovery Start
oracle@HP(P046)                PX Deq: Txn Recovery Start
oracle@HP(P050)                PX Deq: Txn Recovery Start
oracle@HP(P054)                PX Deq: Txn Recovery Start
oracle@HP(P058)                PX Deq: Txn Recovery Start
oracle@HP(P017)                PX Deq: Txn Recovery Start
oracle@HP(P062)                PX Deq: Txn Recovery Start
oracle@HP(P021)                PX Deq: Txn Recovery Start
oracle@HP(P025)                PX Deq: Txn Recovery Start
oracle@HP(P029)                PX Deq: Txn Recovery Start
oracle@HP(P033)                PX Deq: Txn Recovery Start
oracle@HP(P037)                PX Deq: Txn Recovery Start
oracle@HP(P041)                PX Deq: Txn Recovery Start
oracle@HP(P045)                PX Deq: Txn Recovery Start
oracle@HP(P049)                PX Deq: Txn Recovery Start
oracle@HP(P053)                PX Deq: Txn Recovery Start
oracle@HP(P057)                PX Deq: Txn Recovery Start
oracle@HP(P061)                PX Deq: Txn Recovery Start

64 rows selected.

HUNTER@hp>showparameter fast_start_parallel_rollback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW

ORACLE手册解释

FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.

Values:

FALSE indicates that parallel rollback is disabled

LOW limits the number of rollback processes to 2 * CPU_COUNT

HIGH limits the number of rollback processes to 4 * CPU_COUNT