[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 --//感觉还是大师写的脚本好用。