[20210527]enq RO - fast object reuse Final Blocker.txt

[20210527]enq RO - fast object reuse Final Blocker.txt

--//做一個enq RO - fast object reuse的等待事件的演示.一般出現這個主要是truncate drop物件,需要將髒塊寫盤.
In Wait Event: 'enq: RO - fast object reuse' and 'enq: KO - fast object checkpoint', CKPT is shown as
final_blocking_session when one DBWR is suspended.

1.環境:
SYS@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> @ tpt/bg DBW
NAME  DESCRIPTION                           SID       OPID SPID   PADDR            SADDR
----- ------------------------------ ---------- ---------- ------ ---------------- ----------------
DBW0  db writer process 0                   141         10 36443  00000000880D5E58 0000000087540E48
DBW1  db writer process 1                   155         11 36445  0000000087CD7658 0000000087547008
DBW2  db writer process 2                   169         12 36447  00000000878D6108 000000008754D1C8

2.設定suspend:
--//使用kill -19.
$ echo 36443 36445 36447 | xargs -n1 kill -19

--//session 1:
SCOTT@book> truncate table tx;

--//session 2:
SYS@book> @ wc
WAIT_EVENT_TEXT                        CHAIN_ID CHAIN_SIGNATURE                                                       SID       BSID FINAL_BSID PROGRAM                        SQL_ID        CLIENT_INFO
------------------------------------ ---------- -------------------------------------------------------------- ---------- ---------- ---------- ------------------------------ ------------- --------------------
rdbms ipc message                             1 'rdbms ipc message'<='enq: RO - fast object reuse'                    197                       oracle@xxxyyyy4 (CKPT)
enq: RO - fast object reuse                   1 'rdbms ipc message'<='enq: RO - fast object reuse'                     30        197        197 sqlplus@xxxyyyy4 (TNS V1-V3)   88431gbcnbvpk

WAIT_EVENT_TEXT                             SID       BSID FINAL_BSID PROGRAM                        SQL_ID        P1TEXT                       P1 P2TEXT                       P2 P3TEXT                       P3
------------------------------------ ---------- ---------- ---------- ------------------------------ ------------- -------------------- ---------- -------------------- ---------- -------------------- ----------
rdbms ipc message                           197                       oracle@xxxyyyy4 (CKPT)                       timeout                     100                               0                               0
enq: RO - fast object reuse                  30        197        197 sqlplus@xxxyyyy4 (TNS V1-V3)   88431gbcnbvpk name|mode            1380909062 2                         65562 0                             1

--//1380909062 = /2^16  %2^16 (Type | Mode) = 21071,6 = 0x524f0006
--//524f = RO 0006 表示模式6.
--//很奇怪最後的阻塞者sid＝197.對應CKPT。

3.為什麼Final Blocker是CKPT呢?

In both above tests, CKPT is shown as final_blocking_session, but in reality, that is DBW1 suspended. The reason is
probably because Foreground process only communicates with CKPT, and has no direct IPC call of DB WRITER processes.

4.取消掛起：
$ echo 36443 36445 36447 | xargs -n1 kill -18