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

[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,显示时间的语句便于观察.