19.15 on AIX, single instance
The sequence of events:
A user started running Create Table as Select in session 11909. The query is slow, the session is waiting on 'db file sequential read'
A few minutes later the same user started running Create table in session 596, table name is the same as in session 11909. This session waits on 'row cache lock', this is expected behaviour.
More than 24 hours later other sessions start experiencing waits on 'library cache lock' and 'cursor: pin S wait on X'.
Hang Manager correctly identified session 11909 as Final Blocker, this is shown in dia0 LWS trace files. However it did't terminate the session
This continuesd for about 8 hours, the number of sessions waiting on 'library cache lock' and 'cursor: pin S wait on X' exceeded 300. Eventually we manually terminated 11909.
I expected that in this situation Hang Manager would terminate session 11909 and/or 596. Is there a tuning parameter (other than setting sensitivity to High?) that controls the behaviour in such situation?
From dia0 LWS trace file:
*** 2022-07-01T02:27:24.937000+10:00
HM: Session with ID 1909 serial # 46854 (FG) on single instance 1 is hung
and is waiting on 'library cache lock' for 97 seconds.
Session was previously waiting on 'SQL*Net message from client'.
Final Blocker is Session ID 11909 serial# 53798 on instance 1
which is waiting on 'db file sequential read' for 0 seconds, wait id 115901409
p1: 'file#'=0xa5, p2: 'block#'=0x13044e, p3: 'blocks'=0x1
Session ID 1909 is blocking 1 session
*** 2022-07-01T02:27:24.937780+10:00
HM: Session with ID 2043 serial # 30730 (FG) on single instance 1 is hung
and is waiting on 'library cache lock' for 96 seconds.
Session was previously waiting on 'library cache: mutex X'.
Final Blocker is Session ID 11909 serial# 53798 on instance 1
which is waiting on 'db file sequential read' for 0 seconds, wait id 115901409
p1: 'file#'=0xa5, p2: 'block#'=0x13044e, p3: 'blocks'=0x1
Session ID 2043 is blocking 1 session
*** 2022-07-01T02:27:24.938156+10:00
HM: Session with ID 2065 serial # 51414 (FG) on single instance 1 is hung
and is waiting on 'library cache lock' for 97 seconds.
Session was previously waiting on 'db file sequential read'.
Final Blocker is Session ID 11909 serial# 53798 on instance 1
which is waiting on 'db file sequential read' for 0 seconds, wait id 115901409
p1: 'file#'=0xa5, p2: 'block#'=0x13044e, p3: 'blocks'=0x1
*** 2022-07-01T02:27:24.942411+10:00
HM: Session with ID 4218 serial # 50476 (FG) on single instance 1 is hung
and is waiting on 'library cache lock' for 97 seconds.
Session was previously waiting on 'SQL*Net message from client'.
Final Blocker is Session ID 11909 serial# 53798 on instance 1
which is waiting on 'db file sequential read' for 0 seconds, wait id 115901409
p1: 'file#'=0xa5, p2: 'block#'=0x13044e, p3: 'blocks'=0x1
Session ID 4218 is blocking 1 session
*** 2022-07-01T02:27:24.961630+10:00
HM: Session with ID 6539 serial # 859 (FG) on single instance 1 is hung
and is waiting on 'library cache lock' for 96 seconds.
Session was previously waiting on 'db file sequential read'.
Final Blocker is Session ID 11909 serial# 53798 on instance 1
which is waiting on 'db file sequential read' for 0 seconds, wait id 115901409
p1: 'file#'=0xa5, p2: 'block#'=0x13044e, p3: 'blocks'=0x1
*** 2022-07-01T02:27:25.002719+10:00
HM: Session with ID 8707 serial # 3856 (FG) on single instance 1 is hung
and is waiting on 'library cache lock' for 98 seconds.
Session was previously waiting on 'db file sequential read'.
Final Blocker is Session ID 11909 serial# 53798 on instance 1
which is waiting on 'db file sequential read' for 0 seconds, wait id 115901409
p1: 'file#'=0xa5, p2: 'block#'=0x13044e, p3: 'blocks'=0x1
Session ID 8707 is blocking 485 sessions