[20190415]关于shared latch(共享栓锁).txt

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
    where    -- location from where the latch is acquired (x$ksllw.indx)
    why      -- context why the latch is acquired at this where.

And the last one is:

mode – Exclusive or shared mode

the mode argument took only two values:
     8 -- "SHARED"
    16 -- "EXCLUSIVE"

--//我觉得在不理解之前,最好的方法拿别人的例子自己亲自做一遍.慢慢体会与理解.

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

SYS@book> @ laddr.sql 'gcs partitioned table hash'
old   1: select addr, name from v$latch where lower(name) like '%'||lower('&&1')||'%'
new   1: select addr, name from v$latch where lower(name) like '%'||lower('gcs partitioned table hash')||'%'
ADDR             NAME
---------------- ----------------------------------------
0000000060018A18 gcs partitioned table hash

old   1: select addr, name from v$latch_parent where lower(name) like '%'||lower('&&1')||'%'
new   1: select addr, name from v$latch_parent where lower(name) like '%'||lower('gcs partitioned table hash')||'%'
ADDR             NAME
---------------- ----------------------------------------
0000000060018A18 gcs partitioned table hash

old   1: select addr, name from v$latch_children where lower(name) like '%'||lower('&&1')||'%'
new   1: select addr, name from v$latch_children where lower(name) like '%'||lower('gcs partitioned table hash')||'%'
no rows selected

--//ADDR='0000000060018A18'.作者拿"gcs partitioned table hash" latah测试有一定道理,这个latch不用在单实例的情况下.

--//测试脚本.我在原作者的脚本上做了一些修改:
$ cat shared_latch.txt
--//connect / as sysdba
col laddr new_value laddr
col vmode  new_value vmode
select decode(lower('&&1'),'s',8,'x',16) vmode from dual ;
SELECT addr laddr FROM v$latch_parent WHERE NAME='gcs partitioned table hash';
oradebug setmypid
oradebug call kslgetsl_w 0x&laddr 1 4 5  &mode
host sleep &&2
oradebug call kslfre 0x&laddr
--//exit

--//说明:参数1 s,x 表示SHARED,EXCLUSIVE.参数2表示sleep的秒数
--//注:不能使用mode,mode是保留字,使用vmode代替.
--//顺便说一下,我不知道作者如何测试,我遇到的问题如果设置共享拴锁,查询v$latch视图会挂在哪里,无法执行.
--//我换成了v$latch_parent视图(源链接使用v$latch视图)

$ cat peek.sh
#! /bib/bash
sqlplus -s -l / as sysdba <<EOF
spool $1
col laddr new_value laddr
SELECT sysdate,addr laddr FROM v\$latch_parent WHERE NAME='gcs partitioned table hash';
oradebug setmypid
$(seq $2|xargs -I{} echo -e 'oradebug peek 0x&laddr 8\nhost sleep 1' )
spool off
EOF
--//peek 长度8(64位),注意intel的大小头问题.
--//latch_free.sql脚本放在最后.比较长.

2.测试1:
--//测试S mode的情况.
--//执行. peek.sh脚本.
$ . peek.sh /tmp/peeks.txt 30
[oracle@gxqyydg4 IP=100.78 ~/hrp430/latch ] $ . peek.sh /tmp/peeks.txt 30

SYSDATE             LADDR
------------------- ----------------
2019-04-15 10:11:00 0000000060018A18

Statement processed.
[060018A18, 060018A1C) = 00000000
[060018A18, 060018A1C) = 00000000
[060018A18, 060018A1C) = 00000001
[060018A18, 060018A1C) = 00000001
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000002
[060018A18, 060018A1C) = 00000001
[060018A18, 060018A1C) = 00000001
[060018A18, 060018A1C) = 00000000
..
--//注意看peek值的变化.0->1->2-1->0,注这里peek的长度是4.

--//session 1:
SYS@book(295.15 spid=40791 pid=21)>  @shared_latch.txt s 10
     VMODE
----------
         8

LADDR
----------------
0000000060018A18

Statement processed.
Function returned 1

Function returned 0

--//session 2:
--//等几秒执行(我的测试等2秒):
SYS@book(101.9 spid=40540 pid=31)>  @ shared_latch.txt s 10
     VMODE
----------
         8

LADDR
----------------
0000000060018A18
Statement processed.
Function returned 1
Function returned 0

--//session 3:
SYS@book> @ latch_free
Process 21
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=295
Process 31
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=101

--//通过测试,可以发现共享栓锁在以共享模式获取时,不会阻塞,该地址的前4个字节记录的是持有S mode的数量.
--//如果你觉得手工测试比较麻烦,修改如下:

$ cat shared_latch_t.txt
connect / as sysdba
col laddr new_value laddr
col vmode  new_value vmode
select decode(lower('&&1'),'s',8,'x',16) vmode from dual ;
SELECT addr laddr FROM v$latch_parent WHERE NAME='gcs partitioned table hash';
oradebug setmypid
oradebug call kslgetsl_w 0x&laddr 1 4 5  &vmode
host sleep &&2
oradebug call kslfre 0x&laddr
exit

--//建立测试脚本(a.sh)如下:
$ cat a.sh
#! /bin/bash
source peek.sh /tmp/peeks.txt 20 > /dev/null &
seq 20 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
wait

--//测试结果如下:
$ grep  -v '^$' /tmp/peeks.txt | uniq -c
      1 SYSDATE             LADDR
      1 ------------------- ----------------
      1 2019-04-15 11:32:44 0000000060018A18
      1 Statement processed.
      2 [060018A18, 060018A20) = 00000001 00000000
      2 [060018A18, 060018A20) = 00000002 00000000
      2 [060018A18, 060018A20) = 00000003 00000000
      2 [060018A18, 060018A20) = 00000002 00000000
      2 [060018A18, 060018A20) = 00000001 00000000
--//没有阻塞,10秒之内都获取latch.
     10 [060018A18, 060018A20) = 00000000 00000000
--//第1列表示该行出现的次数(我每秒取样1次).

$ cat /tmp/latch_free.txt
2019-04-15 11:32:44
2019-04-15 11:32:45
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:32:46
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142
Process 35
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=156

2019-04-15 11:32:47
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142
Process 35
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=156

2019-04-15 11:32:48
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142
Process 35
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=156
Process 36
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=170

2019-04-15 11:32:49
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142
Process 35
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=156
Process 36
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=170

2019-04-15 11:32:50
Process 35
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=156
Process 36
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=170

2019-04-15 11:32:51
Process 35
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=156
Process 36
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=170

2019-04-15 11:32:52
Process 36
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=170

2019-04-15 11:32:53
Process 36
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=170

2019-04-15 11:32:54
2019-04-15 11:32:56
--//结果不说明了,与上面的测试一样,仅仅多了1个会话.最重要一点S mode下不会出现阻塞的情况.

3.测试2:
--//测试X mode的情况.
 $ cat b.sh
#! /bin/bash
source peek.sh /tmp/peeks.txt 30 > /dev/null &
seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &
sqlplus /nolog @ shared_latch_t.txt x 5 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt s 5 > /dev/null &
#sleep 2
sqlplus /nolog @ shared_latch_t.txt s 5 > /dev/null &
wait
--//注:我注解sleep 2,大家根据需要调整时间间隔.
$ grep  -v '^$' /tmp/peeks.txt | uniq -c
      1 SYSDATE             LADDR
      1 ------------------- ----------------
      1 2019-04-15 11:35:44 0000000060018A18
      1 Statement processed.
      5 [060018A18, 060018A20) = 00000020 20000000
     10 [060018A18, 060018A20) = 00000001 00000000
     15 [060018A18, 060018A20) = 00000000 00000000
--//注意前面第1列是该行出现的次数(我每秒取样1次).也就是5秒是0x00000020(PID=32,前4位),10秒是00000001.
--//注意前5秒的peek的记录.后4位0x20000000,也就是X mode peek记录是前4位是PID,后4位是0x20000000.

$ cat /tmp/latch_free.txt
2019-04-15 11:35:44
2019-04-15 11:35:45
Process 32
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=114

2019-04-15 11:35:47
Process 32
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=114
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
  Process 34, waiting for: 0000000060018A18 whr=5 why=4
--//X mode获取阻塞了2个会话的共享拴锁.

2019-04-15 11:35:48
Process 32
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=114
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:35:49
Process 32
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=114
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:35:50
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4
--//如果出现阻塞,会导致顺序的申请共享拴锁串行化处理,阻塞S mode模式.
--//这也是为什么看到10秒是0x00000001的情况.

2019-04-15 11:35:51
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:35:52
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:35:53
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:35:54
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:35:55
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:35:56
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:35:57
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:35:59
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:36:00
2019-04-15 11:36:01

--//可以看出在第1个会话X mode的情况下(peek看到值是0x20=32,对应PID号),阻塞后面2个S mode会话,并且导致后面S mode拴锁串行化,顺序执行.
--//注意S mode后面的peek记录值是S mode的数量(不是PID).

4. 测试3:
--//顺序获取 S模式,X模式,S模式的情况.
$ cat c.sh
#! /bin/bash
source peek.sh /tmp/peeks.txt 20 > /dev/null &
seq 20 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt x 6 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
wait

$ grep  -v '^$' /tmp/peeks.txt | uniq -c
      1 SYSDATE             LADDR
      1 ------------------- ----------------
      1 2019-04-15 11:44:02 0000000060018A18
      1 Statement processed.
      2 [060018A18, 060018A20) = 00000001 00000000
      4 [060018A18, 060018A20) = 00000001 40000000
      6 [060018A18, 060018A20) = 00000021 20000000
      6 [060018A18, 060018A20) = 00000001 00000000
      2 [060018A18, 060018A20) = 00000000 00000000
--//注意看peek值变化,开始2秒(S mode)peek值0x00000001 00000000,第2个会话X mode时,前4位是0x00000001(表示持有S mode的数量),后4位是0x40000000,持续时间4秒.
--//也就是S mode 阻塞X 模式,必须等待S mode释放,X mode才能持有.
--//接着第2个会话持有X mode,peek值00000021 20000000,前4位是PID 0x21=33.后4位是20000000,X 模式会阻塞S mode.后面不再说明了.

2019-04-15 11:44:02
2019-04-15 11:44:03
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

2019-04-15 11:44:04
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
--//S mode 阻塞 X mode.

2019-04-15 11:44:06
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:07
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:08
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:09
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4
--//X mode 阻塞 S mode.

2019-04-15 11:44:10
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:11
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:12
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:13
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 11:44:14
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:44:15
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:44:16
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:44:18
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:44:19
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:44:20
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 11:44:21
2019-04-15 11:44:22

--//大家可以自行建立脚本测试.比如例子:
$ cat d.sh
#! /bin/bash
source peek.sh /tmp/peeks.txt 30 > /dev/null &
seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt x 6 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
sqlplus /nolog @ shared_latch_t.txt s 6 > /dev/null &
wait

$ grep  -v '^$' /tmp/peeks.txt | uniq -c
      1 SYSDATE             LADDR
      1 ------------------- ----------------
      1 2019-04-15 11:55:59 0000000060018A18
      1 Statement processed.
      2 [060018A18, 060018A20) = 00000002 00000000 <= 2个会话(也许指PID更加合适一些)S mode
      4 [060018A18, 060018A20) = 00000002 40000000 <= X mode获取阻塞,设置后4位0x40000000
      6 [060018A18, 060018A20) = 00000022 20000000 <= X mode获取成功,前4位PID,后4位0x20000000.
     12 [060018A18, 060018A20) = 00000001 00000000 <= X mode释放,导致后续的S mode 串行化,需要12秒
      6 [060018A18, 060018A20) = 00000000 00000000
--//不再说明.仅仅记住一点X mode会导致S mode的获取串行化.

5. 测试4:
--//顺序获取 X模式,X模式,X模式的情况.
$ cat e.sh
#! /bin/bash
source peek.sh /tmp/peeks.txt 20 > /dev/null &
seq 20 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free.txt &
sqlplus /nolog @ shared_latch_t.txt x 5 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt x 5 > /dev/null &
sleep 2
sqlplus /nolog @ shared_latch_t.txt x 5 > /dev/null &
wait

$ grep  -v '^$' /tmp/peeks.txt | uniq -c
      1 SYSDATE             LADDR
      1 ------------------- ----------------
      1 2019-04-15 12:09:53 0000000060018A18
      1 Statement processed.
      5 [060018A18, 060018A20) = 0000001C 20000000
      5 [060018A18, 060018A20) = 00000021 20000000
      5 [060018A18, 060018A20) = 00000022 20000000
      5 [060018A18, 060018A20) = 00000000 00000000
--//我想不用我解析,大家应该明白.X mode获取成功,前4位PID,后4位0x20000000.
--//X mode是排他的模式,肯定阻塞X mode的获取,可以看到每次都是5秒.

$ cat /tmp/latch_free.txt
2019-04-15 12:09:53
2019-04-15 12:09:54
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

2019-04-15 12:09:55
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:09:56
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:09:57
Process 28
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58
  Process 33, waiting for: 0000000060018A18 whr=5 why=4
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:09:58
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:09:59
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:10:00
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:10:01
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:10:03
Process 33
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=128
  Process 34, waiting for: 0000000060018A18 whr=5 why=4

2019-04-15 12:10:04
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 12:10:05
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 12:10:06
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 12:10:07
Process 34
 holding: 0000000060018A18  "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=142

2019-04-15 12:10:08
2019-04-15 12:10:09

6.总结:
--//A. S mode 下: peek记录的前4位持有S mode的数量.后4位是0x0. (这里针对的64位的系统)
--//B. S mode 下,如果出现X mode,peek记录的前4位持有S mode的数量.后4位是0x40000000.
--//一旦X mode持有变成 前4位持有会话PID号,后4位0x20000000.
--//C. X mode 持有,会导致顺序的S mode 串行化.从调优角度讲这是最"可怕"的事情.
--//D. 从以上测试可以看出 shared latch优化的重点就是减少X mode出现的频次.
--//E. 大家可以使用我写的脚本重复测试,也修改时间间隔.验证我看到的情况是否正确.
--//最后不小心又写的太长,希望大家能看懂^_^.给一个建议,从测试方法等各个方面,我会认真看注解以及反馈.谢谢!!

7.附件latch_free.sql:
$ 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;

--//我修改加入set feedback off,显示时间的语句便于观察.

最新文章

1. SubSonic3.0使用存储过程查询时，不能使用output参数返回值的问题修改
2. ftp同步代码
3. spring快速入门（四）
4. 两种文件上传的实现－Ajax和form+iframe
5. backbonejs
6. Java 在指定目录建立指定文件名的文件 并输入内容
7. Language
8. 一模 （5） day1
9. shell 与用户交互
10. 栈上连续定义的int变量，地址相差12个字节
11. Unity3d 一些 常见路径
12. Hellow world!
13. Microsoft Visual Studio 2010 Service Pack 1（exe）
14. 修改hosts使用谷歌服务
15. 个人工作记录---工作中遇到的sql查询语句解析
16. 联想G480安装CentOS电缆驱动器
17. IIS发布WebService的一些常见问题
18. 201521123011 《Java程序设计》 第二周学习总结
19. C++ 脑筋急转弯
20. 组件vue传值

热门文章

1. vue总结
2. 【spring】ApplicationListener传递参数到页面（解决静态+动态资源路径+静态文件的缓存控制）
3. Python内置函数(5)——bin
4. solr之环境配置四
5. 通过LRU实现通用高效的超时连接探测
6. .net core高性能通讯开源组件BeetleX
7. Educational Codeforces Round 48 (Rated for Div. 2)——A. Death Note ##
8. transient和synchronized的使用
9. Chapter 5 Blood Type——6
10. Android事件机制之二：onTouch详解