When a large transaction got killed, terminated we all know that smon will do the rollback of the transaction, “smon: enable tx recovery” which can evident in alert log.
Some time due to fast_start_parallel_rollback =high , lot of parallel sessions invoked and may complete hung your database or not permit any other session to work.
When a large transaction is killed, SMON recovers it.
A large number of wait for an undo record events and PX Deq: Txn Recovery Start events occur simultaneously.
I recently faced an issue, when I killed the session and session was updating the for 3 hours.
1) The session I was deleting was taking too long, so I killed it as follows.
alter system kill session 'sid,seiral#' immediate; |
2) Even though I marked it as KILLED with the above command, the rollback took a long time, so I caught the spid and executed kill -9 as shown below.
select 'kill -9 ' || spid from v$process where addr in (select paddr from v$session where status='KILLED' and type != 'BACKGROUND') |
When you kill a process like this, SMON uses multiple server processes to roll back the transaction .
This is called Fast Start Parallel Rollback .
After performing kill -9 at the OS level as shown below , a significant amount of wait for a undo record events are generated.
PX Deq: You can see that Txn Recovery Start has been detected.
===========================================================================================
Session Information
===========================================================================================
SID-SERIAL HashValue Spid Machine-Program COMMAND LOGON_TIME Call(s) EVENT
-------------- ------------ ---------- -------------------------- ------ ---------- ---------- -------------------------
[9874,46615] 0 7472078 db01-oracle@db01 (P000) n/a 0620.20:26 000.000 db file sequential read
[265,61609] 0 61670314 db01-oracle@db01 (P059) n/a 0620.20:26 000.000 wait for an undo record
[5681,17633] 0 35717294 db01-oracle@db01 (P035) n/a 0620.20:26 000.000 row cache lock
[5480,33199] 0 26707536 db01-oracle@db01 (P007) n/a 0620.20:26 000.000 wait for an undo record
[5238,20611] 0 20578538 db01-oracle@db01 (P036) n/a 0620.20:26 000.000 wait for an undo record
[5061,59825] 0 25493528 db01-oracle@db01 (P034) n/a 0620.20:26 000.000 wait for an undo record
[5044,17947] 0 57540908 db01-oracle@db01 (P057) n/a 0620.20:26 000.000 wait for an undo record
[4872,23015] 0 34210080 db01-oracle@db01 (P033) n/a 0620.20:26 000.000 wait for an undo record
[4856,26783] 0 55266940 db01-oracle@db01 (P026) n/a 0620.20:26 000.000 wait for an undo record
[4853,30281] 0 51118748 db01-oracle@db01 (P005) n/a 0620.20:26 000.000 wait for an undo record
[4671,45407] 0 10551614 db01-oracle@db01 (P032) n/a 0620.20:26 000.000 wait for an undo record
[4071,2219] 0 8257782 db01-oracle@db01 (P023) n/a 0620.20:26 000.000 wait for an undo record
[449,48911] 0 30605964 db01-oracle@db01 (P009) n/a 0620.20:26 000.000 wait for an undo record
[627,28573] 0 30802342 db01-oracle@db01 (P025) n/a 0620.20:26 000.000 wait for an undo record
[838,56489] 0 34407142 db01-oracle@db01 (P026) n/a 0620.20:26 000.000 wait for an undo record
[1029,26291] 0 54526396 db01-oracle@db01 (P054) n/a 0620.20:26 000.000 wait for an undo record
[2620,7781] 0 56623890 db01-oracle@db01 (P022) n/a 0620.20:26 000.000 wait for an undo record
[2664,23379] 0 49807750 db01-oracle@db01 (P004) n/a 0620.20:26 000.000 wait for an undo record
[1623,35105] 0 34865774 db01-oracle@db01 (P001) n/a 0620.20:26 000.000 wait for an undo record
[2268,17005] 0 50069814 db01-oracle@db01 (P011) n/a 0620.20:26 000.000 wait for an undo record
[2636,48395] 0 53346564 db01-oracle@db01 (P027) n/a 0620.20:26 000.000 wait for an undo record
[3018,3649] 0 18677940 db01-oracle@db01 (P028) n/a 0620.20:26 000.000 wait for an undo record
[3256,43711] 0 8848138 db01-oracle@db01 (P055) n/a 0620.20:26 000.000 wait for an undo record
[3277,17711] 0 52822526 db01-oracle@db01 (P029) n/a 0620.20:26 000.000 wait for an undo record
[3432,50939] 0 52363774 db01-oracle@db01 (P008) n/a 0620.20:26 000.000 wait for an undo record
[3846,50973] 0 15205060 db01-oracle@db01 (P030) n/a 0620.20:26 000.000 wait for an undo record
[4052,16635] 0 29557328 db01-oracle@db01 (P031) n/a 0620.20:26 000.000 wait for an undo record
[227,41677] 0 37814498 db01-oracle@db01 (P026) n/a 0620.20:26 000.000 wait for an undo record
================================================================================
Session Information
================================================================================
SID-SERIAL HashValue Spid Machine-Program COMMAND LOGON_TIME Call(s) EVENT
-------------- ------------ ---------- -------------------------- ------- ----------- ---------------------------
[9867,18343] 0 7472078 db01-oracle@db01 (P000) n/a 0620.20:26 000.000 wait for an undo record
[5238,20743] 0 20578538 db01-oracle@db01 (P036) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[3256,43759] 791757000 52822526 db01-oracle@db01 (P029) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8495,57391] 0 26518268 db01-oracle@db01 (P056) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[9080,60257] 0 17301526 db01-oracle@db01 (P018) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[9473,7991] 0 30474314 db01-oracle@db01 (P002) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[4853,30289] 0 55266940 db01-oracle@db01 (P026) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[9694,22351] 791757000 50200772 db01-oracle@db01 (P019) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[5484,56401] 791757000 26707536 db01-oracle@db01 (P007) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[3437,62165] 0 52363774 db01-oracle@db01 (P008) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[4671,45415] 0 10551614 db01-oracle@db01 (P032) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[5061,59833] 0 25493528 db01-oracle@db01 (P034) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[4872,23023] 0 34210080 db01-oracle@db01 (P033) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[4856,26797] 0 51118748 db01-oracle@db01 (P005) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[5681,17641] 791757000 35717294 db01-oracle@db01 (P035) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[5852,43825] 0 5439868 db01-oracle@db01 (P010) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[6268,34661] 0 15597644 db01-oracle@db01 (P037) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[6453,32903] 0 17433114 db01-oracle@db01 (P038) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[6700,63859] 0 40633182 db01-oracle@db01 (P039) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[6864,26577] 0 21168948 db01-oracle@db01 (P041) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[6896,55739] 0 14090420 db01-oracle@db01 (P006) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[7073,3583] 0 28182642 db01-oracle@db01 (P013) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[11908,26765] 0 56689098 db01-oracle@db01 (P003) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[7855,36717] 0 44368098 db01-oracle@db01 (P014) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8044,42353] 0 54526138 db01-oracle@db01 (P015) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8099,25759] 0 21758836 db01-oracle@db01 (P040) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8261,14049] 0 33227092 db01-oracle@db01 (P016) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8309,42657] 0 6357220 db01-oracle@db01 (P042) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8482,3069] 0 3605038 db01-oracle@db01 (P043) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8484,64059] 0 55050878 db01-oracle@db01 (P017) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[4071,2261] 0 29557328 db01-oracle@db01 (P031) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8668,6415] 0 52694900 db01-oracle@db01 (P044) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[8888,42229] 0 61408046 db01-oracle@db01 (P045) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[9056,6235] 791757000 44892260 db01-oracle@db01 (P046) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[4052,16643] 0 8257782 db01-oracle@db01 (P023) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[9276,18081] 0 7603116 db01-oracle@db01 (P047) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[3846,50985] 0 15205060 db01-oracle@db01 (P030) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[3277,17725] 0 8848138 db01-oracle@db01 (P055) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[3030,32601] 0 18677940 db01-oracle@db01 (P028) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[9874,46623] 0 26870730 db01-oracle@db01 (P048) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[10057,62267] 791757000 51970352 db01-oracle@db01 (P049) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[10459,64567] 791757000 39387460 db01-oracle@db01 (P050) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[10667,31975] 0 51183616 db01-oracle@db01 (P058) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[10710,37301] 791757000 15466958 db01-oracle@db01 (P051) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[10880,7319] 791757000 53018904 db01-oracle@db01 (P020) select 0620.20:26 000.000 PX Deq: Txn Recovery Start
[11075,40977] 0 40370534 db01-oracle@db01 (P021) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[11683,43791] 0 50266508 db01-oracle@db01 (P052) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[11903,921] 0 1376566 db01-oracle@db01 (P053) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[5044,17955] 0 57540908 db01-oracle@db01 (P057) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
[2636,48541] 0 53346564 db01-oracle@db01 (P027) n/a 0620.20:26 000.000 PX Deq: Txn Recovery Start
Initialization parameters
The degree of parallelism during recovery is determined based on the value of FAST_START_PARALLEL_ROLLBACK .
If it is FALSE, Parallel Rollback is not performed.
If LOW (default), rollbacks are performed in parallel up to CPU_COUNT * 2.
If HIGH, rollbacks are performed in parallel up to CPU_COUNT * 4.
Parallel rollback isn't always a good thing.
For services with a lot of changes, this may result in a failure with the following error message due to a lack of undo segments.
ORA-30036: unable to extend segment by 8 in undo tablespace "UNDO TABLESPACE NAME"
You can check the status with V$FAST_START_SERVERS during parallel rollback processing
select state, count(*) from V$FAST_START_SERVERS group by state;
STATE COUNT(*)
----------- ----------
RECOVERING 60
/
STATE COUNT(*)
----------- ----------
IDLE 59
RECOVERING 1
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at"
FROM v$fast_start_transactions ;
USN STATE Total Done ToDo Finish at
---------- ------------- ---------- -------- ---------- -------------------
1109 RECOVERING 2269489 31317 2238172 2026/01/02 00:06:03
We can also predict as follows:
select ktuxeusn, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe where ktuxecfl = 'DEAD';
KTUXEUSN Time KTUXESIZ KTUXESTA
---------- ------------------- ---------- --------------
1109 2026/01/02 23:07:22 1800231 ACTIVE
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at" FROM v$fast_start_transactions ; USN STATE Total Done ToDo Finish at ----------------- -------- -------- ------ ------------------- 1109 RECOVERED 32874 32874 0 2026/01/02 00:24:21
No comments:
Post a Comment