ITPub博客

首页 > 数据库 > Oracle > [20190415]关于shared latch(共享栓锁).txt

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

原创 Oracle 作者:lfree 时间:2019-04-15 14:55:44 0 删除 编辑

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


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

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

注册时间:2008-01-03

  • 博文量
    2392
  • 访问量
    6149728