[20190423]簡單測試latch nowilling等待模式.txt

--//我對這個問題的理解就是如果引數willing=0,表示無法獲取該latch，直接退出，再尋找類似的latch。

--//我僅僅知道redo copy latch具有這個特性：

> select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch_children where lower(name) like '%'||lower('redo copy')||'%' ;

ADDR NAME LEVEL# LATCH# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS WAIT_TIME

---------------- ---------- ------ ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ----------

00000012D720ADA8 redo copy 4 208 53 0 0 500627938 304381 0 0 0 0

00000012D720ACD0 redo copy 4 208 53 0 0 497827706 323330 0 0 0 0

..

00000012D72086D8 redo copy 4 208 53 0 0 491448415 365472 0 0 0 0

00000012D7208600 redo copy 4 208 53 0 0 508008338 391955 0 0 0 0

48 rows selected.

--//你可以發現nowait latch 的一個特點,就是IMMEDIATE_GETS會相對很高.注我查詢的生產系統的情況.測試環境不會這麼高的.

--//引數如下，另外我前面blog寫錯了，where是最後1個引數。

kslgetl(laddr, wait, why, where) – Get exclusive latch

More precisely, to request the latch Oracle kernel needs:

--//更準確地說，要請求閂鎖Oracle核心需要：

laddress -- address of latch in SGA

wait -- flag. If true, this is latch get in willing-to-wait mode..

why -- context why the latch is acquired at this where.

where -- code for location from where the latch is acquired.

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

--//參考連結如下:http://blog.itpub.net/267265/viewspace-2641548/=>[20190416]exclusive latch測試指令碼.txt

$ cat peek.sh

#! /bib/bash

# 引數如下:latch_name Monitoring_duration or laddr

sqlplus -s -l / as sysdba <<EOF

col laddr new_value laddr

SELECT sysdate,addr laddr FROM v\$latch_parent WHERE NAME='$1';

oradebug setmypid

$(seq $2|xargs -I{} echo -e 'oradebug peek 0x&laddr 8\nhost sleep 1' )

EOF

$ cat exclusive_latch.txt

/* 引數如下: @ exclusive_latch.txt latch_name willing why where sleep_num */

--//connect / as sysdba

col laddr new_value laddr

SELECT addr laddr FROM v$latch_parent WHERE NAME='&&1';

oradebug setmypid

oradebug call kslgetl 0x&laddr &&2 &&3 &&4

host sleep &&5

oradebug call kslfre 0x&laddr

--//exit

$ cat y1.sh

#! /bin/bash

zdate=$(date '+%Y%m%d%H%M%S')

echo $zdate

source peek.sh 'test excl. parent2 l0' 20 | timestamp.pl >| /tmp/peekx_${zdate}.txt &

seq 20 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >| /tmp/latch_freeo_${zdate}.txt &

# 引數如下: @ exclusive_latch.txt latch_name willing why where sleep_num

sqlplus / as sysdba <<< "@ exclusive_latch.txt 'test excl. parent2 l0' 0 1 2 6" > /dev/null &

sleep 2

sqlplus / as sysdba <<< "@ exclusive_latch.txt 'test excl. parent2 l0' 0 3 4 6" > /dev/null &

sleep 4.1

sqlplus / as sysdba <<< "@ exclusive_latch.txt 'test excl. parent2 l0' 0 5 6 6" > /dev/null &

wait

$ . y1.sh

20190424091250

[3]- Done sqlplus / as sysdba <<< "@ exclusive_latch.txt 'test excl. parent2 l0' 0 1 2 6" > /dev/null

[1] Done source peek.sh 'test excl. parent2 l0' 20 | timestamp.pl >|/tmp/peekx_${zdate}.txt

[4]- Done sqlplus / as sysdba <<< "@ exclusive_latch.txt 'test excl. parent2 l0' 0 3 4 6" > /dev/null

[5]+ Done sqlplus / as sysdba <<< "@ exclusive_latch.txt 'test excl. parent2 l0' 0 5 6 6" > /dev/null

[2]+ Done seq 20 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >|/tmp/latch_freeo_${zdate}.txt

2.測試:

$ grep -v '^.*: $' /tmp/peekx_20190424091250.txt | cut -c10- | uniq -c

1 SYSDATE LADDR

1 ------------------- ----------------

1 2019-04-24 09:12:50 0000000060009978

1 Statement processed.

6 [060009978, 060009980) = 00000015 00000000

1 [060009978, 060009980) = 00000000 00000000

6 [060009978, 060009980) = 00000015 00000000

7 [060009978, 060009980) = 00000000 00000000

--//你可以發現第1個會話申請成功,第2個會話沒有申請成功,直接退出.第3個會話申請成功(因為sleep 2+4.1秒).

--//cat /tmp/latch_freeo_20190424091250.txt

2019-04-24 09:12:50

2019-04-24 09:12:51

Process 21

holding: 0000000060009978 "test excl. parent2 l0" lvl=0 whr=2 why=1, SID=295

2019-04-24 09:12:52

Process 21

holding: 0000000060009978 "test excl. parent2 l0" lvl=0 whr=2 why=1, SID=295

2019-04-24 09:12:53

Process 21

holding: 0000000060009978 "test excl. parent2 l0" lvl=0 whr=2 why=1, SID=295

2019-04-24 09:12:54

Process 21

holding: 0000000060009978 "test excl. parent2 l0" lvl=0 whr=2 why=1, SID=295

2019-04-24 09:12:55

Process 21

holding: 0000000060009978 "test excl. parent2 l0" lvl=0 whr=2 why=1, SID=295

2019-04-24 09:12:56

Process 21