[20210527]enq KO - fast object checkpoint Final Blocker.txt

[20210527]enq KO - fast object checkpoint  Final Blocker.txt

--//做一個enq KO - fast object checkpoint的等待事件的演示.一般出現這個主要是直接路徑讀之前,需要將髒塊寫盤.
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.環境:
SCOTT@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/bg2 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

$ kill -l
 1) SIGHUP       2) SIGINT       3) SIGQUIT      4) SIGILL
 5) SIGTRAP      6) SIGABRT      7) SIGBUS       8) SIGFPE
 9) SIGKILL     10) SIGUSR1     11) SIGSEGV     12) SIGUSR2
13) SIGPIPE     14) SIGALRM     15) SIGTERM     16) SIGSTKFLT
17) SIGCHLD     18) SIGCONT     19) SIGSTOP     20) SIGTSTP
21) SIGTTIN     22) SIGTTOU     23) SIGURG      24) SIGXCPU
25) SIGXFSZ     26) SIGVTALRM   27) SIGPROF     28) SIGWINCH
29) SIGIO       30) SIGPWR      31) SIGSYS      34) SIGRTMIN
35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3  38) SIGRTMIN+4
39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8
43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12
47) SIGRTMIN+13 48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14
51) SIGRTMAX-13 52) SIGRTMAX-12 53) SIGRTMAX-11 54) SIGRTMAX-10
55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7  58) SIGRTMAX-6
59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2
63) SIGRTMAX-1  64) SIGRTMAX
--//19表示SIGSTOP，也就是stop. 18 表示SIGCONT ，也就是繼續。

SCOTT@book> create table tx as select * from all_objects;
SCOTT@book> insert into tx  select  * from tx ;
84825 rows created.
SCOTT@book> commit ;
Commit complete.

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

SCOTT@book> select /*+ parallel(tx 4) */ count(*) from tx ;

--//掛起。

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: KO - fast object checkpoint'               197                       oracle@xxxyyyy4 (CKPT)
enq: KO - fast object checkpoint              1 'rdbms ipc message'<='enq: KO - fast object checkpoint'                30        197        197 sqlplus@xxxyyyy4 (TNS V1-V3)   713nq1zdqjdus

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: KO - fast object checkpoint             30        197        197 sqlplus@xxxyyyy4 (TNS V1-V3)   713nq1zdqjdus name|mode            1263468550 2                         65562 0                             1

--//1263468550 = /2^16  %2^16 (Type | Mode) = 19279,6 = 0x4b4f0006
--//4b4f = KO  0006 表示模式6.
--//很奇怪最後的阻塞者sid＝197.

SYS@book> @tpt/ash/ash_wait_chains "blocking_session||','||blocking_session_serial#||',@'||blocking_inst_id||' => '||session_id||','||session_serial#||',@'||inst_id||' => '||event2" 1=1 sysdate-1/1440 sysdate

-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- -----------------------------------------------------------
 100%          35         .6 -> 197,1,@1 => 30,27,@1 => enq: KO - fast object checkpoint


SYS@book> @ sid 197
sid = 197
SPID       PID        SID    SERIAL# CLIENT_INFO          PNAME  TRACEFILE                                                         PROGRAM                                  TERMINAL     SQL_ID        STATUS   C50
------ ------- ---------- ---------- -------------------- ------ ----------------------------------------------------------------- ---------------------------------------- ------------ ------------- -------- --------------------------------------------------
36451       14        197          1                      CKPT   /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_36451.trc    oracle@xxxyyyy4 (CKPT)                   UNKNOWN                    ACTIVE   alter system kill session '197,1' immediate;
--//對應程式確是CKPT程式。

SYS@book> @ tpt/bg2 ckpt
NAME     DESCRIPTION   SID       OPID SPID   PADDR            SADDR
-------- ------------ ---- ---------- ------ ---------------- ----------------
CKPT     checkpoint    197         14 36451  00000000870D3FD8 0000000087559548

3.為什麼Final Blocker是CKPT呢?

In both above tests, CKPT is shown as final_blocking_session, but in reality, that is DBWn 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