来自 技术 2019-04-17 00:00 的文章

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