Blocking Session And Final Blocking Session In v$session Oracle
- What is the difference between blocking session and final blocking session?
- Can they both be same or different?
- When do they occur?
- How to identify them?
Blocking session means the session which blocks another session currently and acts as the parent blocker.
Final blocking session means the child blocker which blocks another session from a parent blocker. They are part of wait chains which occur on top of the chain.
If the table is locked in exclusive mode, then the consequent sessions are blocked from this session.
SQL> LOCK table dspm in exclusive mode;
Table(s) Locked.
In another session, there is another session which execute direct path insert.SQL> INSERT /*+APPEND PARALLEL(8) */ into dspm select * from dba_source;
====>waiting in DML lockBoth blocking and final blocking sessions are same here because session 272 is both blocking and final blocking session and need to release the lock to provide access to other sessions.
SELECT blocking_session,final_blocking_session,sid,serial# from v$session;
BLOCKING_SESSION FINAL_BLOCKING_SESSION SID SERIAL#
---------------- ---------------------- ---------- ----------
30 53527
33 43650
272 272 255 57902
Another scenario where blocking and final blocking sessions both are totally different. Blocking sid is 10 and final blocking sid is 247.
BLOCKING_SESSION FINAL_BLOCKING_SESSION SID SERIAL#
---------------- ---------------------- ---------- ----------
10 247 255 62576
257 58720
258 52129
259 54077
261 50178
10 247 262 27004
263 9138
264 22396
10 247 269 62628
10 247 273 43140
280 1236
BLOCKING_SESSION FINAL_BLOCKING_SESSION SID SERIAL#
---------------- ---------------------- ---------- ----------
282 42139
10 247 286 47997
57 rows selected.
If we further check the details, they both are derived from log writer master and slave processes where slaves are the final blocker on top of the wait chains.
SQL> col EVENT for a20
SQL> SELECT sid,username,program,module,action,event from v$session where sid in (10,247);
SID USERNAME PROGRAM MODULE ACTION EVENT
---------- -------------------- -------------------- -------------------- -------------------- --------------------
10 oracle@xhydra (LGWR) rdbms ipc message
247 oracle@xhydra (LG00) LGWR worker group id
leAnother situation where multiple blockers are having different final blocking sid.
SQL> SELECT blocking_session,final_blocking_session,sid,serial# from v$session;
BLOCKING_SESSION FINAL_BLOCKING_SESSION SID SERIAL#
---------------- ---------------------- ---------- ----------
1 54950
2 61194
3 43260
5 37310
6 51617
7 21945
8 35927
9 22842
10 3966
11 53786
286 10 12 15482
10 10 13 32839
10 10 15 63226
17 29137
52 10 18 26731
19 32942
20 13561
23 24998
24 59432
30 6971
15 10 31 56748
35 7537
10 10 36 10042
37 25337
262 10 38 47235
46 51123
277 10 47 63031
10 10 48 41990
10 10 52 36831
238 56860
239 8282
240 8263
241 60647
242 36474
243 45151
244 26719
245 26867
246 47090
247 10706
248 40117
249 9474
250 33411
251 26321
252 38322
253 2770
254 57166
10 10 255 30058
257 58720
258 52129
259 54077
261 50178
10 10 262 27004
263 9138
10 10 269 62628
10 10 273 43140
48 10 277 8681
280 1236
282 42139
10 10 285 46456
10 10 286 47997
60 rows selected.
There are background processes which acts as blockers and final blockers. Most of the processes are blocked by a root blocker LGWR process to further not progress.
SQL> SELECT sid,username,program,module,action,event from v$session where sid in (10,247,48,286,262,15,277,52);
SID USERNAME PROGRAM MODULE ACTION EVENT
---------- -------------------- -------------------- -------------------- -------------------- --------------------
10 oracle@xhydra (LGWR) rdbms ipc message
15 oracle@xhydra (W001) KTSJ KTSJ Slave log file switch (arc
hiving needed)
48 oracle@xhydra (W006) KTSJ KTSJ Slave log file switch (arc
hiving needed)
52 oracle@xhydra (M000) MMON_SLAVE Auto-Flush Slave Act log file switch (arc
ion hiving needed)
247 oracle@xhydra (LG00) LGWR worker group id
le
262 oracle@xhydra (W003) KTSJ KTSJ Slave log file switch (arc
hiving needed)
277 oracle@xhydra (J000) buffer busy waits
286 oracle@xhydra (W005) KTSJ KTSJ Slave log file switch (arc
hiving needed)
8 rows selected.
SID 10 is the final blocker which is the LGWR culprit which causes other processes to wait on log file switch (archiving needed) , LGWR worker group idle etc..
*** 2023-11-14T21:43:46.367100+05:30 (CDB$ROOT(1))
HM: Early Warning - Session ID 254 serial# 57166 OS PID 67156 (M001)
is waiting on 'row cache lock' for 32 seconds, wait id 8
p1: 'cache id'=0x16, p2: 'mode'=0x0, p3: 'request'=0x3
Blocked by Session ID 52 serial# 36831 on instance 1
which is waiting on 'log file switch (archiving needed)' for 32 seconds
p1: ''=0x0, p2: ''=0x0, p3: ''=0x0
Final Blocker is Session ID 10 serial# 3966 on instance 1
which is waiting on 'rdbms ipc message' for 0 seconds
p1: 'timeout'=0xe1, p2: ''=0x0, p3: ''=0x0
IO
Total Self- Total Total Outlr Outlr Outlr
Hung Rslvd Rslvd Wait WaitTm Wait WaitTm Wait
Sess Hangs Hangs Count Secs Count Secs Count Wait Event
------ ------ ------ ------ ------ ------ ------ ------ -----------
0 0 0 0 0 0 0 0 row cache lock
HM: Dumping Short Stack of pid[37.67156] (sid:254, ser#:57166)
Short stack dump:
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-semtimedop()+14<-skgpwwait()+187<-ksliwat()+2218<-kslwaitctx()+188<-kqrget()+1574<-kqrLockPo()+246<-kqrpre2()+1117<-kqrpre()+35<-kewrgns_get_next_snaphdl()+115<-kerpiGetSnapshotId()+19<-kerpiSaveReport()+90<-kerpiReportFlushActionCb()+769<-kebmscw_slave_cb_wrapper()+21<-kebmiasc_invoke_action_switch_cb()+154<-kebmpsp_pdb_switch_private()+113<-kebm_slave_main()+800<-ksvrdp_int()+1941<-opirip()+583<-opidrv()+581<-sou2o()+165<-opimai_real()+173<-ssthrdmain()+417<-main()+256<-__libc_start_main()+243<-0x5541F689495641D7
HM: Current SQL: none
*** 2023-11-14T21:43:48.522177+05:30 (CDB$ROOT(1))
HM: Early Warning - Session ID 18 serial# 26731 OS PID 56573 (MMON)
is waiting on 'log file switch (archiving needed)' for 32 seconds, wait id 4573
p1: ''=0x0, p2: ''=0x0, p3: ''=0x0
Final Blocker is Session ID 10 serial# 3966 on instance 1
which is waiting on 'rdbms ipc message' for 0 seconds
p1: 'timeout'=0x10b, p2: ''=0x0, p3: ''=0x0
IO
Total Self- Total Total Outlr Outlr Outlr
Hung Rslvd Rslvd Wait WaitTm Wait WaitTm Wait
Sess Hangs Hangs Count Secs Count Secs Count Wait Event
------ ------ ------ ------ ------ ------ ------ ------ -----------
6 0 0 12 1344 11 1344 0 log file switch (archiving needed)Summary:
- Always focus on the final blocker rather than the initial blocker as they are the root blockers.