[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