[20220311]完善ash_wait_chains指令碼.txt

[20220311]完善ash_wait_chains指令碼.txt

--//以前寫的指令碼：http://blog.itpub.net/267265/viewspace-2841682/ ->[20211111]補充完善ash_wait_chains指令碼.txt
--//發現一些小問題。

SYS@book> @wcy "timestamp'2022-03-11 17:06:20'" sysdate "event='enq: TX - row lock contention'"

-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
SP2-0552: Bind variable "06" not declared.

--//如果有空格解析不對，修改如下,加入雙引號：
$ cat wcx.sql
@ tpt/ash/ash_wait_chains2 BLOCKING_SESSION||','||BLOCKING_SESSION_SERIAL#||',@'||BLOCKING_INST_ID||'=>'||session_id||','||SESSION_SERIAL#||',@'||inst_id||'=>'||event 1=1  "&&1" "&&2"

$ cat wcy.sql
@ tpt/ash/ash_wait_chains BLOCKING_SESSION||','||BLOCKING_SESSION_SERIAL#||',@'||BLOCKING_INST_ID||'=>'||session_id||','||SESSION_SERIAL#||',@'||inst_id||'=>'||event "&&3"  &&1 "&&2"

SYS@book> @wcy "timestamp'2022-03-11 17:06:20'" sysdate "event='enq: TX - row lock contention'"
-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS #Blkrs WAIT_CHAIN                                                                                        FIRST_SEEN          LAST_SEEN
------ ---------- ---------- ------ ------------------------------------------------------------------------------------------------- ------------------- -------------------
   0%           7          0      1 -> 125,15,@1=>138,17,@1=>enq: TX - row lock contention -> ,,@=>125,15,@1=>buffer busy waits       2022-03-11 17:24:54 2022-03-11 17:30:23
   0%           6          0      1 -> 189,11,@1=>240,11,@1=>enq: TX - row lock contention -> ,,@=>189,11,@1=>buffer busy waits       2022-03-11 17:24:44 2022-03-11 17:29:38
   0%           5          0      1 -> 191,9,@1=>22,17,@1=>enq: TX - row lock contention -> ,,@=>191,9,@1=>buffer busy waits          2022-03-11 17:24:35 2022-03-11 17:27:39
   0%           5          0      1 -> 343,19,@1=>363,9,@1=>enq: TX - row lock contention -> ,,@=>343,19,@1=>buffer busy waits        2022-03-11 17:25:17 2022-03-11 17:29:32
   0%           5          0      1 -> 57,15,@1=>363,9,@1=>enq: TX - row lock contention -> ,,@=>57,15,@1=>buffer busy waits          2022-03-11 17:24:30 2022-03-11 17:30:40
   0%           5          0      1 -> 39,19,@1=>54,1297,@1=>enq: TX - row lock contention -> ,,@=>39,19,@1=>buffer busy waits        2022-03-11 17:25:08 2022-03-11 17:28:46
   0%           5          0      1 -> 71,9,@1=>225,9,@1=>enq: TX - row lock contention -> ,,@=>71,9,@1=>buffer busy waits            2022-03-11 17:27:06 2022-03-11 17:28:49
   0%           5          0      1 -> 310,9,@1=>378,17,@1=>enq: TX - row lock contention -> ,,@=>310,9,@1=>buffer busy waits         2022-03-11 17:25:07 2022-03-11 17:29:33
   0%           5          0      1 -> 73,19,@1=>173,9,@1=>enq: TX - row lock contention -> ,,@=>73,19,@1=>buffer busy waits          2022-03-11 17:27:03 2022-03-11 17:30:42
   0%           5          0      1 -> 22,17,@1=>379,17,@1=>enq: TX - row lock contention -> ,,@=>22,17,@1=>buffer busy waits         2022-03-11 17:24:34 2022-03-11 17:30:35
   0%           5          0      1 -> 395,9,@1=>360,13,@1=>enq: TX - row lock contention -> ,,@=>395,9,@1=>buffer busy waits         2022-03-11 17:24:37 2022-03-11 17:30:37
   0%           5          0      1 -> 107,19,@1=>38,11,@1=>enq: TX - row lock contention -> ,,@=>107,19,@1=>buffer busy waits        2022-03-11 17:24:36 2022-03-11 17:28:16
   0%           5          0      1 -> 343,19,@1=>293,17,@1=>enq: TX - row lock contention -> ,,@=>343,19,@1=>buffer busy waits       2022-03-11 17:25:59 2022-03-11 17:30:23
   0%           5          0      1 -> 257,8563,@1=>18,5351,@1=>enq: TX - row lock contention -> ,,@=>257,8563,@1=>buffer busy waits  2022-03-11 17:24:44 2022-03-11 17:30:40
   0%           5          0      1 -> 73,19,@1=>258,17,@1=>enq: TX - row lock contention -> ,,@=>73,19,@1=>buffer busy waits         2022-03-11 17:24:52 2022-03-11 17:28:38
   0%           5          0      1 -> 157,9,@1=>90,15,@1=>enq: TX - row lock contention -> ,,@=>157,9,@1=>buffer busy waits          2022-03-11 17:26:39 2022-03-11 17:30:38
   0%           5          0      1 -> 377,9,@1=>190,15,@1=>enq: TX - row lock contention -> ,,@=>377,9,@1=>buffer busy waits         2022-03-11 17:25:03 2022-03-11 17:29:49
   0%           5          0      1 -> 126,9,@1=>191,9,@1=>enq: TX - row lock contention -> ,,@=>126,9,@1=>buffer busy waits          2022-03-11 17:25:25 2022-03-11 17:30:39
   0%           5          0      1 -> 395,9,@1=>73,19,@1=>enq: TX - row lock contention -> ,,@=>395,9,@1=>buffer busy waits          2022-03-11 17:24:51 2022-03-11 17:30:37
   0%           5          0      1 -> 395,9,@1=>5,17,@1=>enq: TX - row lock contention -> ,,@=>395,9,@1=>buffer busy waits           2022-03-11 17:25:28 2022-03-11 17:29:53
   0%           5          0      1 -> 57,15,@1=>310,9,@1=>enq: TX - row lock contention -> ,,@=>57,15,@1=>buffer busy waits          2022-03-11 17:24:39 2022-03-11 17:30:35
   0%           5          0      1 -> 258,17,@1=>174,17,@1=>enq: TX - row lock contention -> ,,@=>258,17,@1=>buffer busy waits       2022-03-11 17:26:18 2022-03-11 17:29:15
   0%           5          0      1 -> 20,17,@1=>344,9,@1=>enq: TX - row lock contention -> ,,@=>20,17,@1=>buffer busy waits          2022-03-11 17:24:42 2022-03-11 17:29:53
   0%           5          0      1 -> 362,17,@1=>257,8563,@1=>enq: TX - row lock contention -> ,,@=>362,17,@1=>buffer busy waits     2022-03-11 17:26:15 2022-03-11 17:29:52
   0%           4          0      1 -> 226,17,@1=>106,17,@1=>enq: TX - row lock contention -> ,,@=>226,17,@1=>buffer busy waits       2022-03-11 17:24:37 2022-03-11 17:29:35
   0%           4          0      1 -> 189,11,@1=>87,367,@1=>enq: TX - row lock contention -> ,,@=>189,11,@1=>buffer busy waits       2022-03-11 17:24:27 2022-03-11 17:30:38
   0%           4          0      1 -> 106,17,@1=>71,9,@1=>enq: TX - row lock contention -> ,,@=>106,17,@1=>buffer busy waits         2022-03-11 17:29:06 2022-03-11 17:30:09
   0%           4          0      1 -> 107,19,@1=>123,23,@1=>enq: TX - row lock contention -> ,,@=>107,19,@1=>buffer busy waits       2022-03-11 17:24:43 2022-03-11 17:26:44
   0%           4          0      1 -> 173,9,@1=>209,17,@1=>enq: TX - row lock contention -> ,,@=>173,9,@1=>buffer busy waits         2022-03-11 17:27:08 2022-03-11 17:29:44
30 rows selected.