[20210527]關於v$wait_chain.txt

[20210527]關於v$wait_chain.txt

--//我個人查詢阻塞和lock的情況,很少使用v$wait_chain檢視,自己上網查詢寫了一個指令碼,也許以後工作有用.

$ cat wc.sql
column WAIT_EVENT_TEXT format a30
column CHAIN_SIGNATURE format a62
column p1text format a20
column p2text format a20
column p3text format a20
column program format a30
--column p1 format a20
--column p2 format a20
--column p3 format a20

select c.wait_event_text, c.chain_id , c.chain_signature, c.sid, c.blocker_sid bsid, final_blocking_session final_bsid,
s.program, s.sql_id, s.client_info
from v$wait_chains c, v$session s
where c.sid = s.sid order by c.chain_id, s.program;

select c.wait_event_text, c.sid, c.blocker_sid bsid, s.final_blocking_session final_bsid,
       s.program, s.sql_id, s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3
from v$wait_chains c, v$session s
where c.sid = s.sid order by c.chain_id, s.program;

--//rac的情況估計還給改寫.
--//我以前喜歡使用ash_wait_chain.sql指令碼,執行如下,命令比較長,順便也做一個記錄:

@ tpt/ash/ash_wait_chains BLOCKING_SESSION||','||BLOCKING_SESSION_SERIAL#||',@'||BLOCKING_INST_ID||'=>'||session_id||','||SESSION_SERIAL#||',@'||inst_id||'=>'||event 1=1 sysdate-1/1440 sysdate

--//加入一些空格顯示更加直觀一些.

@ 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


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

2.簡單測試：
--//session 1:
SCOTT@book> select * from tx where rownum=1 for update;
...

SCOTT@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.33.35446

--//session 2:
SCOTT@book>  select * from tx where rownum=1 for update;

--//掛起。

3.觀察：
SCOTT@book> @ wc

WAIT_EVENT_TEXT                  CHAIN_ID CHAIN_SIGNATURE                                                       SID       BSID FINAL_BSID PROGRAM                        SQL_ID        CLIENT_INFO
------------------------------ ---------- -------------------------------------------------------------- ---------- ---------- ---------- ------------------------------ ------------- --------------------
SQL*Net message from client             1 'SQL*Net message from client'<='enq: TX - row lock contention'         30                       sqlplus@gxqyydg4 (TNS V1-V3)
enq: TX - row lock contention           1 'SQL*Net message from client'<='enq: TX - row lock contention'         44         30         30 sqlplus@gxqyydg4 (TNS V1-V3)   dp85vk7dt1fav

WAIT_EVENT_TEXT                       SID       BSID FINAL_BSID PROGRAM                        SQL_ID        P1TEXT                       P1 P2TEXT                       P2 P3TEXT                       P3
------------------------------ ---------- ---------- ---------- ------------------------------ ------------- -------------------- ---------- -------------------- ---------- -------------------- ----------
SQL*Net message from client            30                       sqlplus@gxqyydg4 (TNS V1-V3)                 driver id            1650815232 #bytes                        1                               0
enq: TX - row lock contention          44         30         30 sqlplus@gxqyydg4 (TNS V1-V3)   dp85vk7dt1fav name|mode            1415053318 usn<<16 | slot           655393 sequence                  35446

--//1415053318 = /2^16  %2^16 (Type | Mode) = 21592,6 = 0x54580006
--//655393 = /2^16  %2^16 (Type | Mode) = 10,33 = 0xa0021
--//與前面的xid輸出能對上。

SCOTT@book> @ sql_id dp85vk7dt1fav
SQL_ID        SQLTEXT
------------- --------------------------------------------
dp85vk7dt1fav  select * from tx where rownum=1 for update
--//注sql_id 看到的未必是阻塞的sql語句。

SCOTT@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%          60          1 -> 30,15,@1 => 44,9,@1 => enq: TX - row lock contention

--//感覺還是大師寫的指令碼好用。