ITPub博客

首页 > 数据库 > Oracle > [20190416]完善shared latch测试脚本2.txt

[20190416]完善shared latch测试脚本2.txt

原创 Oracle 作者:lfree 时间:2019-04-16 10:53:12 0 删除 编辑

[20190416]完善shared latch测试脚本2.txt


--//昨天测试shared latch,链接:http://blog.itpub.net/267265/viewspace-2641414/,感觉有点开窍了.^_^.


http://andreynikolaev.wordpress.com/2010/11/17/shared-latch-behaves-like-enqueue/


For the shared latches Oracle 10g uses kslgetsl(laddr, wait, why, where, mode) function. Oracle 11g has kslgetsl_w()

function with the same interface, but internally uses ksl_get_shared_latch(). Like in my previous post, I guess the

meaning of kslgetsl() arguments as:


--//对于共享锁存,Oracle 10g使用kslgetsl(laddr,wait,why,where,mode)函数。Oracle 11g具有相同接口的kslgetsl_w()函数,但

--//在内部使用ksl_get_share_latch()。与上一篇文章一样,我认为kslgetsl()参数的含义是:

--//注:我以前一直以为还是kslgetsl,原来11g已经改为kslgetsl_w,不过内部使用还是ksl_get_shared_latch().


    laddress -- address of latch in SGA

    wait     -- flag. If not 0, then willing-to-wait latch get

    why      -- context why the latch is acquired at this where.

    where    -- location from where the latch is acquired (x$ksllw.indx)


And the last one is:


    mode – Exclusive or shared mode


the mode argument took only two values:

     8 -- "SHARED"

    16 -- "EXCLUSIVE"


--//我当时的测试针对'gcs partitioned table hash'  latch,完善修改测试脚本,增加一些通用性.


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 shared_latch.txt

/* 参数如下: @ latch.txt latch_name willing why where mode sleep_num */

connect / as sysdba

col laddr new_value laddr

col vmode  new_value vmode

select decode(lower('&&5'),'s',8,'x',16,'8',8,'16',16) vmode from dual ;

SELECT addr laddr FROM v$latch_parent WHERE NAME='&&1';

oradebug setmypid

oradebug call kslgetsl_w 0x&laddr &&2 &&3 &&4  &vmode

host sleep &&6

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;


$ cat /usr/local/bin/timestamp.pl

#!/usr/bin/perl

while (<>) {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();

printf("%02d:%02d:%02d", $hour, $min, $sec);

print  ": $_";

#print localtime() . ": $_";

}

--//使用timestamp.pl在开始标注时间.这样更加清晰.


2.测试:

--//补充测试 X mode,S mode ,X 模式的情况.

$ cat f1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 s 6 > /dev/null &

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 s 6 > /dev/null &

sleep 0.1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 x 6 > /dev/null &

wait


$ grep  -v '^.*: $' /tmp/peeks.txt

09:28:38: SYSDATE             LADDR

09:28:38: ------------------- ----------------

09:28:38: 2019-04-16 09:28:38 0000000060018A18

09:28:38: Statement processed.

09:28:38: [060018A18, 060018A20) = 0000001C 20000000

09:28:39: [060018A18, 060018A20) = 0000001C 20000000

09:28:40: [060018A18, 060018A20) = 0000001C 20000000

09:28:41: [060018A18, 060018A20) = 0000001C 20000000

09:28:42: [060018A18, 060018A20) = 0000001C 20000000

09:28:43: [060018A18, 060018A20) = 0000001C 20000000

09:28:44: [060018A18, 060018A20) = 00000001 00000000

09:28:45: [060018A18, 060018A20) = 00000001 00000000

09:28:46: [060018A18, 060018A20) = 00000001 00000000

09:28:47: [060018A18, 060018A20) = 00000001 00000000

09:28:48: [060018A18, 060018A20) = 00000001 00000000

09:28:49: [060018A18, 060018A20) = 00000001 00000000

09:28:50: [060018A18, 060018A20) = 00000001 00000000

09:28:51: [060018A18, 060018A20) = 00000001 00000000

09:28:52: [060018A18, 060018A20) = 00000001 00000000

09:28:53: [060018A18, 060018A20) = 00000001 00000000

09:28:54: [060018A18, 060018A20) = 00000001 00000000

09:28:55: [060018A18, 060018A20) = 00000001 00000000

09:28:56: [060018A18, 060018A20) = 0000001F 20000000

09:28:57: [060018A18, 060018A20) = 0000001F 20000000

09:28:58: [060018A18, 060018A20) = 0000001F 20000000

09:28:59: [060018A18, 060018A20) = 0000001F 20000000

09:29:00: [060018A18, 060018A20) = 0000001F 20000000

09:29:01: [060018A18, 060018A20) = 0000001F 20000000

09:29:02: [060018A18, 060018A20) = 00000000 00000000

09:29:03: [060018A18, 060018A20) = 00000000 00000000

09:29:04: [060018A18, 060018A20) = 00000000 00000000

09:29:05: [060018A18, 060018A20) = 00000000 00000000

09:29:06: [060018A18, 060018A20) = 00000000 00000000

09:29:07: [060018A18, 060018A20) = 00000000 00000000


$ grep  -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 09:28:38 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A20) = 0000001C 20000000

     12  [060018A18, 060018A20) = 00000001 00000000

      6  [060018A18, 060018A20) = 0000001F 20000000

      6  [060018A18, 060018A20) = 00000000 00000000

--//仅仅注意一个细节,peek值 的后4位并没有出现0x40000000的情况.


$ cat /tmp/latch_free.txt

2019-04-16 09:28:38

2019-04-16 09:28:39

Process 28

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58


2019-04-16 09:28:40

Process 28

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

  Process 29, waiting for: 0000000060018A18 whr=5 why=4

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:41

Process 28

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

  Process 29, waiting for: 0000000060018A18 whr=5 why=4

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:42

Process 28

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

  Process 29, waiting for: 0000000060018A18 whr=5 why=4

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:44

Process 28

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

  Process 29, waiting for: 0000000060018A18 whr=5 why=4

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:45

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=72

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:46

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=72

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:47

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=72

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:48

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=72

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:49

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=72

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:50

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=72

  Process 30, waiting for: 0000000060018A18 whr=5 why=4

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:51

Process 30

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=86

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:52

Process 30

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=86

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:53

Process 30

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=86

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:54

Process 30

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=86

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:56

Process 30

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=86

  Process 31, waiting for: 0000000060018A18 whr=5 why=4


2019-04-16 09:28:57

Process 31

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101


2019-04-16 09:28:58

Process 31

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101


2019-04-16 09:28:59

Process 31

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101


2019-04-16 09:29:00

Process 31

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101


2019-04-16 09:29:01

Process 31

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101


2019-04-16 09:29:02

Process 31

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101


2019-04-16 09:29:03

2019-04-16 09:29:04


--//结果我就不再讲解了.

--//不过有点奇怪的是,如果修改f1.sh如下:


$ cat f1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   s 6 > /dev/null &

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  s 6 > /dev/null &

##sleep 0.1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 x 6 > /dev/null &

wait

--//注解sleep 0.01秒.结果如下:


$ grep  -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 09:33:37 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A20) = 0000001D 20000000

      6  [060018A18, 060018A20) = 0000001F 20000000

     12  [060018A18, 060018A20) = 00000001 00000000

      6  [060018A18, 060018A20) = 00000000 00000000

--//给人的感觉优先处理X mode 锁,然后才是S mode.我测试多次结果都一样.如果修改如下:

$ cat f1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   s 6 > /dev/null &

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 x 6 > /dev/null &

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  s 6 > /dev/null &

##sleep 0.1

wait


$ grep  -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 09:37:56 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A20) = 0000001B 20000000

      6  [060018A18, 060018A20) = 00000001 00000000

      6  [060018A18, 060018A20) = 0000001E 20000000

      6  [060018A18, 060018A20) = 00000001 00000000

      6  [060018A18, 060018A20) = 00000000 00000000

--//仅仅注意一个细节,peek值 的后4位并没有出现0x40000000的情况.  


--//latch_free.txt

2019-04-16 09:37:56

2019-04-16 09:37:57

Process 27

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=44


2019-04-16 09:37:58

Process 27

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=44

  Process 29, waiting for: 0000000060018A18 whr=10 why=9

  Process 30, waiting for: 0000000060018A18 whr=12 why=11

  Process 31, waiting for: 0000000060018A18 whr=8 why=7


--//同时执行的sql语句,总是最后1个先启动执行.


2019-04-16 09:37:59

Process 27

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=44

  Process 29, waiting for: 0000000060018A18 whr=10 why=9

  Process 30, waiting for: 0000000060018A18 whr=12 why=11

  Process 31, waiting for: 0000000060018A18 whr=8 why=7


2019-04-16 09:38:01

Process 27

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=44

  Process 29, waiting for: 0000000060018A18 whr=10 why=9

  Process 30, waiting for: 0000000060018A18 whr=12 why=11

  Process 31, waiting for: 0000000060018A18 whr=8 why=7


2019-04-16 09:38:02

Process 27

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=44

  Process 29, waiting for: 0000000060018A18 whr=10 why=9

  Process 30, waiting for: 0000000060018A18 whr=12 why=11

  Process 31, waiting for: 0000000060018A18 whr=8 why=7


2019-04-16 09:38:03

Process 29

 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=10 why=9, SID=72

  Process 30, waiting for: 0000000060018A18 whr=12 why=11

  Process 31, waiting for: 0000000060018A18 whr=8 why=7


--//总之,有了这些脚本大家可以自行组合测试.我仅仅测试

--//SSS XSS SXS XXX

--//这里算是XSX,是否后4位出现的规律与第1次持有的mode是shared还是EXCLUSIVE有关.


--//视乎peek看到的值与入队时当前持有的状态shared,exclusive有关.

$ cat g1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  x 6 > /dev/null &

wait


$ grep  -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 09:48:14 0000000060018A18

      1  Statement processed.

      2  [060018A18, 060018A20) = 00000001 00000000

      4  [060018A18, 060018A20) = 00000001 40000000

      6  [060018A18, 060018A20) = 0000001D 20000000

      6  [060018A18, 060018A20) = 0000001E 20000000

     12  [060018A18, 060018A20) = 00000000 00000000


--//这样出现后4位是0x40000000好像仅仅一种可能,就是当前持有S mode,入队X mode时才会出现这样的情况.

--//再做一个例子:


$ cat g1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  x 6 > /dev/null &

sleep 1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 x 6 > /dev/null &

wait


$ grep  -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 09:58:45 0000000060018A18

      1  Statement processed.

      2  [060018A18, 060018A20) = 00000001 00000000

      2  [060018A18, 060018A20) = 00000002 00000000

      2  [060018A18, 060018A20) = 00000002 40000000

      2  [060018A18, 060018A20) = 00000001 40000000

      6  [060018A18, 060018A20) = 0000001E 20000000

      6  [060018A18, 060018A20) = 0000001F 20000000

     10  [060018A18, 060018A20) = 00000000 00000000


--//出现2次后4位是0x40000000的情况.可以理解这样模式持有S mode的情况下,有X mode入队,才会出现这样的情况.


$ cat h1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5  x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 6 7  s 6 > /dev/null &

sleep 4.1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10 x 6 > /dev/null &

wait


$ grep  -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 10:11:26 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A20) = 0000001C 20000000

      1  [060018A18, 060018A20) = 00000001 00000000

      5  [060018A18, 060018A20) = 00000001 40000000

      6  [060018A18, 060018A20) = 0000001C 20000000

     12  [060018A18, 060018A20) = 00000000 00000000


--//出现1次.最后我感觉脚本写的还是不好,每次都覆盖前面的测试结果.加入时间变量,修改如下:


$ cat g1.sh

#! /bin/bash

zdate=$(date '+%H%M%S')

echo $zdate

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks_${zdate}.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  s 6 > /dev/null &

sleep 2.1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 x 6 > /dev/null &

wait


--//大家可以自行组合,peek看到的值也许不重要,只要知道请求都是S mode下不会阻塞.

--//X模式下,请求的S模式都会导致串行化.同时S mode也会阻塞X mode就足够了.最后测试一种情况看看:


$ cat i1.sh

#! /bin/bash

zdate=$(date '+%H%M%S')

echo $zdate

source peek.sh 'gcs partitioned table hash' 36 | timestamp.pl >| /tmp/peeks_${zdate}.txt &

seq 36 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  s 6 > /dev/null &

sleep 2.1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 s 6 > /dev/null &

wait


$ grep  -v '^.*: $' /tmp/peeks_102719.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 10:27:19 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A20) = 0000001C 20000000

      1  [060018A18, 060018A20) = 00000001 00000000

      6  [060018A18, 060018A20) = 00000002 00000000

      5  [060018A18, 060018A20) = 00000001 00000000

     18  [060018A18, 060018A20) = 00000000 00000000


--//可以最后请求S mode 没有阻塞,需要18秒完成.如果修改如下:

$ cat i1.sh

#! /bin/bash

zdate=$(date '+%H%M%S')

echo $zdate

source peek.sh 'gcs partitioned table hash' 36 | timestamp.pl >| /tmp/peeks_${zdate}.txt &

seq 36 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt &

# 参数如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5   x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8   s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10  s 6 > /dev/null &

sleep 1.9

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 s 6 > /dev/null &

wait


$ grep  -v '^.*: $' /tmp/peeks_103201.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 10:32:01 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A20) = 0000001C 20000000

     18  [060018A18, 060018A20) = 00000001 00000000

     12  [060018A18, 060018A20) = 00000000 00000000


--//可以发现我仅仅修改sleep 1.9秒,就导致后面3个S mode串行化.需要24秒完成.一旦串行化就很慢.

--//有点想作者说的那样shared latch like enquence.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2641497/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2421
  • 访问量
    6191388