欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20190416]exclusive latch测试脚本.txt

程序员文章站 2022-11-27 15:54:52
[20190416]exclusive latch测试脚本.txt--//昨天做了shared latch的测试脚本,今天完善exclusive latch测试脚本,上个星期的测试我是手工执行的.--//今天写一个脚本验证看看.相关链接:http://blog.itpub.net/267265/vi ......

[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之类的进程会做清理工作.