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