[20190415]關於shared latch(共享栓鎖).txt

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) = 00000001

[060018A18, 060018A1C) = 00000002

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

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: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)

This query shows trees of processes currently holding and waiting for latches

Tree output enumerates these processes and latches as following:

Process <PID1>

...

...

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,顯示時間的語句便於觀察.

[20190415]關於shared latch(共享栓鎖).txt

相關文章