[20190416]exclusive latch测试脚本.txt

[20190416]exclusive latch测试脚本.txt --//昨天做了shared latch的测试脚本,今天完善exclusive latch测试脚本,上个星期的测试我是手工执行的. --//今天写一个脚本验证看看.相关链接: http://blog.itpub.net/267265/viewspace-2641414/ => [20190415]关于shared latch(共享栓锁).txt http://blog.itpub.net/267265/viewspace-2641497/ => [20190416]完善shared latch测试脚本2.txt 1.环境: SYS@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 $ cat peek.sh #! /bib/bash # 参数如下:latch_name Monitoring_duration sqlplus -s -l / as sysdba <<EOF col laddr new_value laddr SELECT sysdate,addr laddr FROM v\$latch_parent WHERE NAME='$1'; oradebug setmypid $(seq $2|xargs -I{} echo -e 'oradebug peek 0x&laddr 8\nhost sleep 1' ) EOF $ cat exclusive_latch.txt /* 参数如下: @ latch.txt latch_name willing why where sleep_num */ connect / as sysdba col laddr new_value laddr SELECT addr laddr FROM v$latch_parent WHERE NAME='&&1'; oradebug setmypid oradebug call kslgetl 0x&laddr &&2 &&3 &&4 host sleep &&5 oradebug call kslfre 0x&laddr exit $ cat latch_free.sql /*      This file is part of demos for "Contemporary Latch Internals" seminar v.18.09.2010      Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)      http://AndreyNikolaev.wordpress.com      This query shows trees of processes currently holding and waiting for latches      Tree output enumerates these processes and latches as following: Process <PID1>  <latch1 holding by PID1>     <processes waiting for latch1>        ...  <latch2 holding by PID1>     <processes waiting for latch2>        ... Process <PID2> ... */ set head off set feedback off set linesize 120 select sysdate from dual; select   LPAD(' ', (LEVEL - 1) )      ||case when latch_holding is null then 'Process '||pid              else 'holding: '||latch_holding||'  "'||name||'" lvl='||level#||' whr='||whr||' why='||why ||', SID='||sid        end      || case when latch_waiting  is not  null then ', waiting for: '||latch_waiting||' whr='||whr||' why='||why        end latchtree  from ( /* Latch holders */ select ksuprpid pid,ksuprlat latch_holding, null latch_waiting, to_char(ksuprpid) parent_id, rawtohex(ksuprlat) id,        ksuprsid sid,ksuprllv level#,ksuprlnm name,ksuprlmd mode_,ksulawhy why,ksulawhr whr  from x$ksuprlat union all /* Latch waiters */ select indx pid,null latch_holding, ksllawat latch_waiting,rawtohex(ksllawat) parent_id,to_char(indx) id,        null,null,null,null,ksllawhy why,ksllawer whr from x$ksupr where ksllawat !='00' union all /*  The roots of latch trees: processes holding latch but not waiting for latch */ select pid, null, null, null, to_char(pid),null,null,null,null,null,null from ( select distinct ksuprpid pid  from x$ksuprlat minus select indx pid from x$ksupr where ksllawat !='00') ) latch_op connect by prior id=parent_id start with parent_id  is null; 2.测试: --//上个星期测试的是'test excl. parent2 l0',继续拿它测试. $ cat  x1.sh #! /bin/bash zdate=$(date '+%H%M%S') echo $zdate source peek.sh 'test excl. parent2 l0' 20 | timestamp.pl >| /tmp/peekx_${zdate}.txt & seq 20 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_freeo_${zdate}.txt & # 参数如下: @ exclusive_latch.txt latch_name willing why where sleep_num sqlplus /nolog @ exclusive_latch.txt 'test excl. parent2 l0' 1 2 3 6 > /dev/null & sleep 2 sqlplus /nolog @ exclusive_latch.txt 'test excl. parent2 l0' 1 4 5 6 > /dev/null & sleep 2 sqlplus /nolog @ exclusive_latch.txt 'test excl. parent2 l0' 1 6 7 6 > /dev/null & wait $ grep  -v '^.*: $' /tmp/peekx_111121.txt | cut -c10- | uniq -c       1  SYSDATE             LADDR       1  ------------------- ----------------       1  2019-04-16 11:11:21 0000000060009978       1  Statement processed.       6  [060009978, 060009980) = 00000023 00000000       6  [060009978, 060009980) = 00000024 00000000       6  [060009978, 060009980) = 00000025 00000000       2  [060009978, 060009980) = 00000000 00000000 --//exclusive latch 是排他的,必须等其释放,其它进程才可以持有. --//做一个测试,如果一个会话长时间持有已经死掉的情况下,oracle如何处理.  $ cat x2.sh #! /bin/bash zdate=$(date '+%H%M%S') echo $zdate source peek.sh 'test excl. parent2 l0' 320 | timestamp.pl >| /tmp/peekx_${zdate}.txt & seq 320 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt & # 参数如下: @ exclusive_latch.txt latch_name willing why where sleep_num sqlplus /nolog @ exclusive_latch.txt 'test excl. parent2 l0' 1 2 3 300 > /dev/null & sleep 2 sqlplus /nolog @ exclusive_latch.txt 'test excl. parent2 l0' 1 4 5 6 > /dev/null & sleep 2 sqlplus /nolog @ exclusive_latch.txt 'test excl. parent2 l0' 1 6 7 6 > /dev/null & wait --//执行x2.sh $ . x2.sh 112841 .. $ pstree -ap ...   |   |   |-sqlplus,5545 \040\040\040\040\040\040 @ exclusive_latch.txt test\040excl.\040parent2\040l0 1 2 3 300   |   |   |   |-oracle,5595 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))   |   |   |   `-sleep,5726 300 $ kill -9 5595 5545 $ grep  -v '^.*: $' /tmp/peekx_112841.txt | cut -c10- | uniq -c       1  SYSDATE             LADDR       1  ------------------- ----------------       1  2019-04-16 11:28:42 0000000060009978       1  Statement processed.     107  [060009978, 060009980) = 0000001B 00000000       6  [060009978, 060009980) = 0000001C 00000000       6  [060009978, 060009980) = 0000001E 00000000     201  [060009978, 060009980) = 00000000 00000000 --//kill后后续的会话持有没有问题.估计pmon之类的进程会做清理工作.