[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)

     

     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之類的程式會做清理工作.