Huaiyao's Notes

Parallel transaction recovery

Test parallel transaction recovery.

A large table.

1
2
3
4
5
SQL> select count(*) from SIEBEL.S_EVT_ACT;
COUNT(*)
----------
11182092

A non-batch commit update.

1
2
3
4
5
6
7
8
9
D01DBA01:/export/home/oracle/rjin>more aaa.sql
UPDATE SIEBEL.S_EVT_ACT SET APPT_REPT_TYPE=
'Non-Repeating',
DB_LAST_UPD = SYSDATE,
DB_LAST_UPD_SRC = 'DataMigration100'
WHERE APPT_REPT_FLG = 'N' AND CAL_TYPE_CD=
'Calendar and Activities'
AND APPT_REPT_TYPE IS NULL
AND APPT_REPT_APPT_ID IS NULL;

Run 15mins,kill the session.

1
2
3
4
5
SQL> alter system kill session '52,21' immediate;
alter system kill session '52,21' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill

1
2
3
4
5
6
SQL> @aaa
UPDATE SIEBEL.S_EVT_ACT SET APPT_REPT_TYPE=
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed

No active session.

1
2
3
SQL> select * from v$transaction;
no rows selected

Server Load becomes high.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
D01DBA01:/export/home/oracle>top
load averages: 12.4, 5.52, 3.82; up 970+03:27:21 04:22:58
535 processes: 533 sleeping, 2 on cpu
CPU states: 87.0% idle, 12.2% user, 0.8% kernel, 0.0% iowait, 0.0% swap
Memory: 32G phys mem, 5700M free mem, 150G total swap, 148G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
6014 oracle 2 0 0 4000M 3927M cpu/119 9:22 0.65% oracle
27784 oracle 1 30 0 3996M 3981M sleep 0:49 0.22% oracle
28107 oracle 1 54 0 3996M 3981M sleep 0:04 0.13% oracle
2380 oracle 1 50 0 9976M 9839M sleep 102.8H 0.13% oracle
28073 oracle 1 55 0 3996M 3981M sleep 0:04 0.12% oracle
28127 oracle 1 54 0 3996M 3981M sleep 0:05 0.11% oracle
28206 oracle 1 52 0 3996M 3981M sleep 0:04 0.11% oracle
28208 oracle 1 59 0 3996M 3981M sleep 0:04 0.10% oracle
28275 oracle 1 49 0 3996M 3981M sleep 0:04 0.10% oracle
28069 oracle 1 59 0 3996M 3981M sleep 0:04 0.10% oracle
28250 oracle 1 39 0 3996M 3981M sleep 0:05 0.10% oracle
28015 oracle 1 59 0 3996M 3981M sleep 0:05 0.09% oracle
27935 oracle 1 51 0 3996M 3981M sleep 0:04 0.09% oracle
28029 oracle 1 49 0 3996M 3981M sleep 0:04 0.09% oracle
28033 oracle 1 51 0 3996M 3981M sleep 0:04 0.09% oracle

1
2
3
4
5
6
7
8
9
D01DBA01:/export/home/oracle>ps -ef|grep 6014
oracle 28630 26871 0 04:23:08 pts/9 0:00 grep 6014
oracle 6014 1 1 14:29:59 ? 9:30 ora_smon_D01DBA01
D01DBA01:/export/home/oracle>ps -ef|grep 27784
oracle 27784 1 1 04:20:27 ? 0:56 ora_p000_D01DBA01
oracle 28632 26871 0 04:23:14 pts/9 0:00 grep 27784
D01DBA01:/export/home/oracle>ps -ef|grep 28107
oracle 28669 26871 0 04:23:20 pts/9 0:00 grep 28107
oracle 28107 1 0 04:20:46 ? 0:05 ora_p139_D01DBA01

Pick 28107 to check.
See event ‘PX Deq: Txn Recovery Start; and ‘wait for a undo record’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
SQL> select * from v$process where spid=28107
SQL> @pr
Pivoting output....
==============================
ADDR : 000000049B4F3070
PID : 203
SPID : 28107
PNAME : P139
USERNAME : oracle
SERIAL# : 4
TERMINAL : UNKNOWN
PROGRAM : oracle@slcd000dbs006 (P139)
TRACEID :
TRACEFILE :
/opt/oracle/base/diag/rdbms/d01dba01/D01DBA01/trace/D01DBA01_p139_28107.trc
BACKGROUND :
LATCHWAIT :
LATCHSPIN :
PGA_USED_MEM : 1729528
PGA_ALLOC_MEM : 2033360
PGA_FREEABLE_MEM : 0
PGA_MAX_MEM : 2033360
PL/SQL procedure successfully completed.
SQL> select * from v$session where paddr='000000049B4F3070'
2 @pr
Pivoting output....
==============================
SADDR : 00000004A0845E90
SID : 526
SERIAL# : 25
AUDSID : 0
PADDR : 000000049B4F3070
USER# : 0
USERNAME :
COMMAND : 0
OWNERID : 65775
TADDR :
LOCKWAIT :
STATUS : INACTIVE
SERVER : DEDICATED
SCHEMA# : 0
SCHEMANAME : SYS
OSUSER : oracle
PROCESS : 28107
MACHINE : slcd000dbs006
PORT : 0
TERMINAL : UNKNOWN
PROGRAM : oracle@slcd000dbs006 (P139)
TYPE : USER
SQL_ADDRESS : 00
SQL_HASH_VALUE : 0
SQL_ID :
SQL_CHILD_NUMBER : 0
SQL_EXEC_START :
SQL_EXEC_ID :
PREV_SQL_ADDR : 00
PREV_HASH_VALUE : 0
PREV_SQL_ID :
PREV_CHILD_NUMBER : 0
PREV_EXEC_START :
PREV_EXEC_ID :
PLSQL_ENTRY_OBJECT_ID :
PLSQL_ENTRY_SUBPROGRAM_ID :
PLSQL_OBJECT_ID :
PLSQL_SUBPROGRAM_ID :
MODULE :
MODULE_HASH : 0
ACTION :
ACTION_HASH : 0
CLIENT_INFO :
FIXED_TABLE_SEQUENCE : 72251
ROW_WAIT_OBJ# : 0
ROW_WAIT_FILE# : 3
ROW_WAIT_BLOCK# : 307413
ROW_WAIT_ROW# : 0
TOP_LEVEL_CALL# : 0
LOGON_TIME : 25-JUN-17
LAST_CALL_ET : 0
PDML_ENABLED : NO
FAILOVER_TYPE : NONE
FAILOVER_METHOD : NONE
FAILED_OVER : NO
RESOURCE_CONSUMER_GROUP :
PDML_STATUS : DISABLED
PDDL_STATUS : ENABLED
PQ_STATUS : ENABLED
CURRENT_QUEUE_DURATION : 0
CLIENT_IDENTIFIER :
BLOCKING_SESSION_STATUS : UNKNOWN
BLOCKING_INSTANCE :
BLOCKING_SESSION :
FINAL_BLOCKING_SESSION_STATUS : UNKNOWN
FINAL_BLOCKING_INSTANCE :
FINAL_BLOCKING_SESSION :
SEQ# : 6524
EVENT# : 244
EVENT : PX Deq: Txn Recovery Start
P1TEXT : sleeptime/senderid
P1 : 200
P1RAW : 00000000000000C8
P2TEXT : passes
P2 : 1
P2RAW : 0000000000000001
P3TEXT :
P3 : 0
P3RAW : 00
WAIT_CLASS_ID : 2723168908
WAIT_CLASS# : 6
WAIT_CLASS : Idle
WAIT_TIME : 0
SECONDS_IN_WAIT : 0
STATE : WAITING
WAIT_TIME_MICRO : 21354
TIME_REMAINING_MICRO : -1
TIME_SINCE_LAST_WAIT_MICRO : 0
SERVICE_NAME : SYS$BACKGROUND
SQL_TRACE : DISABLED
SQL_TRACE_WAITS : FALSE
SQL_TRACE_BINDS : FALSE
SQL_TRACE_PLAN_STATS : FIRST EXEC
SESSION_EDITION_ID : 0
CREATOR_ADDR : 000000049B4F3070
CREATOR_SERIAL# : 4
ECID :
PL/SQL procedure successfully completed.
SQL> select * from v$session where paddr='000000049B4F3070'
2 @pr
Pivoting output....
==============================
SADDR : 00000004A0845E90
SID : 526
SERIAL# : 25
AUDSID : 0
PADDR : 000000049B4F3070
USER# : 0
USERNAME :
COMMAND : 0
OWNERID : 65775
TADDR :
LOCKWAIT :
STATUS : ACTIVE
SERVER : DEDICATED
SCHEMA# : 0
SCHEMANAME : SYS
OSUSER : oracle
PROCESS : 28107
MACHINE : slcd000dbs006
PORT : 0
TERMINAL : UNKNOWN
PROGRAM : oracle@slcd000dbs006 (P139)
TYPE : USER
SQL_ADDRESS : 00
SQL_HASH_VALUE : 0
SQL_ID :
SQL_CHILD_NUMBER : 0
SQL_EXEC_START :
SQL_EXEC_ID :
PREV_SQL_ADDR : 00
PREV_HASH_VALUE : 0
PREV_SQL_ID :
PREV_CHILD_NUMBER : 0
PREV_EXEC_START :
PREV_EXEC_ID :
PLSQL_ENTRY_OBJECT_ID :
PLSQL_ENTRY_SUBPROGRAM_ID :
PLSQL_OBJECT_ID :
PLSQL_SUBPROGRAM_ID :
MODULE :
MODULE_HASH : 0
ACTION :
ACTION_HASH : 0
CLIENT_INFO :
FIXED_TABLE_SEQUENCE : 72286
ROW_WAIT_OBJ# : 0
ROW_WAIT_FILE# : 3
ROW_WAIT_BLOCK# : 299876
ROW_WAIT_ROW# : 0
TOP_LEVEL_CALL# : 0
LOGON_TIME : 25-JUN-17
LAST_CALL_ET : 0
PDML_ENABLED : NO
FAILOVER_TYPE : NONE
FAILOVER_METHOD : NONE
FAILED_OVER : NO
RESOURCE_CONSUMER_GROUP :
PDML_STATUS : DISABLED
PDDL_STATUS : ENABLED
PQ_STATUS : ENABLED
CURRENT_QUEUE_DURATION : 0
CLIENT_IDENTIFIER :
BLOCKING_SESSION_STATUS : UNKNOWN
BLOCKING_INSTANCE :
BLOCKING_SESSION :
FINAL_BLOCKING_SESSION_STATUS : UNKNOWN
FINAL_BLOCKING_INSTANCE :
FINAL_BLOCKING_SESSION :
SEQ# : 7160
EVENT# : 754
EVENT : wait for a undo record
P1TEXT :
P1 : 0
P1RAW : 00
P2TEXT :
P2 : 0
P2RAW : 00
P3TEXT :
P3 : 0
P3RAW : 00
WAIT_CLASS_ID : 1893977003
WAIT_CLASS# : 0
WAIT_CLASS : Other
WAIT_TIME : 0
SECONDS_IN_WAIT : 0
STATE : WAITING
WAIT_TIME_MICRO : 67032
TIME_REMAINING_MICRO : 32968
TIME_SINCE_LAST_WAIT_MICRO : 0
SERVICE_NAME : SYS$BACKGROUND
SQL_TRACE : DISABLED
SQL_TRACE_WAITS : FALSE
SQL_TRACE_BINDS : FALSE
SQL_TRACE_PLAN_STATS : FIRST EXEC
SESSION_EDITION_ID : 0
CREATOR_ADDR : 000000049B4F3070
CREATOR_SERIAL# : 4
ECID :
PL/SQL procedure successfully completed.

Check parallel activity.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
SQL> set pages 1000 lines 200
SQL> select * from v$px_session;
SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
00000004A07CE390 408 33 239 1 1 1 1 1 256 256
00000004A07D4350 416 3 239 1 1 1 1 2 256 256
00000004A07DD2F0 421 35 239 1 1 1 1 3 256 256
000000049B709460 430 3 239 1 1 1 1 4 256 256
00000004A07E9270 437 3 239 1 1 1 1 5 256 256
000000049C74D4D0 444 3 239 1 1 1 1 6 256 256
000000049C75C430 457 25 239 1 1 1 1 7 256 256
00000004A0807130 463 23 239 1 1 1 1 8 256 256
00000004A080D0F0 471 27 239 1 1 1 1 9 256 256
000000049B742200 485 25 239 1 1 1 1 10 256 256
000000049C7802B0 491 21 239 1 1 1 1 11 256 256
000000049B757120 506 21 239 1 1 1 1 12 256 256
000000049B766080 519 21 239 1 1 1 1 13 256 256
00000004A0845E90 526 27 239 1 1 1 1 14 256 256
000000049C7AA0F0 533 29 239 1 1 1 1 15 256 256
000000049C7B00B0 541 25 239 1 1 1 1 16 256 256
00000004A085ADB0 547 23 239 1 1 1 1 17 256 256
00000004A0860D70 555 25 239 1 1 1 1 18 256 256
000000049C7C4FD0 562 25 239 1 1 1 1 19 256 256
00000004A086FCD0 568 27 239 1 1 1 1 20 256 256
00000004A0875C90 576 29 239 1 1 1 1 21 256 256
000000049B7A4DE0 582 25 239 1 1 1 1 22 256 256
00000004A0884BF0 589 23 239 1 1 1 1 23 256 256
00000004A088ABB0 597 29 239 1 1 1 1 24 256 256
000000049C7EEE10 604 23 239 1 1 1 1 25 256 256
000000049B7BFCC0 611 23 239 1 1 1 1 26 256 256
00000004A089FAD0 618 23 239 1 1 1 1 27 256 256
000000049C803D30 625 23 239 1 1 1 1 28 256 256
000000049B7D4BE0 632 25 239 1 1 1 1 29 256 256
000000049C812C90 638 23 239 1 1 1 1 30 256 256
000000049B7E3B40 645 31 239 1 1 1 1 31 256 256
000000049B7E9B00 653 25 239 1 1 1 1 32 256 256
00000004A08C9910 660 23 239 1 1 1 1 33 256 256
000000049B7F8A60 666 23 239 1 1 1 1 34 256 256
000000049B7FEA20 674 25 239 1 1 1 1 35 256 256
00000004A08DE830 681 23 239 1 1 1 1 36 256 256
000000049B80D980 687 25 239 1 1 1 1 37 256 256
00000004A08ED790 694 27 239 1 1 1 1 38 256 256
000000049B819900 703 25 239 1 1 1 1 39 256 256
000000049B8228A0 708 21 239 1 1 1 1 40 256 256
00000004A09026B0 715 23 239 1 1 1 1 41 256 256
00000004A0908670 723 21 239 1 1 1 1 42 256 256
000000049B8377C0 729 23 239 1 1 1 1 43 256 256
00000004A09175D0 736 23 239 1 1 1 1 44 256 256
000000049C87B830 743 23 239 1 1 1 1 45 256 256
00000004A092C4F0 757 25 239 1 1 1 1 46 256 256
00000004A09324B0 765 25 239 1 1 1 1 47 256 256
000000049B861600 771 21 239 1 1 1 1 48 256 256
000000049B8675C0 779 23 239 1 1 1 1 49 256 256
000000049C8A5670 785 25 239 1 1 1 1 50 256 256
000000049C8AB630 793 23 239 1 1 1 1 51 256 256
00000004A0956330 799 29 239 1 1 1 1 52 256 256
00000004A095C2F0 807 29 239 1 1 1 1 53 256 256
000000049B88B440 813 27 239 1 1 1 1 54 256 256
00000004A096B250 820 21 239 1 1 1 1 55 256 256
000000049C8CF4B0 827 21 239 1 1 1 1 56 256 256
000000049C8D5470 835 21 239 1 1 1 1 57 256 256
000000049B8A6320 842 27 239 1 1 1 1 58 256 256
000000049C8E43D0 848 23 239 1 1 1 1 59 256 256
000000049C8EA390 856 27 239 1 1 1 1 60 256 256
000000049B8BB240 863 27 239 1 1 1 1 61 256 256
00000004A099B050 870 29 239 1 1 1 1 62 256 256
000000049C8FF2B0 877 25 239 1 1 1 1 63 256 256
00000004A09A9FB0 883 23 239 1 1 1 1 64 256 256
00000004A09AFF70 891 27 239 1 1 1 1 65 256 256
000000049B560620 2 23 239 1 1 1 1 66 256 256
000000049C59E6D0 8 23 239 1 1 1 1 67 256 256
00000004A06463F0 17 23 239 1 1 1 1 68 256 256
000000049C5AA650 24 25 239 1 1 1 1 69 256 256
00000004A0655350 30 29 239 1 1 1 1 70 256 256
00000004A065B310 38 23 239 1 1 1 1 71 256 256
000000049B58A460 44 29 239 1 1 1 1 72 256 256
000000049B590420 52 25 239 1 1 1 1 73 256 256
00000004A0670230 59 23 239 1 1 1 1 74 256 256
000000049B59F380 65 27 239 1 1 1 1 75 256 256
00000004A067F190 72 25 239 1 1 1 1 76 256 256
000000049C5E33F0 79 23 239 1 1 1 1 77 256 256
000000049B5B42A0 86 25 239 1 1 1 1 78 256 256
00000004A06940B0 93 23 239 1 1 1 1 79 256 256
000000049C5F8310 100 25 239 1 1 1 1 80 256 256
000000049C5FE2D0 108 25 239 1 1 1 1 81 256 256
000000049B5CF180 115 29 239 1 1 1 1 82 256 256
00000004A06AEF90 122 27 239 1 1 1 1 83 256 256
000000049B5DE0E0 128 27 239 1 1 1 1 84 256 256
00000004A06BDEF0 135 25 239 1 1 1 1 85 256 256
000000049C622150 142 27 239 1 1 1 1 86 256 256
000000049C628110 150 23 239 1 1 1 1 87 256 256
00000004A06D2E10 156 25 239 1 1 1 1 88 256 256
000000049C637070 163 29 239 1 1 1 1 89 256 256
000000049C63D030 171 23 239 1 1 1 1 90 256 256
00000004A06E7D30 177 19 239 1 1 1 1 91 256 256
00000004A06EDCF0 185 29 239 1 1 1 1 92 256 256
000000049C651F50 192 25 239 1 1 1 1 93 256 256
000000049B622E00 199 21 239 1 1 1 1 94 256 256
000000049C660EB0 205 27 239 1 1 1 1 95 256 256
000000049C666E70 213 25 239 1 1 1 1 96 256 256
000000049B637D20 220 25 239 1 1 1 1 97 256 256
000000049C675DD0 226 27 239 1 1 1 1 98 256 256
000000049B646C80 233 25 239 1 1 1 1 99 256 256
000000049B64CC40 241 19 239 1 1 1 1 100 256 256
000000049C68ACF0 247 79 239 1 1 1 1 101 256 256
000000049B65BBA0 254 21 239 1 1 1 1 102 256 256
00000004A073B9B0 261 21 239 1 1 1 1 103 256 256
00000004A0741970 269 31 239 1 1 1 1 104 256 256
00000004A0747930 277 23 239 1 1 1 1 105 256 256
00000004A07508D0 282 25 239 1 1 1 1 106 256 256
000000049B67FA20 288 21 239 1 1 1 1 107 256 256
000000049C6BAAF0 297 21 239 1 1 1 1 108 256 256
000000049C6C3A90 302 217 239 1 1 1 1 109 256 256
000000049B691960 312 21 239 1 1 1 1 110 256 256
000000049C6CFA10 318 39 239 1 1 1 1 111 256 256
00000004A077A710 324 25 239 1 1 1 1 112 256 256
000000049C6DE970 331 19 239 1 1 1 1 113 256 256
000000049B6AF820 338 19 239 1 1 1 1 114 256 256
000000049C6EA8F0 347 323 239 1 1 1 1 115 256 256
000000049C6F3890 352 35 239 1 1 1 1 116 256 256
00000004A07A4550 366 47 239 1 1 1 1 117 256 256
000000049B6D36A0 372 123 239 1 1 1 1 118 256 256
00000004A07B04D0 382 19 239 1 1 1 1 119 256 256
00000004A07B9470 387 45 239 1 1 1 1 120 256 256
000000049B6E85C0 393 19 239 1 1 1 1 121 256 256
000000049B6EE580 401 39 239 1 1 1 1 122 256 256
000000049C72C630 407 45 239 1 1 1 1 123 256 256
000000049B6FD4E0 414 29 239 1 1 1 1 124 256 256
000000049C7385B0 423 3 239 1 1 1 1 125 256 256
00000004A07E32B0 429 25 239 1 1 1 1 126 256 256
000000049B712400 435 37 239 1 1 1 1 127 256 256
00000004A07F2210 442 29 239 1 1 1 1 128 256 256
00000004A07F81D0 450 31 239 1 1 1 1 129 256 256
000000049B727320 456 25 239 1 1 1 1 130 256 256
000000049B72D2E0 464 27 239 1 1 1 1 131 256 256
000000049C76B390 470 27 239 1 1 1 1 132 256 256
000000049C771350 478 29 239 1 1 1 1 133 256 256
00000004A081C050 484 25 239 1 1 1 1 134 256 256
00000004A0822010 492 29 239 1 1 1 1 135 256 256
00000004A0827FD0 500 25 239 1 1 1 1 136 256 256
00000004A0830F70 505 29 239 1 1 1 1 137 256 256
000000049B75D0E0 514 25 239 1 1 1 1 138 256 256
000000049C79B190 520 29 239 1 1 1 1 139 256 256
000000049B76C040 527 23 239 1 1 1 1 140 256 256
00000004A084BE50 534 21 239 1 1 1 1 141 256 256
000000049B77AFA0 540 25 239 1 1 1 1 142 256 256
000000049B780F60 548 27 239 1 1 1 1 143 256 256
000000049C7BF010 554 25 239 1 1 1 1 144 256 256
000000049B78FEC0 561 25 239 1 1 1 1 145 256 256
000000049B795E80 569 23 239 1 1 1 1 146 256 256
000000049C7D3F30 575 21 239 1 1 1 1 147 256 256
000000049C7D9EF0 583 25 239 1 1 1 1 148 256 256
000000049B7AADA0 590 27 239 1 1 1 1 149 256 256
000000049C7E8E50 596 21 239 1 1 1 1 150 256 256
000000049B7B9D00 603 27 239 1 1 1 1 151 256 256
00000004A0899B10 610 27 239 1 1 1 1 152 256 256
000000049C7FDD70 617 27 239 1 1 1 1 153 256 256
000000049B7CEC20 624 27 239 1 1 1 1 154 256 256
00000004A08AEA30 631 25 239 1 1 1 1 155 256 256
00000004A08B49F0 639 27 239 1 1 1 1 156 256 256
000000049C818C50 646 29 239 1 1 1 1 157 256 256
000000049C81EC10 654 25 239 1 1 1 1 158 256 256
000000049C827BB0 659 29 239 1 1 1 1 159 256 256
000000049C82DB70 667 27 239 1 1 1 1 160 256 256
00000004A08D8870 673 27 239 1 1 1 1 161 256 256
000000049C83CAD0 680 27 239 1 1 1 1 162 256 256
000000049C842A90 688 25 239 1 1 1 1 163 256 256
000000049B813940 695 23 239 1 1 1 1 164 256 256
00000004A08F3750 702 25 239 1 1 1 1 165 256 256
000000049C8579B0 709 29 239 1 1 1 1 166 256 256
000000049B828860 716 27 239 1 1 1 1 167 256 256
000000049C866910 722 29 239 1 1 1 1 168 256 256
000000049C86C8D0 730 27 239 1 1 1 1 169 256 256
000000049B83D780 737 27 239 1 1 1 1 170 256 256
00000004A091D590 744 27 239 1 1 1 1 171 256 256
000000049C8817F0 751 25 239 1 1 1 1 172 256 256
000000049B8526A0 758 25 239 1 1 1 1 173 256 256
000000049C890750 764 25 239 1 1 1 1 174 256 256
000000049C896710 772 29 239 1 1 1 1 175 256 256
00000004A0941410 778 27 239 1 1 1 1 176 256 256
00000004A09473D0 786 25 239 1 1 1 1 177 256 256
000000049B876520 792 27 239 1 1 1 1 178 256 256
000000049B87C4E0 800 21 239 1 1 1 1 179 256 256
000000049C8BA590 806 23 239 1 1 1 1 180 256 256
000000049C8C0550 814 23 239 1 1 1 1 181 256 256
000000049B891400 821 29 239 1 1 1 1 182 256 256
00000004A0971210 828 29 239 1 1 1 1 183 256 256
000000049B8A0360 834 29 239 1 1 1 1 184 256 256
00000004A0980170 841 23 239 1 1 1 1 185 256 256
00000004A0986130 849 27 239 1 1 1 1 186 256 256
000000049B8B5280 855 23 239 1 1 1 1 187 256 256
00000004A0995090 862 23 239 1 1 1 1 188 256 256
000000049C8F92F0 869 21 239 1 1 1 1 189 256 256
000000049B8CA1A0 876 25 239 1 1 1 1 190 256 256
000000049B8D0160 884 27 239 1 1 1 1 191 256 256
000000049C90E210 890 23 239 1 1 1 1 192 256 256
00000004A063A470 1 27 239 1 1 1 1 193 256 256
00000004A0640430 9 27 239 1 1 1 1 194 256 256
000000049C5A4690 16 33 239 1 1 1 1 195 256 256
000000049B575540 23 25 239 1 1 1 1 196 256 256
000000049B57B500 31 21 239 1 1 1 1 197 256 256
000000049C5B95B0 37 27 239 1 1 1 1 198 256 256
000000049C5BF570 45 21 239 1 1 1 1 199 256 256
000000049C5C5530 53 39 239 1 1 1 1 200 256 256
000000049C5CE4D0 58 27 239 1 1 1 1 201 256 256
000000049C5D4490 66 25 239 1 1 1 1 202 256 256
000000049B5A5340 73 25 239 1 1 1 1 203 256 256
00000004A0685150 80 27 239 1 1 1 1 204 256 256
000000049C5E93B0 87 25 239 1 1 1 1 205 256 256
000000049B5BA260 94 27 239 1 1 1 1 206 256 256
00000004A069A070 101 25 239 1 1 1 1 207 256 256
000000049B5C91C0 107 25 239 1 1 1 1 208 256 256
00000004A06A8FD0 114 21 239 1 1 1 1 209 256 256
000000049C60D230 121 23 239 1 1 1 1 210 256 256
000000049C6131F0 129 23 239 1 1 1 1 211 256 256
000000049B5E40A0 136 25 239 1 1 1 1 212 256 256
00000004A06C3EB0 143 23 239 1 1 1 1 213 256 256
000000049B5F3000 149 27 239 1 1 1 1 214 256 256
000000049B5F8FC0 157 25 239 1 1 1 1 215 256 256
00000004A06D8DD0 164 21 239 1 1 1 1 216 256 256
000000049B607F20 170 27 239 1 1 1 1 217 256 256
000000049B60DEE0 178 31 239 1 1 1 1 218 256 256
000000049C64BF90 184 21 239 1 1 1 1 219 256 256
000000049B61CE40 191 25 239 1 1 1 1 220 256 256
00000004A06FCC50 198 29 239 1 1 1 1 221 256 256
00000004A0702C10 206 23 239 1 1 1 1 222 256 256
000000049B631D60 212 25 239 1 1 1 1 223 256 256
00000004A0711B70 219 25 239 1 1 1 1 224 256 256
00000004A0717B30 227 23 239 1 1 1 1 225 256 256
000000049C67BD90 234 25 239 1 1 1 1 226 256 256
00000004A0726A90 240 87 239 1 1 1 1 227 256 256
00000004A072CA50 248 41 239 1 1 1 1 228 256 256
000000049C690CB0 255 29 239 1 1 1 1 229 256 256
000000049B661B60 262 29 239 1 1 1 1 230 256 256
000000049B667B20 270 31 239 1 1 1 1 231 256 256
000000049C6A5BD0 276 27 239 1 1 1 1 232 256 256
000000049C6AEB70 281 25 239 1 1 1 1 233 256 256
000000049C6B4B30 289 29 239 1 1 1 1 234 256 256
00000004A075C850 298 29 239 1 1 1 1 235 256 256
00000004A07657F0 303 41 239 1 1 1 1 236 256 256
00000004A076B7B0 311 27 239 1 1 1 1 237 256 256
000000049B69A900 317 47 239 1 1 1 1 238 256 256
000000049B6A08C0 325 35 239 1 1 1 1 239 256 256
00000004A07806D0 332 133 239 1 1 1 1 240 256 256
00000004A0786690 340 3 239 1 1 1 1 241 256 256
000000049C6ED8D0 344 187 239 1 1 1 1 242 256 256
00000004A07955F0 353 113 239 1 1 1 1 243 256 256
00000004A079B5B0 361 33 239 1 1 1 1 244 256 256
000000049C6FF810 368 31 239 1 1 1 1 245 256 256
00000004A07AA510 374 407 239 1 1 1 1 246 256 256
000000049C70E770 381 69 239 1 1 1 1 247 256 256
000000049C717710 386 39 239 1 1 1 1 248 256 256
000000049B6E55E0 396 5 239 1 1 1 1 249 256 256
00000004A07C83D0 400 57 239 1 1 1 1 250 256 256
000000049B6F4540 409 69 239 1 1 1 1 251 256 256
000000049C7325F0 415 25 239 1 1 1 1 252 256 256
000000049B7034A0 422 27 239 1 1 1 1 253 256 256
000000049C741550 428 25 239 1 1 1 1 254 256 256
000000049C747510 436 39 239 1 1 1 1 255 256 256
000000049B7183C0 443 29 239 1 1 1 1 256 256 256
000000049C684D30 239 1 239
257 rows selected.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
SQL> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P192 IN USE 256 28216 1 27
P065 IN USE 128 27945 2 23
P066 IN USE 129 27947 8 23
P193 IN USE 257 28218 9 27
P194 IN USE 258 28220 16 33
P067 IN USE 130 27949 17 23
P195 IN USE 259 28222 23 25
P068 IN USE 131 27951 24 25
P069 IN USE 132 27953 30 29
P196 IN USE 260 28224 31 21
P197 IN USE 261 28226 37 27
P070 IN USE 133 27955 38 23
P071 IN USE 134 27957 44 29
P198 IN USE 262 28228 45 21
P072 IN USE 135 27959 52 25
P199 IN USE 263 28230 53 39
P200 IN USE 264 28232 58 27
P073 IN USE 136 27961 59 23
P074 IN USE 137 27963 65 27
P201 IN USE 265 28234 66 25
P075 IN USE 138 27965 72 25
P202 IN USE 266 28236 73 25
P076 IN USE 139 27967 79 23
P203 IN USE 267 28238 80 27
P077 IN USE 140 27969 86 25
P204 IN USE 268 28240 87 25
P078 IN USE 141 27971 93 23
P205 IN USE 269 28242 94 27
P079 IN USE 142 27973 100 25
P206 IN USE 270 28244 101 25
P207 IN USE 271 28246 107 25
P080 IN USE 143 27975 108 25
P208 IN USE 272 28248 114 21
P081 IN USE 144 27977 115 29
P209 IN USE 273 28250 121 23
P082 IN USE 145 27979 122 27
P083 IN USE 146 27981 128 27
P210 IN USE 274 28252 129 23
P084 IN USE 147 27983 135 25
P211 IN USE 275 28254 136 25
P085 IN USE 148 27985 142 27
P212 IN USE 276 28256 143 23
P213 IN USE 277 28258 149 27
P086 IN USE 149 27987 150 23
P087 IN USE 150 27989 156 25
P214 IN USE 278 28260 157 25
P088 IN USE 151 27991 163 29
P215 IN USE 279 28262 164 21
P216 IN USE 280 28264 170 27
P089 IN USE 152 27993 171 23
P090 IN USE 153 27995 177 19
P217 IN USE 281 28266 178 31
P218 IN USE 282 28268 184 21
P091 IN USE 154 27997 185 29
P219 IN USE 283 28270 191 25
P092 IN USE 155 27999 192 25
P220 IN USE 284 28273 198 29
P093 IN USE 156 28001 199 21
P094 IN USE 157 28003 205 27
P221 IN USE 285 28275 206 23
P222 IN USE 286 28277 212 25
P095 IN USE 158 28005 213 25
P223 IN USE 287 28279 219 25
P096 IN USE 159 28007 220 25
P097 IN USE 160 28009 226 27
P224 IN USE 288 28281 227 23
P098 IN USE 161 28011 233 25
P225 IN USE 289 28283 234 25
P226 IN USE 290 28285 240 87
P099 IN USE 162 28013 241 19
P100 IN USE 163 28015 247 79
P227 IN USE 291 28287 248 41
P101 IN USE 164 28018 254 21
P228 IN USE 292 28289 255 29
P102 IN USE 165 28029 261 21
P229 IN USE 293 28291 262 29
P103 IN USE 166 28031 269 31
P230 IN USE 294 28293 270 31
P231 IN USE 295 28295 276 27
P104 IN USE 167 28033 277 23
P232 IN USE 296 28297 281 25
P105 IN USE 168 28035 282 25
P106 IN USE 169 28037 288 21
P233 IN USE 297 28299 289 29
P107 IN USE 170 28039 297 21
P234 IN USE 298 28301 298 29
P108 IN USE 171 28041 302 217
P235 IN USE 299 28303 303 41
P236 IN USE 300 28305 311 27
P109 IN USE 172 28043 312 21
P237 IN USE 301 28307 317 47
P110 IN USE 173 28045 318 39
P111 IN USE 174 28047 324 25
P238 IN USE 302 28309 325 35
P112 IN USE 175 28049 331 19
P239 IN USE 303 28311 332 133
P113 IN USE 176 28051 338 19
P240 IN USE 304 28313 340 3
P241 IN USE 305 28315 344 187
P114 IN USE 177 28053 347 323
P115 IN USE 178 28058 352 35
P242 IN USE 306 28317 353 113
P243 IN USE 307 28319 361 33
P116 IN USE 180 28060 366 47
P244 IN USE 308 28321 368 31
P117 IN USE 181 28062 372 123
P245 IN USE 309 28323 374 407
P246 IN USE 310 28326 381 69
P118 IN USE 182 28065 382 19
P247 IN USE 311 28328 386 39
P119 IN USE 183 28067 387 45
P120 IN USE 184 28069 393 19
P248 IN USE 312 28330 396 5
P249 IN USE 313 28332 400 57
P121 IN USE 185 28071 401 39
P122 IN USE 186 28073 407 45
P000 IN USE 58 27784 408 33
P250 IN USE 314 28334 409 69
P123 IN USE 187 28075 414 29
P251 IN USE 315 28336 415 25
P001 IN USE 59 27787 416 3
P002 IN USE 60 27789 421 35
P252 IN USE 316 28338 422 27
P124 IN USE 188 28077 423 3
P253 IN USE 317 28340 428 25
P125 IN USE 189 28079 429 25
P003 IN USE 61 27793 430 3
P126 IN USE 190 28081 435 37
P254 IN USE 318 28342 436 39
P004 IN USE 62 27795 437 3
P127 IN USE 191 28083 442 29
P255 IN USE 319 28346 443 29
P005 IN USE 63 27797 444 3
P128 IN USE 192 28085 450 31
P129 IN USE 193 28087 456 25
P006 IN USE 65 27799 457 25
P007 IN USE 66 27801 463 23
P130 IN USE 194 28089 464 27
P131 IN USE 195 28091 470 27
P008 IN USE 67 27803 471 27
P132 IN USE 196 28093 478 29
P133 IN USE 197 28095 484 25
P009 IN USE 69 27805 485 25
P010 IN USE 70 27808 491 21
P134 IN USE 198 28097 492 29
P135 IN USE 199 28099 500 25
P136 IN USE 200 28101 505 29
P011 IN USE 72 27810 506 21
P137 IN USE 201 28103 514 25
P012 IN USE 74 27812 519 21
P138 IN USE 202 28105 520 29
P013 IN USE 75 27814 526 27
P139 IN USE 203 28107 527 23
P014 IN USE 76 27816 533 29
P140 IN USE 204 28109 534 21
P141 IN USE 205 28111 540 25
P015 IN USE 77 27818 541 25
P016 IN USE 78 27820 547 23
P142 IN USE 206 28113 548 27
P143 IN USE 207 28115 554 25
P017 IN USE 79 27822 555 25
P144 IN USE 208 28117 561 25
P018 IN USE 80 27824 562 25
P019 IN USE 81 27826 568 27
P145 IN USE 209 28119 569 23
P146 IN USE 210 28121 575 21
P020 IN USE 82 27828 576 29
P021 IN USE 83 27830 582 25
P147 IN USE 211 28123 583 25
P022 IN USE 84 27832 589 23
P148 IN USE 212 28125 590 27
P149 IN USE 213 28127 596 21
P023 IN USE 85 27834 597 29
P150 IN USE 214 28129 603 27
P024 IN USE 86 27836 604 23
P151 IN USE 215 28131 610 27
P025 IN USE 87 27838 611 23
P152 IN USE 216 28133 617 27
P026 IN USE 88 27840 618 23
P153 IN USE 217 28135 624 27
P027 IN USE 89 27842 625 23
P154 IN USE 218 28137 631 25
P028 IN USE 90 27844 632 25
P029 IN USE 91 27846 638 23
P155 IN USE 219 28139 639 27
P030 IN USE 92 27848 645 31
P156 IN USE 220 28141 646 29
P031 IN USE 93 27850 653 25
P157 IN USE 221 28143 654 25
P158 IN USE 222 28145 659 29
P032 IN USE 94 27853 660 23
P033 IN USE 95 27857 666 23
P159 IN USE 223 28147 667 27
P160 IN USE 224 28149 673 27
P034 IN USE 96 27859 674 25
P161 IN USE 225 28151 680 27
P035 IN USE 97 27861 681 23
P036 IN USE 98 27864 687 25
P162 IN USE 226 28153 688 25
P037 IN USE 99 27869 694 27
P163 IN USE 227 28155 695 23
P164 IN USE 228 28157 702 25
P038 IN USE 100 27875 703 25
P039 IN USE 101 27883 708 21
P165 IN USE 229 28159 709 29
P040 IN USE 102 27888 715 23
P166 IN USE 230 28161 716 27
P167 IN USE 231 28163 722 29
P041 IN USE 103 27891 723 21
P042 IN USE 104 27893 729 23
P168 IN USE 232 28165 730 27
P043 IN USE 105 27895 736 23
P169 IN USE 233 28167 737 27
P044 IN USE 106 27897 743 23
P170 IN USE 234 28169 744 27
P171 IN USE 235 28171 751 25
P045 IN USE 108 27899 757 25
P172 IN USE 236 28173 758 25
P173 IN USE 237 28176 764 25
P046 IN USE 109 27901 765 25
P047 IN USE 110 27903 771 21
P174 IN USE 238 28179 772 29
P175 IN USE 239 28181 778 27
P048 IN USE 111 27905 779 23
P049 IN USE 112 27907 785 25
P176 IN USE 240 28183 786 25
P177 IN USE 241 28185 792 27
P050 IN USE 113 27909 793 23
P051 IN USE 114 27911 799 29
P178 IN USE 242 28187 800 21
P179 IN USE 243 28189 806 23
P052 IN USE 115 27913 807 29
P053 IN USE 116 27915 813 27
P180 IN USE 244 28191 814 23
P054 IN USE 117 27917 820 21
P181 IN USE 245 28193 821 29
P055 IN USE 118 27919 827 21
P182 IN USE 246 28195 828 29
P183 IN USE 247 28198 834 29
P056 IN USE 119 27921 835 21
P184 IN USE 248 28200 841 23
P057 IN USE 120 27923 842 27
P058 IN USE 121 27925 848 23
P185 IN USE 249 28202 849 27
P186 IN USE 250 28204 855 23
P059 IN USE 122 27931 856 27
P187 IN USE 251 28206 862 23
P060 IN USE 123 27935 863 27
P188 IN USE 252 28208 869 21
P061 IN USE 124 27937 870 29
P189 IN USE 253 28210 876 25
P062 IN USE 125 27939 877 25
P063 IN USE 126 27941 883 23
P190 IN USE 254 28212 884 27
P191 IN USE 255 28214 890 23
P064 IN USE 127 27943 891 27
256 rows selected.

Top wait event for last 5 mins shows lots of ‘wait for a undo record’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> @ashtop event 1=1 &5min
Total
Seconds AAS %This EVENT CPU User I/O Application Concurrency FIRST_SEEN LAST_SEEN
--------- ------- ------- ---------------------------------------- ---------- ---------- ----------- ----------- -------------------- --------------------
40373 134.6 92% | wait for a undo record 0 0 0 0 2017-06-25 04:21:01 2017-06-25 04:25:54
2090 7.0 5% | 2090 0 0 0 2017-06-25 04:20:57 2017-06-25 04:25:54
931 3.1 2% | latch: row cache objects 0 0 0 931 2017-06-25 04:21:01 2017-06-25 04:25:46
170 .6 0% | PX Deq: Test for msg 0 0 0 0 2017-06-25 04:21:01 2017-06-25 04:25:52
101 .3 0% | buffer busy waits 0 0 0 101 2017-06-25 04:21:10 2017-06-25 04:25:43
47 .2 0% | db file sequential read 0 47 0 0 2017-06-25 04:21:01 2017-06-25 04:25:46
41 .1 0% | wait for stopper event to be increased 0 0 0 0 2017-06-25 04:21:07 2017-06-25 04:25:51
7 .0 0% | os thread startup 0 0 0 7 2017-06-25 04:20:55 2017-06-25 04:21:20
2 .0 0% | log file parallel write 0 0 0 0 2017-06-25 04:22:51 2017-06-25 04:25:46
1 .0 0% | db file parallel write 0 0 0 0 2017-06-25 04:21:37 2017-06-25 04:21:37
10 rows selected.

Check OEM
See high CPU/high active session/parallel execution in the same time window.
Performance Home
Top Activity

Learn FAST_START_PARALLEL_ROLLBACK
Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams080.htm#REFRN10059

1
2
3
4
5
6
7
8
9
10
11
12
FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions.
When using fast-start parallel rollback, the background process SMON is used as a coordinator and rolls back sets of transactions in parallel, using multiple server processes.
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

1
2
3
4
5
6
7
8
9
10
11
SQL> show parameter cpu_count
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
cpu_count integer 128
SQL> show parameter fast_start_parallel_rollback
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW

So above 256(128*2) PX sessions/processes are as expected.